分析一个号称拥有“专利”的SQL

先来看一本书（见下图），作者是业界鼎鼎有名的大师级人物Richard Niemiec，从9i、10g到现在的11g，这个优化系列的书，每一本都是厚厚的千页左右，可见大师的称号不是浪得虚名。下面这部最近几年翻译出版的11gR2，书的译者也都是老虎刘非常熟悉和敬佩的几位老朋友，当时也都在甲骨文供职。其中主要的3位译者，杨中是老虎刘在RWP时的老板，朱波和吕学勇在进入甲骨文之前也和老虎刘在同一家公司。这是一本全面介绍Oracle性能相关知识的好书，值得推荐。

书买了有两年多，一直没时间仔细去看。最近有空在翻看这本书的时候，发现了这样一个号称带有“专利”的sql 优化方法，看下面的两个截图：

既然这么神奇，老虎刘就实际测试了一下，结果却是“出人意料”（因为可疑所以验证）。

我不知道Richard 大师是在哪个版本上测试得到一开始那个非常差的执行计划：两表join 使用merge join cartesian-笛卡尔积。 出现这种情况非常可能是优化器的bug，我在11203版本并没有得到那样的执行计划，实际的执行计划与书中的最终执行计划相似，只是Hash join那一步少了个SEMI。

下面的测试结果验证了书上说的两种情况和老虎刘使用hint进行优化的两种情况的效率：

两张表：t100k和t200k，后者是前者的2倍复制关系。 t200k表有ID字段上的索引。（两表都收集了统计信息）

两表的数据分布可能与书中表的数据分布不太一致，但应该能反映出其中的主要问题。

SQL> select min(id),max(id) from t100k;

MIN(ID) MAX(ID)

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

1 100000

SQL> select min(id),max(id) from t200k;

MIN(ID) MAX(ID)

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

1 100000

SQL> select count(*) from t100k;

COUNT(*)

----------

100000

SQL> select count(*) from t200k;

COUNT(*)

----------

200000

先看两个书中sql写法的性能比较：

SQL> set autotrace on

SQL> select count(*) from t100k a,t200k b where a.id=b.id and rownum=1;

COUNT(*)

----------

1

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 1 |

| 1 | SORT AGGREGATE | | 1 |

|* 2 | COUNT STOPKEY | | |

|* 3 | HASH JOIN | | 198K|

| 4 | TABLE ACCESS FULL | T100K | 100K|

| 5 | INDEX FAST FULL SCAN| IDX_T200K | 200K|

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM=1)

3 - access("A"."ID"="B"."ID")

Statistics

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

1 recursive calls

0 db block gets

1494 consistent gets

......

SQL> select count(*) from t100k a where exists (select 1 from t200k b where a.id=b.id) and rownum=1;

COUNT(*)

----------

1

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 1 |

| 1 | SORT AGGREGATE | | 1 |

|* 2 | COUNT STOPKEY | | |

|* 3 | HASH JOIN SEMI | | 100K|

| 4 | TABLE ACCESS FULL | T100K | 100K|

| 5 | INDEX FAST FULL SCAN| IDX_T200K | 200K|

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM=1)

3 - access("A"."ID"="B"."ID")

Statistics

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

1 recursive calls

0 db block gets

1494 consistent gets

......

两种写法的buffer gets居然是相同的。这也是测试前预期的一个结果。

===================================================

再来看看老虎刘使用hint对这个SQL进行优化后的效率：

第一个SQL，使用nested loop，不使用hash join：

SQL> select /*+ leading(a b) use_nl(b) */count(*) from t100k a,t200k b where a.id=b.id and rownum=1;

COUNT(*)

----------

1

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 1 |

| 1 | SORT AGGREGATE | | 1 |

|* 2 | COUNT STOPKEY | | |

| 3 | NESTED LOOPS | | 198K|

| 4 | TABLE ACCESS FULL| T100K | 100K|

|* 5 | INDEX RANGE SCAN | IDX_T200K | 2 |

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM=1)

5 - access("A"."ID"="B"."ID")

Statistics

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

1 recursive calls

0 db block gets

6 consistent gets

......

第二个SQL，不做unnest，执行计划会使用filter操作：

SQL> select count(*) from t100k a where exists (select /*+ no_unnest */1 from t200k b where a.id=b.id) and rownum=1;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 3730640639

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

|* 2 | COUNT STOPKEY | |

|* 3 | FILTER | |

| 4 | TABLE ACCESS FULL| T100K |

|* 5 | INDEX RANGE SCAN | IDX_T200K |

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM=1)

3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T200K" "B" WHERE

"B"."ID"=:B1))

5 - access("B"."ID"=:B1)

Statistics

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

1 recursive calls

0 db block gets

6 consistent gets

......

测试结果：

前两种按照书上的写法，效率并没有差别，都是1494个consistent gets，而使用了hint进行优化后，两种方法都只有6个consistent gets。

既然我们得到了这样的一个结果，是不是就可以对这样的SQL使用hint进行优化呢？

答案是不一定。

因为两表的数据分布决定了这个SQL的效率。我们还要考虑如果两表join之后，没有匹配的记录的情况（或者是因为数据分布原因，两表需要扫描大量数据之后才能匹配的情况），如果是这种情况，使用hint的方法，很有可能需要比没有hint的方法更多的consistent gets（根据执行计划可以判断）。我这里没有把这种情况的测试结果写出来，有兴趣的朋友可以自己测试一下。

总结：

根据SQL的写法及生成的执行计划，可以判断出这个SQL并不会通过改写一下就提升很多性能的神奇效果，那是一个不太恰当的结论。只能说在一个错误的执行计划的比较下，一个相对正常的执行计划就显得非常“神奇”了。我不知道1990年的oracle数据库是不是存在这种神奇的情况，至少在11gR2，是没有这种情况的。

具体如何优化这个SQL，我们还要看表的具体数据分布情况：

如果两表根据关联条件能够匹配的记录较多，我们就可以使用上面的两种hint来优化；

如果两表匹配的记录数很少，或者大部分是没有匹配的，还是用不加hint的SQL效率高一些。而如果t100k是一个只有几千条记录的小表，则使用use_nl的hint的效率还是会好一些。

总之，优化无定式，需要根据数据分布情况，结合人脑优化器，来判断SQL到底使用哪一种执行计划更优。