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

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

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

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

控制執行計劃最簡單的方法莫過於使用HINT，這篇文章要介紹的是，在不使用HINT的情況下，讓Oracle產生INDEX_JOIN執行計劃。

下面先構造查詢所用的表，問題中使用的表是HR使用者下的EMPLOYEES。如果hr使用者不存在，Oracle9i可以透過$ORACLE_HOME/demo/schema/human_resources/hr_main.sql來建立使用者。

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

表已建立。

SQL> SELECT COUNT(*) FROM EMPLOYEES;

COUNT(*)
----------
107

已選擇 1 行。

查詢語句為：

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

已選擇107行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES'

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

需要採用索引連線，首先將兩個索引建立起來。而且索引連線是CBO才能採用的執行計劃，因此對錶和索引進行分析。

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：

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

Oracle執行的仍然是全表掃描，但是最佳化器以及是CBO了。先透過HINT，看看現在是否已經滿足了INDEX_JOIN執行計劃的執行條件：

SQL> SELECT /*+ INDEX_JOIN(EMPLOYEES PK_EMPLOYEES IND_EMP_SALARY) */
2 EMPLOYEE_ID, SALARY
3 FROM EMPLOYEES
4 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的條件已經滿足，Oracle之所以沒有選擇INDEX_JOIN而選擇全表掃描，是因為Oracle認為全表掃描的代價比INDEX_JOIN要低。

不使用HINT，而讓Oracle選擇INDEX_JOIN，就必須讓Oracle認為全表掃描的代價比INDEX_JOIN要高。

最直接的辦法是修改Oracle收集的統計資訊，透過這種方法來使Oracle認為表掃描的搭建遠遠大於透過索引連線的代價。

SQL> SELECT NUM_ROWS, BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'EMPLOYEES';

NUM_ROWS BLOCKS
---------- ----------
107 2

現在的統計資訊顯示，全部資料儲存在兩個BLOCK中，Oracle當然認為全部掃描的代價低，如果設定表統計資訊中BLOCK的數量很大，Oracle就會認識到全表掃描的代價比較大。

SQL> EXEC DBMS_STATS.SET_TABLE_STATS(USER, 'EMPLOYEES', NUMROWS => 100, NUMBLKS => 100)

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=100 Bytes=800)
1 0 VIEW OF 'index$_join$_001' (Cost=4 Card=100 Bytes=800)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'IND_EMP_SALARY' (NON-UNIQUE) (Cost=3 Card=100 Bytes=800)
4 2 INDEX (FAST FULL SCAN) OF 'PK_EMPLOYEES' (UNIQUE) (Cost=3 Card=100 Bytes=800)

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的統計資訊的方法，讓目標執行計劃的代價最小，從而使得Oracle選擇了預期的執行計劃。