将“相关值”导致的Nested Loop优化成Hash Join (转）-优快云博客

http://www.cnblogs.com/killkill/archive/2011/04/11/2013044.html

原文地址：http://www.itpub.net/thread-1376537-1-2.html

“相关值”是我剽窃的一个词，与之相反的是“不相关值”，我借用来描述以下这种情况：在SQL中有两个表A和B要join，join条件是A.name=B.first_name，判定符“=”左右两边的值是“不相关”的，而substr(A.name,1,length(B.first_name))=B.first_name，判定符“=”左右两边的值是“相关”的，因为要通过“=”右边的值才能求出左边的值（相反亦然）。

由于这种“相关值”的情况，A、B表join的算法注定只能走Nested Loop了，因为每一个不同的驱动值（驱动表中的值，也就是B表中的值）将改变A表负责join的值，执行计划如下：

1	select a.,b.object_name from* TEST_OBJECT a, TEST_OBJ1 b

2	where substr(a.object_name,1,length(b.object_name))=b.object_name;

 
    01已选择108612行。 
 
    02  
 
    03已用时间:  00: 00: 21.54 
 
    04  
 
    05--------------------------------------------------------------------- 
 
    06| Id  | Operation            |  Name        | Rows  | Bytes | Cost  | 
 
    07--------------------------------------------------------------------- 
 
    08|   0 | SELECT STATEMENT     |              |       |       |       | 
 
    09|   1 |  NESTED LOOPS        |              |       |       |       | 
 
    10|   2 |   TABLE ACCESS FULL  | TEST_OBJ1    |       |       |       | 
 
    11|*  3 |   TABLE ACCESS FULL  | TEST_OBJECT  |       |       |       | 
 
    12--------------------------------------------------------------------- 
 
    13  
 
    14Predicate Information (identified by operation id): 
 
    15--------------------------------------------------- 
 
    16  
 
    17   3 - filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJ 
 
    18              ECT_NAME"))) 
 
    19  
 
    20统计信息 
 
    21---------------------------------------------------------- 
 
    22          1  recursive calls 
 
    23          0  db block gets 
 
    24    9262734  consistent gets

这里展开一下，Nested Loop这个算法复杂度是 O(B*A) （B表示驱动表的记录数，A表示内部表），如果A、B都很大，那就杯具了，关于Nested Loop驱动表的选取就不展开了。

网友anlinew提供了一种非常精妙的，决的优化方法，将语句改写成：

 
    1select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b 
 
    2where substr(a.object_name,1,length(b.object_name))=b.object_name 
 
    3and substr(a.object_name,1,4)=substr(b.object_name,1,4)   
 
    4and length(b.object_name)>3 
 
    5union all
 
    6select a.*,b.object_name from TEST_OBJECT a, TEST_OBJ1 b 
 
    7where substr(a.object_name,1,length(b.object_name))=b.object_name 
 
    8and length(b.object_name)<4;

 
    01已选择108612行。 
 
    02  
 
    03已用时间:  00: 06: 51.24 
 
    04  
 
    05----------------------------------------------------------------------------------- 
 
    06| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | 
 
    07----------------------------------------------------------------------------------- 
 
    08|   0 | SELECT STATEMENT    |             |  3468 |   365K| 90997 (100)| 00:18:12 | 
 
    09|   1 |  UNION-ALL          |             |       |       |            |          | 
 
    10|*  2 |   HASH JOIN         |             |    34 |  3672 |   176   (2)| 00:00:03 | 
 
    11|*  3 |    TABLE ACCESS FULL| TEST_OBJ1   |   659 | 12521 |    35   (0)| 00:00:01 | 
 
    12|   4 |    TABLE ACCESS FULL| TEST_OBJECT | 52544 |  4566K|   139   (1)| 00:00:02 | 
 
    13|   5 |   NESTED LOOPS      |             |  3434 |   362K| 90821   (1)| 00:18:10 | 
 
    14|*  6 |    TABLE ACCESS FULL| TEST_OBJ1   |   659 | 12521 |    35   (0)| 00:00:01 | 
 
    15|*  7 |    TABLE ACCESS FULL| TEST_OBJECT |     5 |   445 |   138   (1)| 00:00:02 | 
 
    16----------------------------------------------------------------------------------- 
 
    17  
 
    18Predicate Information (identified by operation id): 
 
    19--------------------------------------------------- 
 
    20  
 
    21   2 - access(SUBSTR("A"."OBJECT_NAME",1,4)=SUBSTR("B"."OBJECT_NAME",1,4)) 
 
    22       filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJE 
 
    23              CT_NAME"))) 
 
    24   3 - filter(LENGTH("B"."OBJECT_NAME")>3) 
 
    25   6 - filter(LENGTH("B"."OBJECT_NAME")<4) 
 
    26   7 - filter("B"."OBJECT_NAME"=SUBSTR("A"."OBJECT_NAME",1,LENGTH("B"."OBJE 
 
    27              CT_NAME"))) 
 
    28  
 
    29统计信息 
 
    30---------------------------------------------------------- 
 
    31          1  recursive calls 
 
    32          0  db block gets 
 
    33      33946  consistent gets