Oracle對索引分析的優化

今天在PUB上看貼的時候發現了一個有趣的現象，Oracle在進行索引分析的時候並非完全按照指定的方式進行。比如，要求Oracle進行COMPUTE STATISTICS分析，但是Oracle很可能進行的是ESTIMATE STATISTICS。

測試版本9204，Oracle在資料量達到一定量的情況下，會改變分析的預設行為，由COMPUTE STATISTICS變為ESTIMATE STATISTICS：

SQL> CREATE TABLE T_STAT (ID NUMBER, NAME VARCHAR2(100), CREATE_DATE DATE);

Table created.

SQL> CREATE INDEX IND_T_STAT_ID ON T_STAT(ID);

Index created.

SQL> CREATE INDEX IND_T_STAT_NAME ON T_STAT(NAME);

Index created.

SQL> CREATE INDEX IND_T_STAT_CREATE_DATE ON T_STAT(CREATE_DATE);

Index created.

SQL> INSERT INTO T_STAT SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS;

45152 rows created.

SQL> COMMIT;

Commit complete.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_STAT', CASCADE => TRUE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(DISTINCT ID), COUNT(DISTINCT NAME), COUNT(DISTINCT CREATE_DATE) FROM T_STAT;

COUNT(DISTINCTID) COUNT(DISTINCTNAME) COUNT(DISTINCTCREATE_DATE)
----------------- ------------------- --------------------------
45152 20044 5785

SQL> SELECT INDEX_NAME, NUM_ROWS, SAMPLE_SIZE, DISTINCT_KEYS FROM USER_INDEXES WHERE TABLE_NAME = 'T_STAT';

INDEX_NAME NUM_ROWS SAMPLE_SIZE DISTINCT_KEYS
------------------------------ ---------- ----------- -------------
IND_T_STAT_CREATE_DATE 45152 45152 5785
IND_T_STAT_ID 45152 45152 45152
IND_T_STAT_NAME 45152 45152 20044

從目前的結果看，Oracle採用的是COMPUTE的方法，因為NUM_ROWS和SAMPLE_SIZE是相等的。這個時候得到的DISTINCT_KEYS也和SQL語句從表中獲取的資料是一樣多的。

下面增大表的資料量：

SQL> BEGIN
2 FOR I IN 1..3 LOOP
3 INSERT INTO T_STAT SELECT ROWNUM + 45152*POWER(2, I - 1), NAME, CREATE_DATE - I FROM T_STAT;
4 COMMIT;
5 END LOOP;
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_STAT', CASCADE => TRUE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(DISTINCT ID), COUNT(DISTINCT NAME), COUNT(DISTINCT CREATE_DATE) FROM T_STAT;

COUNT(DISTINCTID) COUNT(DISTINCTNAME) COUNT(DISTINCTCREATE_DATE)
----------------- ------------------- --------------------------
361216 20044 39955

SQL> SELECT INDEX_NAME, NUM_ROWS, SAMPLE_SIZE, DISTINCT_KEYS FROM USER_INDEXES WHERE TABLE_NAME = 'T_STAT';

INDEX_NAME NUM_ROWS SAMPLE_SIZE DISTINCT_KEYS
------------------------------ ---------- ----------- -------------
IND_T_STAT_CREATE_DATE 361216 361216 39955
IND_T_STAT_ID 361216 361216 361216
IND_T_STAT_NAME 361216 361216 20044

分析方式仍然沒有發生變化，下面再將資料量擴大一倍：

SQL> INSERT INTO T_STAT SELECT ROWNUM + 361216, NAME, CREATE_DATE - 1 FROM T_STAT;

361216 rows created.

SQL> COMMIT;

Commit complete.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_STAT', CASCADE => TRUE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(DISTINCT ID), COUNT(DISTINCT NAME), COUNT(DISTINCT CREATE_DATE) FROM T_STAT;

COUNT(DISTINCTID) COUNT(DISTINCTNAME) COUNT(DISTINCTCREATE_DATE)
----------------- ------------------- --------------------------
722432 20044 45608

SQL> SELECT INDEX_NAME, NUM_ROWS, SAMPLE_SIZE, DISTINCT_KEYS FROM USER_INDEXES WHERE TABLE_NAME = 'T_STAT';

INDEX_NAME NUM_ROWS SAMPLE_SIZE DISTINCT_KEYS
------------------------------ ---------- ----------- -------------
IND_T_STAT_CREATE_DATE 722432 722432 45608
IND_T_STAT_ID 722432 722432 722432
IND_T_STAT_NAME 712587 172989 5230

這次SAMPLE_SIZE的值已經發生了變化，索引IND_T_STAT_NAME的取樣值只有172989，只佔全部記錄的四分之一。而DISTINCT值也和SQL計算的結果相去甚遠。

再將結果擴大一倍：

SQL> INSERT INTO T_STAT SELECT ROWNUM + 722432, NAME, CREATE_DATE - 1 FROM T_STAT;

722432 rows created.

SQL> COMMIT;

Commit complete.

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_STAT', CASCADE => TRUE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(DISTINCT ID), COUNT(DISTINCT NAME), COUNT(DISTINCT CREATE_DATE) FROM T_STAT;

COUNT(DISTINCTID) COUNT(DISTINCTNAME) COUNT(DISTINCTCREATE_DATE)
----------------- ------------------- --------------------------
1444864 20044 51152

SQL> SELECT INDEX_NAME, NUM_ROWS, SAMPLE_SIZE, DISTINCT_KEYS FROM USER_INDEXES WHERE TABLE_NAME = 'T_STAT';

INDEX_NAME NUM_ROWS SAMPLE_SIZE DISTINCT_KEYS
------------------------------ ---------- ----------- -------------
IND_T_STAT_CREATE_DATE 1420616 311022 11139
IND_T_STAT_ID 1444864 1444864 1444864
IND_T_STAT_NAME 1441050 175268 3133

這次索引IND_T_STAT_CREATE_DATE的SAMPLE_SIZE也發生了變化。而且NUM_ROWS的值也不準確了，這說明Oracle沒有真正的掃描所有的記錄，而是採用估算的方法。

而且，Oracle的估算值還是相當準確的。

SQL> SELECT INDEX_NAME, NUM_ROWS / SAMPLE_SIZE * DISTINCT_KEYS FROM USER_INDEXES WHERE TABLE_NAME = 'T_STAT';

INDEX_NAME NUM_ROWS/SAMPLE_SIZE*DISTINCT_KEYS
------------------------------ ----------------------------------
IND_T_STAT_CREATE_DATE 50878.2068
IND_T_STAT_ID 1444864
IND_T_STAT_NAME 25759.4635

對比上面COUNT(DISTINCT)的結果可以發現，Oracle雖然採用了估算的方式，但是估算結果還是比較準確的。

這和索引的結構有關，由於索引是排序的。當達到了一定的資料量之後，對於重複記錄多的索引，Oracle可以很快的推斷出資料的總體分佈，而對於接近唯一的索引，Oracle必須完全掃描才能得到資料的分佈情況。

這估計就是為什麼IND_T_STAT_ID索引仍然採用COMPUTE的方式。也是在資料量較小的時候，IND_T_STAT_NAME就先採用了ESTIMATE的原因。

上面採用的是CASCADE設定為TRUE，然後分析表的情況，直接分析索引的效果是相同的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69127/，如需轉載，請註明出處，否則將追究法律責任。