oracle如何生成新的执行计划,如何让Oracle产生预期的执行计划（一）

在测试执行计划、SQL优化、对比不同执行计划的效率时，让Oracle产生预期的执行计划还是很有意义的。

这里就以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选择了预期的执行计划。