ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle对索引分析的优化

Oracle对索引分析的优化

原创 Linux操作系统 作者:yangtingkun 时间:2007-01-05 00:00:00 0 删除 编辑

今天在PUB上看贴的时候发现了一个有趣的现象,Oracle在进行索引分析的时候并非完全按照指定的方式进行。比如,要求Oracle进行COMPUTE STATISTICS分析,但是Oracle很可能进行的是ESTIMATE STATISTICS


测试版本9204Oracle在数据量达到一定量的情况下,会改变分析的默认行为,由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_ROWSSAMPLE_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_DATESAMPLE_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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10354036