恆等查詢條件改變執行計劃——SQL優化之Everything is Possible
有的時候開發人員為了方便會在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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 增加索引改變執行計劃——SQL優化之Everything is possible索引SQL優化
- 增加Distinct後查詢效率反而提高——SQL優化之Everything is possibleSQL優化
- sql執行計劃是否改變SQL
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 11g 改變SQL執行計劃SQL
- 一條SQL語句的執行計劃變化探究SQL
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- UNION效率比UNION ALL效率高——SQL優化之Everything is possibleSQL優化
- SQL優化案例-改變那些CBO無能為力的執行計劃(一)SQL優化
- 【sql調優之執行計劃】獲取執行計劃SQL
- 【轉】UNION效率比UNION ALL效率高——SQL優化之Everything is possibleSQL優化
- TiDB 查詢優化及調優系列(四)查詢執行計劃的調整及優化原理TiDB優化
- 利用coe_xfr_sql_profile 改變執行計劃SQL
- SQL多條件查詢SQL
- 透過查詢檢視sql執行計劃SQL
- 使用hint改變執行計劃
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- explain 查詢執行計劃AI
- sql 查詢條件問題SQL
- 一條查詢sql的執行之路SQL
- SQL Server解惑——查詢條件IN中能否使用變數SQLServer變數
- 兩層GROUP BY效率反而比一層GROUP BY效率高——SQL優化之Everything is possibleSQL優化
- 使用leading(,)優化sql執行計劃優化SQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- Oracle 優化器與sql查詢執行順序Oracle優化SQL
- oracl10g以上通過v$sql_plan查詢sql的執行計劃是否發生變化SQL
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- 十七、Mysql之SQL優化查詢MySql優化
- Oracle優化案例-改變那些CBO無能為力的執行計劃(一)Oracle優化
- PostgreSQL執行計劃變化SQL
- 查詢作為條件的SQLSQL
- 改進資料庫效能-SQL查詢優化資料庫SQL優化
- 【優化】Oracle 執行計劃優化Oracle
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL