如何让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(*)ITPUB个人空间 T9?*Y D6u0e m'U
----------ITPUB个人空间^S4JJ9d;u&mG jj
107

已选择 1 行。

查询语句为：

SQL> SET AUTOT TRACE
}5z;f!Q0\ r
B |8c0SQL> SELECT EMPLOYEE_ID, SALARY
7lb-c Z$KX6l3x0 2 FROM EMPLOYEESITPUB个人空间'h.[3k7vR\vb2z.O(S
3 WHERE SALARY > 2000;

已选择107行。

e"n
N|E4?0Execution Plan
+qqm0W$_;bq)}0----------------------------------------------------------
1u|%^Z7Qa | Ln5\0 0 SELECT STATEMENT Optimizer=CHOOSEITPUB个人空间r9h ryy3m/gb^
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES'

;uqcl/P!s0Statistics

v6~J~N2~ gFn#]0----------------------------------------------------------
cUw!uZ7g_0 0 recursive callsITPUB个人空间cl"sjm
\/Lf$L `
0 db block gets
|,ZS*N6^1j[v0 12 consistent getsITPUB个人空间`-my ^%u"Y
0 physical reads
7s"i[#p1D r7?0 0 redo sizeITPUB个人空间]5NWdPW,t7I8^
2273 bytes sent via SQL*Net to clientITPUB个人空间]N~EwhYQ
580 bytes received via SQL*Net from client
WA8Gg,E8td0 9 SQL*Net roundtrips to/from clientITPUB个人空间1LHYJNw}
0 sorts (memory)ITPUB个人空间&m"ls"L:fit[
0 sorts (disk)ITPUB个人空间"un ox$Hr7\/x
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, SALARYITPUB个人空间9}!y!D l\c,?TM/p
2 FROM EMPLOYEES
ryq-q2~,yt5h0 3 WHERE SALARY > 2000;

已选择107行。

ITPUB个人空间(J3vKd?Rc
Execution Plan
#G8K.P;HxLJ'~O0----------------------------------------------------------
Zp'f ev0 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=107 Bytes=856)ITPUB个人空间)I
YY)Ge
G XB
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=856)

C[H(Lfy0Statistics
*V7d9I#| C/Q/E0----------------------------------------------------------ITPUB个人空间;[j.[,i5sx(O1T;\
0 recursive callsITPUB个人空间f"a'N M1Z1Ya:oC
0 db block gets
FEN#~7F.u5i0 12 consistent gets
$A#{4yGi-q(} s_E0 0 physical reads
qa+J$S"w8G(dP,q0 0 redo size
1D n7d c"d){3Gv1F0 2273 bytes sent via SQL*Net to client
-Zo;`"h)p&rFr0 580 bytes received via SQL*Net from client
NyyJ)puN0 9 SQL*Net roundtrips to/from client
G^2R:KK0 0 sorts (memory)
:`%P:gpj(if~0 0 sorts (disk)ITPUB个人空间*Y0m;mV#|w;dUL
107 rows processed

Oracle执行的仍然是全表扫描，但是优化器以及是CBO了。先通过HINT，看看现在是否已经满足了INDEX_JOIN执行计划的执行条件：

SQL> SELECT /*+ INDEX_JOIN(EMPLOYEES PK_EMPLOYEES IND_EMP_SALARY) */ ITPUB个人空间 DR0|]q;Fkm
2 EMPLOYEE_ID, SALARYITPUB个人空间NIoU!e_ L%S
3 FROM EMPLOYEES
U_:YJ e9_"vd0 4 WHERE SALARY > 2000;

已选择107行。

ITPUB个人空间jWDsaXU:iS
Execution Plan
Z%c:xLb8C x0----------------------------------------------------------ITPUB个人空间'L T"{,Q&w&e#b
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=107 Bytes=856)
?)g1k5M#dQ0 1 0 VIEW OF 'index$_join$_001' (Cost=4 Card=107 Bytes=856)
0hu2RS#^ u^)B0 2 1 HASH JOINITPUB个人空间'\1M gjV,|y
3 2 INDEX (RANGE SCAN) OF 'IND_EMP_SALARY' (NON-UNIQUE) (Cost=3 Card=107 Bytes=856)
[2}~1U5k,e0 4 2 INDEX (FAST FULL SCAN) OF 'PK_EMPLOYEES' (UNIQUE) (Cost=3 Card=107 Bytes=856)

ITPUB个人空间8H;t"X,cA:}
Statistics
2U q;R'xte0----------------------------------------------------------ITPUB个人空间;eM,k
F9N"~'y
0 recursive calls
(l%x-o"DNw.^J0 0 db block getsITPUB个人空间z9U$paWVA)@4R
12 consistent gets
9\-t%m*]a0 0 physical reads
z:K)W#O4lo0 0 redo sizeITPUB个人空间M#~Mr|?QL
2273 bytes sent via SQL*Net to clientITPUB个人空间 e)L;d:uT
580 bytes received via SQL*Net from clientITPUB个人空间6Q'Y/eLn~%RQp1vls J
9 SQL*Net roundtrips to/from clientITPUB个人空间-h8FPbkP F"p$]
0 sorts (memory)ITPUB个人空间2}zwEb!cC
0 sorts (disk)ITPUB个人空间t1F[x];WzjL
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 BLOCKSITPUB个人空间O*`J5{A
u1s
---------- ----------
k]-E+bOq?![0 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, SALARYITPUB个人空间Br8Rd,Q4H
2 FROM EMPLOYEESITPUB个人空间;g0w-v+k(EU_~y
3 WHERE SALARY > 2000;

已选择107行。

ITPUB个人空间;kw`m uF(Z%@T(c
Execution Plan
p |k pl[7\0----------------------------------------------------------ITPUB个人空间 @:C*g _J'F K}@L$d
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=100 Bytes=800)
^`[/| e p4n
k0 1 0 VIEW OF 'index$_join$_001' (Cost=4 Card=100 Bytes=800)ITPUB个人空间,{ F7iKG
2 1 HASH JOIN
kZeZ9HfE0 3 2 INDEX (RANGE SCAN) OF 'IND_EMP_SALARY' (NON-UNIQUE) (Cost=3 Card=100 Bytes=800)
bA$bd%m|K0 4 2 INDEX (FAST FULL SCAN) OF 'PK_EMPLOYEES' (UNIQUE) (Cost=3 Card=100 Bytes=800)

ITPUB个人空间(OA&yZ5i&e
Statistics
&?({p)k(e
O/YC0----------------------------------------------------------ITPUB个人空间c|(W XJ,[h
0 recursive callsITPUB个人空间,wV)R$I!F
0 db block getsITPUB个人空间
]\
i%q%|(X5b{
12 consistent getsITPUB个人空间P _
ze7o'WNnnP%a
0 physical readsITPUB个人空间m+fy?0e7x
0 redo sizeITPUB个人空间[.YS+f$~
2273 bytes sent via SQL*Net to clientITPUB个人空间"y-w4EI7Sc
580 bytes received via SQL*Net from client
&tw4O#M"t c0 9 SQL*Net roundtrips to/from client
']E-V5M`2cp0 0 sorts (memory)ITPUB个人空间_fhhgf-w4b
0 sorts (disk)
(kI1rSkPZs6q0 107 rows processed

通过设置Oracle的统计信息的方法，让目标执行计划的代价最小，从而使得Oracle选择了预期的执行计划。