增加索引改變執行計劃——SQL優化之Everything is possible
大家都對刪除物件比較敏感,對增加物件一般會放鬆警惕。而增加索引所帶來的危害和刪除索引同樣大。由於新增了一個新的索引,使得以前執行正常的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 恆等查詢條件改變執行計劃——SQL優化之Everything is PossibleSQL優化
- 增加Distinct後查詢效率反而提高——SQL優化之Everything is possibleSQL優化
- sql執行計劃是否改變SQL
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 11g 改變SQL執行計劃SQL
- UNION效率比UNION ALL效率高——SQL優化之Everything is possibleSQL優化
- SQL優化案例-改變那些CBO無能為力的執行計劃(一)SQL優化
- 【sql調優之執行計劃】獲取執行計劃SQL
- 【轉】UNION效率比UNION ALL效率高——SQL優化之Everything is possibleSQL優化
- 利用coe_xfr_sql_profile 改變執行計劃SQL
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- 使用hint改變執行計劃
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 兩層GROUP BY效率反而比一層GROUP BY效率高——SQL優化之Everything is possibleSQL優化
- 使用leading(,)優化sql執行計劃優化SQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- Oracle優化案例-改變那些CBO無能為力的執行計劃(一)Oracle優化
- PostgreSQL執行計劃變化SQL
- 【優化】Oracle 執行計劃優化Oracle
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL
- SQL優化案例-從執行計劃定位SQL問題(三)SQL優化
- 建立索引調整sql的執行計劃索引SQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- SQL最佳化案例-改變那些CBO無能為力的執行計劃(一)SQL
- 【Oracle】-【索引-HINT,執行計劃】-帶HINT的索引執行計劃Oracle索引
- 一條SQL語句的執行計劃變化探究SQL
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 轉摘_使用leading(,)優化sql執行計劃優化SQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- SQL優化之統計資訊和索引SQL優化索引
- 【優化】ORACLE執行計劃分析優化Oracle
- Oracle 通過註釋改變執行計劃Oracle
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化