oracle取得表中總記錄數最快的方法

查詢表中的記錄總數的文法就是SELECT COUNT(*) FROM TABLE_NAME。這可能是最經常使用的一類SQL語句。

本文討論怎樣才能最快的得到這個記錄數。本文純粹主要是理論上的討論，文章中很多內容（如常數索引）對實際的指導意義不大。

在具體描述之前，強調幾個前提：

首先表中的記錄數不能太少，否則討論的意義就不大了，在我下面的例子中記錄數是3萬左右，其實這個數量級還是比較小，不過已經能夠看出一些效果了。

根據執行時間的長短進行判斷偶然性比較大，本文以沒種方法邏輯讀的多少來進行判斷。由於包括查詢重寫（需要的相對較多的執行計畫的分析）和索引壓縮（屬於CPU密集型，消耗CPU資源較多），僅僅用邏輯讀來衡量各種方法的優劣肯定不會很準確，但是考慮到表中的資料量比較大，而且我們以SQL的第二次執行結果為準，所以，其他方面的影響還是可以忽略的。

另外一個前提就是結果的準確性，查詢USER_TABLES的NUM_ROWS列等類似的方法不在本文討論範疇之內。

最後，由於ORACLE的緩衝和共用池的機制，SQL語句邏輯讀一般從第二次執行才穩定下來，出於篇幅的考慮，下面所有的SELECT COUNT(*) FROM T的結果都是該SQL語句第二次執行的結果。

如果存在一個查詢語句為SELECT COUNT(*)的物化視圖，則最快的方式一定是掃描這張物化視圖。

SQL> CREATE TABLE T (ID NUMBER NOT NULL, NAME VARCHAR2(30), TYPE VARCHAR2(18));

表已建立。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS;

已建立30931行。

SQL> COMMIT;

提交完成。

SQL> CREATE MATERIALIZED VIEW LOG ON T WITH ROWID INCLUDING NEW VALUES;

實體化視圖日誌已建立。

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS
2 SELECT COUNT(*) FROM T;

實體化視圖已建立。

SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

會話已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 過程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=1066)
1 0 TABLE ACCESS (FULL) OF 'MV_T' (Cost=2 Card=82 Bytes=1066)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

根據上面的查詢可以看出，掃描物化視圖，只需3個邏輯讀就可以了。但是，物化視圖對系統的限制比較多。首先要建立物化視圖日誌，還要在SYSTEM或SESSION級設定參數，必須使用CBO等很多的條件，限制了物化視圖的使用，而且最重要的是，一般情況下不會存在一個單純查詢全表記錄數的物化視圖，而一般建立的物化視圖是為了加快一些更加複雜的表串連或聚集的查詢的。因此，即使存在物化視圖，也不會直接得到結果，一般是對物化視圖上的結果進行再次計算。

如果不考慮物化視圖，那麼得到記錄總數的最快的方法一定是BITMAP索引掃描。BITMAP索引的機制使得BITMAP索引回答COUNT(*)之類的查詢具有最快的響應速度和最小的邏輯讀。至於BITMAP索引的機制，這裡就不重複描述了，還是看看BITMAP索引的表現吧：

SQL> DROP MATERIALIZED VIEW MV_T;

實體化視圖已刪除。

SQL> DROP MATERIALIZED VIEW LOG ON T;

實體化視圖日誌已刪除。

SQL> CREATE BITMAP INDEX IND_B_T_TYPE ON T (TYPE);

索引已建立。

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'IND_B_T_TYPE')

PL/SQL 過程已成功完成。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'IND_B_T_TYPE'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到，BITMAP索引的表現十分出色，只需5個邏輯讀就可以得到結果。可惜的是，BITMAP索引比較適合在資料倉儲中使用，而對於OLTP環境，BITMAP索引的鎖粒度將給整個系統帶來嚴重的災難。因此，對於OLTP系統，BITMAP索引也是不合適的。

不考慮BITMAP索引，那麼速度最快的應該是普通索引的快速全掃了，比如主鍵列。

SQL> DROP INDEX IND_B_T_TYPE;

索引已丟棄。

SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID);

表已更改。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_T' (UNIQUE) (Cost=4 Card=30931)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

主鍵的快速全掃只需69個邏輯讀。但是由於主鍵這裡用的是ROWNUM，也就是說是主鍵的值是從1到30931，Oracle儲存這些NUMBER類型則需要2到4位不等。如果建立一個常數索引，則在儲存空間上要節省一些。而在執行索引快速全掃時，就能減少一些邏輯讀。

SQL> CREATE INDEX IND_T_CON ON T(1);

索引已建立。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_T_CON' (NON-UNIQUE) (Cost=4 Card=30931)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

果然，掃描常數索引比掃描主鍵的邏輯讀更小一些。考慮到NUMBER類型中，1的儲存需要兩位，而0的儲存只需一位，那麼用0代替1建立常數索引，應該效果更好。

SQL> CREATE INDEX IND_T_CON_0 ON T(0);

索引已建立。

SQL> SELECT /*+ INDEX(T IND_T_CON_0) */ COUNT(*) FROM T;

COUNT(*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'IND_T_CON_0' (NON-UNIQUE) (Cost=26 Card=30931)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

由於常數索引中所有節點值都相同，如果壓縮一下的話，應該還能減少邏輯讀。

SQL> DROP INDEX IND_T_CON_0;

索引已丟棄。

SQL> CREATE INDEX IND_T_CON_COMPRESS ON T(0) COMPRESS;

索引已建立。

SQL> SELECT /*+ INDEX(T IND_T_CON_COMPRESS) */ COUNT(*) FROM T;

COUNT(*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'IND_T_CON_COMPRESS' (NON-UNIQUE) (Cost=26 Card=30931)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

和預計的一樣，經過壓縮，索引掃描的邏輯讀進一步減少，現在和最初的主鍵掃描相比，邏輯讀已經減少了30%。

如果只為了得到COUNT(*)，那麼壓縮過的常數索引是最佳選擇，不過這個索引對其他查詢是沒有任何協助的，因此，實際中的用處不大。