ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 恒等查询条件改变执行计划——SQL优化之Everything is Possible

恒等查询条件改变执行计划——SQL优化之Everything is Possible

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

有的时候开发人员为了方便会在WHERE语句后面添加一个1=1,这样在处理页面传入的条件是就可用不用判断直接添加AND 条件。

一直认为添加一个恒等条件,不会对Oracle的查询造成什么影响,最多不过影响一下Oracle的性能,但是今天突然发现,这个恒等的查询条件居然可以影响Oracle的执行计划。


首先看一个简单的例子:

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 )
10 ;

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> 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 1 = 1
8 AND T3.INDEX_NAME = T1.OBJECT_NAME
9 AND T3.TABLE_NAME = 'OBJ$'
10 )
11 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=668 Bytes=50768)
1 0 HASH JOIN (ANTI) (Cost=12 Card=668 Bytes=50768)
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 VIEW OF 'VW_SQ_1' (Cost=2 Card=2 Bytes=34)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=2 Card=2 Bytes=58)
7 6 INDEX (RANGE SCAN) OF 'IND_T3_TABNAME' (NON-UNIQUE) (Cost=1 Card=2)

观察两个查询语句会发现,两个查询语句唯一的区别就是第二个查询语句中的NOT EXISTS子查询中包含了一个恒等查询条件1 = 1

仅仅是这一点的区别,却造成了两个SQL语句的执行计划出现了差异。对于第二个查询Oracle居然多生成了一个临时的VIEW。也许有人认为这个执行计划没有太大的区别,基本上可以认为等价。但是由于多生成了一个VIEW的步骤,必然会造成性能的下降,更为关键的是:这说明Oracle认为两个SQL语句是不同的,而且处理方式也是不同的。

如果一个简单的VIEW步骤还不能说明什么问题的话,那么看看下面这个在实际运行中碰到的问题:

SQL> SET AUTOT TRACE EXP
SQL> SELECT A.ID,
2 B.NAME_CHN,
3 B.SPEC,
4 A.MANUFACTURE_ID
5 FROM CAT_PRODUCT A,
6 CAT_DRUG B
7 WHERE A.MEDICAL_ID = B.ID
8 AND A.CHECK_FLAG = 1
9 AND NOT EXISTS
10 (
11 SELECT 1
12 FROM PROJECT_SUPPLY_PRODUCT C
13 WHERE C.DATA_PRODUCT = A.ID
14 AND C.ENABLE_FLAG = '1'
15 AND C.PROJECT_ID = 'MRBR00000000000000709824'
16 )
17 ;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=483 Card=1 Bytes=213)
1 0 NESTED LOOPS (ANTI) (Cost=483 Card=1 Bytes=213)
2 1 NESTED LOOPS (Cost=481 Card=1 Bytes=160)
3 2 VIEW OF 'index$_join$_001' (Cost=480 Card=1 Bytes=77)
4 3 HASH JOIN
5 4 HASH JOIN
6 5 INDEX (FAST FULL SCAN) OF 'PK_CAT_PRODUCT' (UNIQUE) (Cost=33 Card=1 Bytes=77)
7 5 INDEX (FAST FULL SCAN) OF 'TU_CAT_PRODUCT_MED_CHECK' (NON-UNIQUE) (Cost=33 Card=1 Bytes=77)
8 4 INDEX (FAST FULL SCAN) OF 'TU_CAT_PRODUCT_MANUFACT_ID' (NON-UNIQUE) (Cost=33 Card=1 Bytes=77)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'CAT_DRUG' (Cost=1 Card=1 Bytes=83)
10 9 INDEX (UNIQUE SCAN) OF 'PK_CAT_DRUG' (UNIQUE)
11 1 TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_SUPPLY_PRODUCT' (Cost=2 Card=1 Bytes=53)
12 11 INDEX (RANGE SCAN) OF 'IDX_PROJECT_SUPPLY_PRODUCT_PJ' (NON-UNIQUE) (Cost=1 Card=1)

SQL> SELECT A.ID,
2 B.NAME_CHN,
3 B.SPEC,
4 A.MANUFACTURE_ID
5 FROM CAT_PRODUCT A,
6 CAT_DRUG B
7 WHERE A.MEDICAL_ID = B.ID
8 AND A.CHECK_FLAG = 1
9 AND NOT EXISTS
10 (
11 SELECT 1
12 FROM PROJECT_SUPPLY_PRODUCT C
13 WHERE 1 = 1
14 AND C.DATA_PRODUCT = A.ID
15 AND C.ENABLE_FLAG = '1'
16 AND C.PROJECT_ID = 'MRBR00000000000000709824'
17 )
18 ;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=484 Card=1 Bytes=186)
1 0 HASH JOIN (ANTI) (Cost=484 Card=1 Bytes=186)
2 1 NESTED LOOPS (Cost=481 Card=1 Bytes=160)
3 2 VIEW OF 'index$_join$_001' (Cost=480 Card=1 Bytes=77)
4 3 HASH JOIN
5 4 HASH JOIN
6 5 INDEX (FAST FULL SCAN) OF 'PK_CAT_PRODUCT' (UNIQUE) (Cost=33 Card=1 Bytes=77)
7 5 INDEX (FAST FULL SCAN) OF 'TU_CAT_PRODUCT_MED_CHECK' (NON-UNIQUE) (Cost=33 Card=1 Bytes=77)
8 4 INDEX (FAST FULL SCAN) OF 'TU_CAT_PRODUCT_MANUFACT_ID' (NON-UNIQUE) (Cost=33 Card=1 Bytes=77)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'CAT_DRUG' (Cost=1 Card=1 Bytes=83)
10 9 INDEX (UNIQUE SCAN) OF 'PK_CAT_DRUG' (UNIQUE)
11 1 VIEW OF 'VW_SQ_1' (Cost=2 Card=1 Bytes=26)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_SUPPLY_PRODUCT' (Cost=2 Card=1 Bytes=53)
13 12 INDEX (RANGE SCAN) OF 'IDX_PROJECT_SUPPLY_PRODUCT_PJ' (NON-UNIQUE) (Cost=1 Card=1)

SQL语句仍然是只相差了NOT EXISTS语句中的一个1 = 1的恒等条件,但是如果不加这个恒等条件,Oracle用的是NESTED LOOP ANTI,而加上了这个恒等条件,则Oracle选择了HASH JOIN ANTI

这次执行计划的改变足以使SQL的执行时间发生成百上千倍的变化。

一个小小的恒等查询条件居然可以使查询计划发生变化,这是我从来没有想到的。

通过这个问题,我总结了如下三点:

要敢于怀疑任何事情,没有什么是不可能的,一切以实践的结果为准;

不要在SQL上添加一些没有必要的小零碎,一些看似无害的东西在一些情况下可能会引发严重的问题。

建立良好的编码风格,以及严格SQL语句的编写制度的必要性。

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

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10366574