用LEFT JOIN优化标量子查询

《Oracle查询优化改写》读书笔记

以下示例语句执行计划

点击(此处)折叠或打开

1. select a.cust_id,
2.   (select b.cust_id from customers2 b where b.cust_id = a.cust_id) b_cust_id,
     (select b.cust_first_name from customers2 b where b.cust_id = a.cust_id) b_cust_first_name,
     (select b.cust_last_name from customers2 b where b.cust_id = a.cust_id) b_cust_last_name
   from customers a
3. 
   
4. Plan hash value: 3094712696
    
   -------------------------------------------------------------------------------------
   | Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT     |              |  1626K|  9527K|   951   (1)| 00:00:12 |
   |*  1 |  TABLE ACCESS FULL   | CUSTOMERS2   | 19230 |   244K|     3   (0)| 00:00:01 |
   |*  2 |  TABLE ACCESS FULL   | CUSTOMERS2   | 19230 |   469K|     3   (0)| 00:00:01 |
   |*  3 |  TABLE ACCESS FULL   | CUSTOMERS2   | 19230 |   657K|     3   (0)| 00:00:01 |
   |   4 |  INDEX FAST FULL SCAN| CUSTOMERS_PK |  1626K|  9527K|   951   (1)| 00:00:12 |
   -------------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      1 - filter("B"."CUST_ID"=:B1)
      2 - filter("B"."CUST_ID"=:B1)
      3 - filter("B"."CUST_ID"=:B1)
    
   Note
   -----
      - dynamic sampling used for this statement (level=2)
   
   
   
   
   

按书中方法，以LEFT JOIN改写

点击(此处)折叠或打开

1. select a.cust_id,
   
2.   b.cust_id,
   
3.   b.cust_first_name,
   
4.   b.cust_last_name
   
5. from customers a
   
6.   left join customers2 b on a.cust_id = b.cust_id
8. Plan hash value: 555753586
    
   ----------------------------------------------------------------------------------------------
   | Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
   ----------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT      |              |  1626K|    82M|       | 19127   (1)| 00:03:50 |
   |*  1 |  HASH JOIN OUTER      |              |  1626K|    82M|    27M| 19127   (1)| 00:03:50 |
   |   2 |   INDEX FAST FULL SCAN| CUSTOMERS_PK |  1626K|  9527K|       |   951   (1)| 00:00:12 |
   |   3 |   TABLE ACCESS FULL   | CUSTOMERS2   |  1922K|    86M|       | 11412   (1)| 00:02:17 |
   ----------------------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      1 - access("A"."CUST_ID"="B"."CUST_ID"(+))
    
   Note
   -----
      - dynamic sampling used for this statement (level=2)
   
   
   
   
   

注意到，LEFT JOIN的COST更高，并没有起到优化效果。

原因分析：示例1中，以游标方式（ filter("B"."CUST_ID"=:B1) ）过滤了CUSTOMER2的数据源；而在示例2中，没有过滤，全表搜索了CUSTOMER2。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-2076465/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-2076465/