Oracle技术之如何分析执行计划（二）

在从执行计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表（就像上面判断ABC表那样）是否合适，如果不合适，对SQL语句进行更改，使优化器可以选择正确的驱动表。

1、对于RBO优化器：

在ORACLE文档上说：对于RBO来说，以from 子句中从右到左的顺序选择驱动表，即最右边的表为第一个驱动表，这是其英文原文：All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT。不过，在我做的测试中，从来也没有验证过这种说法是正确的。我认为，即使在RBO中，也是有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑当前索引的情况，还可能会考虑where 中的限制条件，但是肯定是与where中限制条件的位置无关。

测试：

如果我创建3个表：

create table A（col1 number（4,0），col2 number（4,0）， col4 char（30））；

create table B（col1 number（4,0），col3 number（4,0）， name_b char（30））；

create table C（col2 number（4,0），col3 number（4,0）， name_c char（30））；

create index inx_col12A on a（col1,col2）；

执行查询：

select A.col4

from B, A, C

where B.col3 = 10

and A.col1 = B.col1

and A.col2 = C.col2

and C.col3 = 5;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 MERGE JOIN

2 1 SORT （JOIN）

3 2 NESTED LOOPS

4 3 TABLE ACCESS （FULL） OF 'B'

5 3 TABLE ACCESS （BY INDEX ROWID） OF 'A'

6 5 INDEX （RANGE SCAN） OF 'INX_COL12A' （NON-UNIQUE）

7 1 SORT （JOIN）

8 7 TABLE ACCESS （FULL） OF 'C'

select A.col4

from B, A, C

where A.col1 = B.col1

and A.col2 = C.col2;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 MERGE JOIN

2 1 SORT （JOIN）

3 2 NESTED LOOPS

4 3 TABLE ACCESS （FULL） OF 'B'

5 3 TABLE ACCESS （BY INDEX ROWID） OF 'A'

6 5 INDEX （RANGE SCAN） OF 'INX_COL12A' （NON-UNIQUE）

7 1 SORT （JOIN）

8 7 TABLE ACCESS （FULL） OF 'C'

将A表上的索引inx_col12A删除后：

select A.col4

from B, A, C

where A.col1 = B.col1

and A.col2 = C.col2;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 MERGE JOIN

2 1 SORT （JOIN）

3 2 MERGE JOIN

4 3 SORT （JOIN）

5 4 TABLE ACCESS （FULL） OF 'C'

6 3 SORT （JOIN）

7 6 TABLE ACCESS （FULL） OF 'A'

8 1 SORT （JOIN）

9 8 TABLE ACCESS （FULL） OF 'B'

通过上面的这些例子，使我对oracle文档上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”这句话持怀疑态度。此时，我也不能使用hints来强制优化器使用nested loop，如果使用了hints，这样就自动使用CBO优化器，而不是RBO优化器了。

2、对于CBO优化器：

CBO根据统计信息选择驱动表，假如没有统计信息，则在from 子句中从左到右的顺序选择驱动表。这与RBO选择的顺序正好相反。这是英文原文（CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO） 。我还是没法证实这句话的正确性。不过经过验证：“如果用ordered 提示（此时肯定用CBO），则以from 子句中按从左到右的顺序选择驱动表”这句话是正确的。实际上在CBO中，如果有统计数据（即对表与索引进行了分析），则优化器会自动根据cost值决定采用哪种连接类型，并选择合适的驱动表，这与where子句中各个限制条件的位置没有任何关系。如果我们要改变优化器选择的连接类型或驱动表，则就需要使用hints了，具体hints的用法在后面会给予介绍。

测试：

如果我创建的3个表：

create table A（col1 number（4,0），col2 number（4,0）， col4 char（30））；

create table B（col1 number（4,0），col3 number（4,0）， name_b char（30））；

create table C（col2 number（4,0），col3 number（4,0）， name_c char（30））；

create index inx_col12A on a（col1,col2）；

执行查询：

select A.col4

from B, A, C

where B.col3 = 10

and A.col1 = B.col1

and A.col2 = C.col2

and C.col3 = 5;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS （Cost=3 Card=1 Bytes=110）

1 0 NESTED LOOPS （Cost=3 Card=1 Bytes=110）

2 1 MERGE JOIN （CARTESIAN） （Cost=2 Card=1 Bytes=52）

3 2 TABLE ACCESS （FULL） OF 'B' （Cost=1 Card=1 Bytes=26）

4 2 SORT （JOIN） （Cost=1 Card=1 Bytes=26）

5 4 TABLE ACCESS （FULL） OF 'C' （Cost=1 Card=1 Bytes=26）

6 1 TABLE ACCESS （FULL） OF 'A' （Cost=1 Card=82 Bytes=4756）

select A.col4

from B, A, C

where A.col1 = B.col1

and A.col2 = C.col2;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS （Cost=5 Card=55 Bytes=4620）

1 0 HASH JOIN （Cost=5 Card=55 Bytes=4620）

2 1 HASH JOIN （Cost=3 Card=67 Bytes=4757）

3 2 TABLE ACCESS （FULL） OF 'B' （Cost=1 Card=82 Bytes=1066）

4 2 TABLE ACCESS （FULL） OF 'A' （Cost=1 Card=82 Bytes=4756）

5 1 TABLE ACCESS （FULL） OF 'C' （Cost=1 Card=82 Bytes=1066）

将A表上的索引inx_col12A删除后：

select A.col4

from B, A, C

where A.col1 = B.col1

and A.col2 = C.col2;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS （Cost=5 Card=55 Bytes=4620）

1 0 HASH JOIN （Cost=5 Card=55 Bytes=4620）

2 1 HASH JOIN （Cost=3 Card=67 Bytes=4757）

3 2 TABLE ACCESS （FULL） OF 'B' （Cost=1 Card=82 Bytes=1066）

4 2 TABLE ACCESS （FULL） OF 'A' （Cost=1 Card=82 Bytes=4756）

5 1 TABLE ACCESS （FULL） OF 'C' （Cost=1 Card=82 Bytes=1066）

select /*+ ORDERED */A.col4

from C, A, B

where B.col3 = 10

and A.col1 = B.col1

and A.col2 = C.col2

and C.col3 = 5;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS （Cost=3 Card=1 Bytes=110）

1 0 NESTED LOOPS （Cost=3 Card=1 Bytes=110）

2 1 NESTED LOOPS （Cost=2 Card=1 Bytes=84）

3 2 TABLE ACCESS （FULL） OF 'C' （Cost=1 Card=1 Bytes=26）

4 2 TABLE ACCESS （FULL） OF 'A' （Cost=1 Card=82 Bytes=4756）

5 1 TABLE ACCESS （FULL） OF 'B' （Cost=1 Card=1 Bytes=26）

这个查询验证了通过ORDERED提示可以正确的提示优化器选择哪个表作为优化器。

oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html