如何讓Oracle產生預期的執行計劃（二）

在測試執行計劃、SQL最佳化、對比不同執行計劃的效率時，讓Oracle產生預期的執行計劃還是很有意義的。

前兩天在PUB上看到一個帖子，問為什麼一個查詢沒有產生INDEX_JOIN執行計劃。http://www.itpub.net/showthread.php?s=&threadid=829318

這裡就以INDEX_JOIN為例，簡單描述一下如何影響Oracle的執行計劃的產生。

介紹另一種改變執行計劃的方法。

如何讓Oracle產生預期的執行計劃（一）：http://yangtingkun.itpub.net/post/468/357542

上一篇文章介紹了利用DBMS_STATS包來設定統計資訊的方法，其實透過修改表結構可以達到同樣的目的。

SQL> DROP TABLE EMPLOYEES;

表已丟棄。

SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES;

表已建立。

SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID);

表已更改。

SQL> CREATE INDEX IND_EMP_SALARY ON EMPLOYEES (SALARY);

索引已建立。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE => TRUE)

PL/SQL 過程已成功完成。

SQL> SET AUTOT TRACE
SQL> SELECT EMPLOYEE_ID, SALARY
2 FROM EMPLOYEES
3 WHERE SALARY > 2000;

已選擇107行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=107 Bytes=856)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=856)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

首先重新建立EMPLOYEES表，收集統計資訊，目前的執行計劃以及恢復為全表掃描。

為了讓Oracle選擇INDEX_JOIN，就要使得表的訪問代價增加，透過給表新增列的方法就可以解決這個問題。

SQL> ALTER TABLE EMPLOYEES ADD
2 (
3 COL1 CHAR(2000) DEFAULT '1'
4 );

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE => TRUE)

PL/SQL 過程已成功完成。

SQL> SELECT EMPLOYEE_ID, SALARY
2 FROM EMPLOYEES
3 WHERE SALARY > 2000;

已選擇107行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=107 Bytes=856)
1 0 VIEW OF 'index$_join$_001' (Cost=4 Card=107 Bytes=856)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'IND_EMP_SALARY' (NON-UNIQUE) (Cost=3 Card=107 Bytes=856)
4 2 INDEX (FAST FULL SCAN) OF 'PK_EMPLOYEES' (UNIQUE) (Cost=3 Card=107 Bytes=856)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

透過修改表結構的方法，使得表的全表掃描代價變大，Oracle因此選擇INDEX_JOIN執行計劃。

透過HINT強制全表掃描，發現物理是COST還是實際邏輯讀，全表掃描都比INDEX_JOIN差。

SQL> SELECT /*+ FULL(EMPLOYEES) */
2 EMPLOYEE_ID, SALARY
3 FROM EMPLOYEES
4 WHERE SALARY > 2000;

已選擇107行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=107 Bytes=856)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=5 Card=107 Bytes=856)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
2610 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed