oracle pl sql索引优化,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，然后分析表的情况，直接分析索引的效果是相同的。