ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 增加索引改变执行计划——SQL优化之Everything is possible

增加索引改变执行计划——SQL优化之Everything is possible

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

大家都对删除对象比较敏感,对增加对象一般会放松警惕。而增加索引所带来的危害和删除索引同样大。由于添加了一个新的索引,使得以前运行正常的SQL错误的选择了新建索引,导致SQL运行时间成倍的增长,从而使整个数据库无法相应。这种现象已经屡见不鲜了。

所以说增加一个索引改变SQL的执行计划,这并不奇怪。如果增加了索引之后,相关查询的执行计划没有改变,那么增加索引的意义何在。

但是奇怪的是,当增加一个索引时,查询的执行计划发生了变化,但是执行计划确并没有使用新增的索引。


首先建立模拟环境:

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS
2 WHERE OWNER = 'SYS'
3 AND OBJECT_TYPE NOT LIKE '%BODY'
4 AND OBJECT_TYPE NOT LIKE 'JAVA%';

Table created.

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SEGMENTS WHERE OWNER = 'SYS';

Table created.

SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE OWNER = 'SYS';

Table created.

SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (OBJECT_NAME);

Table altered.

SQL> CREATE INDEX IND_T2_SEGNAME ON T2(SEGMENT_NAME);

Index created.

SQL> CREATE INDEX IND_T3_TABNAME ON T3(TABLE_NAME);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T3', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)

PL/SQL procedure successfully completed.

SQL> SET AUTOT TRACE EXP
SQL> SELECT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
2 FROM T1, T2
3 WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
4 AND NOT EXISTS
5 (
6 SELECT 1 FROM T3
7 WHERE T3.INDEX_NAME = T1.OBJECT_NAME
8 AND T3.TABLE_NAME = 'OBJ$'
9 );

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=668 Bytes=58784)
1 0 HASH JOIN (ANTI) (Cost=12 Card=668 Bytes=58784)
2 1 HASH JOIN (Cost=9 Card=668 Bytes=39412)
3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
4 2 TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=2 Card=2 Bytes=58)
6 5 INDEX (RANGE SCAN) OF 'IND_T3_TABNAME' (NON-UNIQUE) (Cost=1 Card=2)

为了提高上面查询的性能,建立一个复合索引来避免表的扫描。

但是如果建立索引的时候,把列的顺序搞错了:

SQL> CREATE INDEX IND_T3_IND_TAB_NAME ON T3(INDEX_NAME, TABLE_NAME);

Index created.

SQL> SELECT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
2 FROM T1, T2
3 WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
4 AND NOT EXISTS
5 (
6 SELECT 1 FROM T3
7 WHERE T3.INDEX_NAME = T1.OBJECT_NAME
8 AND T3.TABLE_NAME = 'OBJ$'
9 );

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=389 Card=190 Bytes=11210)
1 0 FILTER
2 1 HASH JOIN (Cost=9 Card=190 Bytes=11210)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=190 Bytes=5130)
4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=2 Card=1 Bytes=29)
6 5 INDEX (RANGE SCAN) OF 'IND_T3_TABNAME' (NON-UNIQUE) (Cost=1 Card=2)

执行计划从原来的HASH JOIN ANTI变成了FILTER,但是Oracle却并没有使用新建立的索引。令人疑惑的是,为什么新建索引没有使用的情况下,Oracle仍然改变了执行计划。

这个例子说明了增加索引时更要谨慎,即使Oracle并没有使用新建索引,仍然有可能改变现有SQL的执行计划。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69131/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10355436