【優化】COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)、COUNT(允許為空列)、COUNT(DISTINCT 列名)
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① COUNT(1)和COUNT(*)的差別（重點）
② 10046和10053的使用
③ “SELECT COUNT(列)”和“SELECT 列”在選擇索引方面的差別
④ COUNT計數的優化
Tips：
本文适合于Oracle國中級人員閱讀，Oracle大師請略過本文。
本文若有錯誤或不完善的地方請大家多多指正，您的批評指正是我寫作的最大動力。
看了很多有關COUNT(1)和COUNT(*)的差別和效率，衆說紛纭。最終還是決定自己動手實驗一番。
項目
source db
db 類型
RAC
db version
11.2.0.3.0
db 存儲
ASM
OS版本及kernel版本
RHEL 6.5
弄清楚COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)、COUNT(允許為空列)、COUNT(DISTINCT 列名)之間的差別，以及它們之間的效率問題。
--建立1W行的表
DROP TABLE T_COUNT_LHR;
CREATE TABLE T_COUNT_LHR AS
SELECT OBJECT_ID,
OBJECT_NAME,
OWNER,
DATA_OBJECT_ID,
OBJECT_TYPE,
LAST_DDL_TIME
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID IS NOT NULL
AND D.OBJECT_NAME IS NOT NULL
AND ROWNUM <= 10000;
--更新空值，
UPDATE T_COUNT_LHR t SET t.object_type='' WHERE Rownum<=5;
UPDATE T_COUNT_LHR t SET t.LAST_DDL_TIME=T.LAST_DDL_TIME+ROWNUM;
UPDATE T_COUNT_LHR t SET t.LAST_DDL_TIME='' WHERE Rownum<=1;
COMMIT;
--添加主鍵、非空限制、唯一索引、普通索引
ALTER TABLE T_COUNT_LHR ADD CONSTRAINT PK_OBJECT_ID PRIMARY KEY(OBJECT_ID);
ALTER TABLE T_COUNT_LHR MODIFY OBJECT_NAME NOT NULL;
CREATE UNIQUE INDEX IDX_LDT ON T_COUNT_LHR(LAST_DDL_TIME);
CREATE INDEX IDX_DATA_OBJECT_ID ON T_COUNT_LHR(DATA_OBJECT_ID);
CREATE INDEX IDX_DATA_OWNER ON T_COUNT_LHR(OWNER);
ALTER TABLE T_COUNT_LHR MODIFY OWNER NOT NULL;
--收集統計資訊
EXEC dbms_stats.gather_table_stats(USER,'T_COUNT_LHR');
SELECT d.COLUMN_NAME,d.DATA_TYPE,d.NUM_NULLS,d.NUM_DISTINCT,d.LAST_ANALYZED FROM cols d WHERE d.TABLE_NAME='T_COUNT_LHR';
表的資訊如下所示：
列名
是否主鍵
是否允許為空
是否有索引
資料類型
空值的行數
不同值的行數
總行數
OBJECT_ID
Y
N
唯一索引
NUMBER
10000
OBJECT_NAME
無
VARCHAR2
8112
OWNER
普通索引（IDX_OWNER）
5
DATA_OBJECT_ID
普通索引（IDX_DATA_OBJECT_ID）
7645
2318
OBJECT_TYPE
20
LAST_DDL_TIME
唯一索引（IDX_LDT）
DATE
1
9999
需要統計如下幾種情況：
SELECT COUNT(1) FROM T_COUNT_LHR;--走索引
SELECT COUNT(*) FROM T_COUNT_LHR;--走索引
SELECT COUNT(ROWID) FROM T_COUNT_LHR; --走索引
SELECT COUNT(OBJECT_ID) FROM T_COUNT_LHR; --走索引
SELECT COUNT(OBJECT_NAME) FROM T_COUNT_LHR;--走索引
SELECT COUNT(OWNER) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(D.DATA_OBJECT_ID) FROM T_COUNT_LHR D; --走索引
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;--走索引
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D; --不走索引
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;--走索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D;--不走索引
SELECT COUNT(DISTINCT OWNER) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D WHERE DATA_OBJECT_ID IS NOT NULL ;--走索引
COUNT()函數是Oracle中的聚合函數，用于統計結果集的行數。其文法形式如下所示：
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.
我們把COUNT的使用情況分為以下3類：
① COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主鍵)、COUNT(ROWID)、COUNT(非空列)
② COUNT(允許為空列)
③ COUNT(DISTINCT 列名)
下面分别從查詢結果和效率方面做個比較：
（一）結果差別
① COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)這幾種方式統計的行數是表中所有存在的行的總數，包括值為NULL的行和非空行。是以，這幾種方式的執行結果相同。這裡的常量可以為數字或字元串，例如，COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是：這裡的COUNT(1)中的“1”并不表示表中的第一列，它其實是一個表達式，可以換成任意數字或字元或表達式。
② COUNT(允許為空列) 這種方式統計的行數不會包括字段值為NULL的行。
③ COUNT(DISTINCT 列名) 得到的結果是除去值為NULL和重複資料後的結果。
④ “SELECT COUNT(''),COUNT(NULL) FROM T_COUNT_LHR;”傳回0行。
（二）效率、索引
① 如果存在主鍵或非空列上的索引，那麼COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主鍵)、COUNT(非空列)會首先選擇主鍵上的索引快速全掃描（INDEX FAST FULL SCAN）。若主鍵不存在則會選擇非空列上的索引。若非空列上沒有索引則肯定走全表掃描（TABLE ACCESS FULL）。其中，COUNT(ROWID)在走索引的時候比其它幾種方式要慢。通過10053事件可以看到這幾種方式除了COUNT(ROWID)之外，其它最終都會轉換成COUNT(*)的方式來執行。
② 對于COUNT(COL1)來說，隻要列字段上有索引則會選擇索引快速全掃描（INDEX FAST FULL SCAN）。而對于“SELECT COL1”來說，除非列上有NOT NULL限制，否則執行計劃會選擇全表掃描。
③ COUNT(DISTINCT 列名) 若列上有索引，且有非空限制或在WHERE子句中使用IS NOT NULL，則會選擇索引快速全掃描。其餘情況選擇全表掃描。
本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/1911715，如需轉載請自行聯系原作者