子查询内用nvl，到外层再关联怎么就像成笛卡尔积了

子查询内用nvl，到外层再关联怎么就像成笛卡尔积了
今天同事在公司写了个查询，就是在子查询里用了nvl，再到外城关联，成了笛卡尔
大概是这样的 create table object1  as
select distinct object_name nam
       ,1 as val 
from all_objects
;create table object2 nologging as select * from object1;
create table table1 nologging as select * from object1;
create table table2 nologging as select * from object1;exec dbms_stats.gather_table_stats('scott','object1',cascade=>true);
exec dbms_stats.gather_table_stats('scott','object2',cascade=>true);
exec dbms_stats.gather_table_stats('scott','table1',cascade=>true);
exec dbms_stats.gather_table_stats('scott','table2',cascade=>true);explain plan for
select count(1)
from (select a.nam
             ,a.val+b.val val 
     from object1 a,table1 b
     where a.nam=b.nam     
     )a
     ,(select a.nam
             ,a.val+b.val val 
     from object2 a,table2 b
     where a.nam=b.nam     
     )b
where a.nam=b.nam
and a.val>=2
and b.val>=2
;   
select * from table(dbms_xplan.display) ;PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3856415621---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |   110 |   115   (7)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |   110 |            |          |
|*  2 |   HASH JOIN           |         |    56 |  6160 |   115   (7)| 00:00:02 |
|*  3 |    HASH JOIN          |         |  1126 | 92332 |    86   (6)| 00:00:02 |
|*  4 |     HASH JOIN         |         |  1126 | 61930 |    57   (6)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| OBJECT2 | 22522 |   593K|    28   (4)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| TABLE2  | 22786 |   623K|    28   (4)| 00:00:01 |
|   7 |     TABLE ACCESS FULL | OBJECT1 | 22771 |   600K|    28   (4)| 00:00:01 |
|   8 |    TABLE ACCESS FULL  | TABLE1  | 22535 |   616K|    28   (4)| 00:00:01 |
---------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A"."NAM"="B"."NAM")
       filter("A"."VAL"+"B"."VAL">=2)
   3 - access("A"."NAM"="A"."NAM")
   4 - access("A"."NAM"="B"."NAM")
       filter("A"."VAL"+"B"."VAL">=2)explain plan for
select count(1)
from (select nvl(a.nam,b.nam) nam
             ,a.val+b.val val 
     from object1 a,table1 b
     where a.nam=b.nam     
     )a
     ,(select  nvl(a.nam,b.nam) nam
             ,a.val+b.val val 
     from object2 a,table2 b
     where a.nam=b.nam     
     )b
where a.nam=b.nam
and a.val>=2
and b.val>=2
;   
select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3388926023----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |   110 | 29501   (5)| 00:05:55 |
|   1 |  SORT AGGREGATE        |         |     1 |   110 |            |          |
|*  2 |   HASH JOIN            |         | 12688 |  1362K| 29501   (5)| 00:05:55 |
|   3 |    TABLE ACCESS FULL   | OBJECT1 | 22771 |   600K|    28   (4)| 00:00:01 |
|   4 |    MERGE JOIN CARTESIAN|         |    25M|  2008M| 29036   (4)| 00:05:49 |
|*  5 |     HASH JOIN          |         |  1126 | 61930 |    57   (6)| 00:00:01 |
|   6 |      TABLE ACCESS FULL | OBJECT2 | 22522 |   593K|    28   (4)| 00:00:01 |
|   7 |      TABLE ACCESS FULL | TABLE2  | 22786 |   623K|    28   (4)| 00:00:01 |
|   8 |     BUFFER SORT        |         | 22535 |   616K| 29009   (4)| 00:05:49 |
|   9 |      TABLE ACCESS FULL | TABLE1  | 22535 |   616K|    26   (4)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A"."NAM"="B"."NAM")
       filter(NVL("A"."NAM","B"."NAM")=NVL("A"."NAM","B"."NAM") AND
              "A"."VAL"+"B"."VAL">=2)
   5 - access("A"."NAM"="B"."NAM")
       filter("A"."VAL"+"B"."VAL">=2)2个脚本结果是一样的
第二个由于子查询用了nvl，其实可以不用的，只是复制其他脚本时本来用full join 的
以为不影响结果就没改了
到外层就成NVL("A"."NAM","B"."NAM")=NVL("A"."NAM","B"."NAM") 了，这不成了笛卡尔积了吗

解决方案 »

  1.   

    问题是执行结果相同
    但效率却相差甚远
    本来几秒的查询变成几分钟
    在公司的实际数据，本来半个钟能出来的，7天都没出子查询改成full join ，倒正常了explain plan for
    select count(1)
    from (select nvl(a.nam,b.nam) nam
                 ,a.val+b.val val 
         from object1 a
            full join table1 b
         on a.nam=b.nam     
         )a
         ,(select  nvl(a.nam,b.nam) nam
                 ,a.val+b.val val 
         from object2 a
            full join  table2 b
         on a.nam=b.nam     
         )b
    where a.nam=b.nam
    and a.val>=2
    and b.val>=2
    ;   
    select * from table(dbms_xplan.display) ;PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 3244338705-------------------------------------------------------------------------------------------
    | Id  | Operation               | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |         |     1 |    68 |       |   419  (25)| 00:00:06 |
    |   1 |  SORT AGGREGATE         |         |     1 |    68 |       |            |          |
    |*  2 |   HASH JOIN             |         |  5201K|   337M|  1024K|   419  (25)| 00:00:06 |
    |   3 |    VIEW                 |         | 22772 |   756K|       |   114   (6)| 00:00:02 |
    |   4 |     UNION-ALL           |         |       |       |       |            |          |
    |*  5 |      FILTER             |         |       |       |       |            |          |
    |*  6 |       HASH JOIN OUTER   |         | 22771 |  1223K|       |    57   (6)| 00:00:01 |
    |   7 |        TABLE ACCESS FULL| OBJECT1 | 22771 |   600K|       |    28   (4)| 00:00:01 |
    |   8 |        TABLE ACCESS FULL| TABLE1  | 22535 |   616K|       |    28   (4)| 00:00:01 |
    |*  9 |      HASH JOIN ANTI     |         |     1 |    53 |       |    57   (6)| 00:00:01 |
    |* 10 |       TABLE ACCESS FULL | TABLE1  |  1127 | 31556 |       |    28   (4)| 00:00:01 |
    |  11 |       TABLE ACCESS FULL | OBJECT1 | 22771 |   555K|       |    28   (4)| 00:00:01 |
    |  12 |    VIEW                 |         | 22843 |   758K|       |   114   (6)| 00:00:02 |
    |  13 |     UNION-ALL           |         |       |       |       |            |          |
    |* 14 |      FILTER             |         |       |       |       |            |          |
    |* 15 |       HASH JOIN OUTER   |         | 22522 |  1209K|       |    57   (6)| 00:00:01 |
    |  16 |        TABLE ACCESS FULL| OBJECT2 | 22522 |   593K|       |    28   (4)| 00:00:01 |
    |  17 |        TABLE ACCESS FULL| TABLE2  | 22786 |   623K|       |    28   (4)| 00:00:01 |
    |* 18 |      HASH JOIN ANTI     |         |   321 | 17013 |       |    57   (6)| 00:00:01 |
    |* 19 |       TABLE ACCESS FULL | TABLE2  |  1139 | 31892 |       |    28   (4)| 00:00:01 |
    |  20 |       TABLE ACCESS FULL | OBJECT2 | 22522 |   549K|       |    28   (4)| 00:00:01 |
    -------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
    ---------------------------------------------------   2 - access(NVL("A"."NAM","B"."NAM")=NVL("A"."NAM","B"."NAM"))
       5 - filter("A"."VAL"+"B"."VAL">=2)
       6 - access("A"."NAM"="B"."NAM"(+))
       9 - access("A"."NAM"="B"."NAM")
      10 - filter(TO_NUMBER(NULL)+"B"."VAL">=2)
      14 - filter("A"."VAL"+"B"."VAL">=2)
      15 - access("A"."NAM"="B"."NAM"(+))
      18 - access("A"."NAM"="B"."NAM")
      19 - filter(TO_NUMBER(NULL)+"B"."VAL">=2)
      

  2.   

    ----------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 110 | 29501 (5)| 00:05:55 |
    | 1 | SORT AGGREGATE | | 1 | 110 | | |
    |* 2 | HASH JOIN | | 12688 | 1362K| 29501 (5)| 00:05:55 |
    | 3 | TABLE ACCESS FULL | OBJECT1 | 22771 | 600K| 28 (4)| 00:00:01 |
    | 4 | MERGE JOIN CARTESIAN| | 25M| 2008M| 29036 (4)| 00:05:49 |
    |* 5 | HASH JOIN | | 1126 | 61930 | 57 (6)| 00:00:01 |
    | 6 | TABLE ACCESS FULL | OBJECT2 | 22522 | 593K| 28 (4)| 00:00:01 |
    | 7 | TABLE ACCESS FULL | TABLE2 | 22786 | 623K| 28 (4)| 00:00:01 |
    | 8 | BUFFER SORT | | 22535 | 616K| 29009 (4)| 00:05:49 |
    | 9 | TABLE ACCESS FULL | TABLE1 | 22535 | 616K| 26 (4)| 00:00:01 |
    ----------------------------------------------------------------------------------关键是上面的那一个排序操作，COST太高