带汇总的标量子查询改写

示例1：

点击(此处)折叠或打开

1. select p.prod_id,
2.   p.prod_name,
     p.prod_category,
     (select sum(s.amount_sold) 
     from sales s
     where s.prod_id = p.prod_id) as total_sold
   from products p
3. 
   
4. Plan hash value: 318513978
    
   -------------------------------------------------------------------------------
   | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT   |          |    72 |  3384 |     3   (0)| 00:00:01 |
   |   1 |  SORT AGGREGATE    |          |     1 |     7 |            |          |
   |*  2 |   TABLE ACCESS FULL| SALES    |   109K|   745K| 31298   (1)| 00:06:16 |
   |   3 |  TABLE ACCESS FULL | PRODUCTS |    72 |  3384 |     3   (0)| 00:00:01 |
   -------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      2 - filter("S"."PROD_ID"=:B1)
   
   

示例2的改写，左连接+内联视图：

点击(此处)折叠或打开

1. select p.prod_id,
2.   p.prod_name,
     p.prod_category,
     total_sold
   from products p
   left join (select s.prod_id, sum(s.amount_sold) as total_sold
             from sales s
             group by prod_id) s on s.prod_id = p.prod_id
3. 
   
4. Plan hash value: 1741282693
    
   ---------------------------------------------------------------------------------
   | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
   ---------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT     |          |    72 |  5256 | 31797   (2)| 00:06:22 |
   |*  1 |  HASH JOIN OUTER     |          |    72 |  5256 | 31797   (2)| 00:06:22 |
   |   2 |   TABLE ACCESS FULL  | PRODUCTS |    72 |  3384 |     3   (0)| 00:00:01 |
   |   3 |   VIEW               |          |   135 |  3510 | 31794   (2)| 00:06:22 |
   |   4 |    HASH GROUP BY     |          |   135 |   945 | 31794   (2)| 00:06:22 |
   |   5 |     TABLE ACCESS FULL| SALES    |    14M|    98M| 31365   (1)| 00:06:17 |
   ---------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      1 - access("S"."PROD_ID"(+)="P"."PROD_ID")
   
   

这里仍然是因为没有过滤条件，示例2的COST比示例1高。

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

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