用WITH…AS改写标量子查询

示例1：

点击(此处)折叠或打开

1. select prod_id,
2.   prod_name,
     prod_list_price,
     (select max(quantity_sold)
       from sales s
       where s.prod_id = p.prod_id
         and s.time_id > p.prod_eff_from ) as max_quantity_sold,
     (select max(amount_sold)
       from sales s
       where s.prod_id = p.prod_id
         and s.time_id < p.prod_eff_to ) as max_amount_sold
   from products p
3. 
   Plan hash value: 1097661653
    
   -----------------------------------------------------------------------------------------
   | Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
   -----------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |          |    72 |  3168 |     3   (0)| 00:00:01 |
   |   1 |  SORT AGGREGATE              |          |     1 |    15 |            |          |
   |   2 |   TABLE ACCESS BY INDEX ROWID| SALES    |  5455 | 81825 |  1512   (1)| 00:00:19 |
   |*  3 |    INDEX RANGE SCAN          | SALES_PK |   982 |       |   530   (1)| 00:00:07 |
   |   4 |  SORT AGGREGATE              |          |     1 |    15 |            |          |
   |   5 |   TABLE ACCESS BY INDEX ROWID| SALES    |  5455 | 81825 |  1512   (1)| 00:00:19 |
   |*  6 |    INDEX RANGE SCAN          | SALES_PK |   982 |       |   530   (1)| 00:00:07 |
   |   7 |  TABLE ACCESS FULL           | PRODUCTS |    72 |  3168 |     3   (0)| 00:00:01 |
   -----------------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      3 - access("S"."PROD_ID"=:B1 AND "S"."TIME_ID">:B2)
          filter("S"."TIME_ID">:B1)
      6 - access("S"."PROD_ID"=:B1 AND "S"."TIME_ID"<:B2)
          filter("S"."TIME_ID"<:B1)
   
   
   
   
   

示例2：

点击(此处)折叠或打开

1. WITH s2 AS
2.   (select p.rowid as rid,
       max(case
           when s.time_id > p.prod_eff_from then 
             s.quantity_sold
           END) AS max_quantity_sold,
       max(case
           when s.time_id < p.prod_eff_to then 
             s.amount_sold
           END) AS max_amount_sold
     from sales s
     inner join products p on p.prod_id = s.prod_id
     group by p.rowid)
   select prod_id,
     prod_name,
     prod_list_price,
     s2.max_quantity_sold,
     s2.max_amount_sold
   from products p
   left join s2 on s2.rid = p.rowid
   
3. Plan hash value: 983752392
    
   ----------------------------------------------------------------------------------
   | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
   ----------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT      |          |  7855K|   636M| 31653   (2)| 00:06:20 |
   |*  1 |  HASH JOIN OUTER      |          |  7855K|   636M| 31653   (2)| 00:06:20 |
   |   2 |   TABLE ACCESS FULL   | PRODUCTS |    72 |  3384 |     3   (0)| 00:00:01 |
   |   3 |   VIEW                |          |  7855K|   284M| 31629   (2)| 00:06:20 |
   |   4 |    HASH GROUP BY      |          |  7855K|   322M| 31629   (2)| 00:06:20 |
   |*  5 |     HASH JOIN         |          |  7855K|   322M| 31408   (1)| 00:06:17 |
   |   6 |      TABLE ACCESS FULL| PRODUCTS |    72 |  1800 |     3   (0)| 00:00:01 |
   |   7 |      TABLE ACCESS FULL| SALES    |    14M|   252M| 31365   (1)| 00:06:17 |
   ----------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
    
      1 - access("S2"."RID"(+)="P".ROWID)
      5 - access("P"."PROD_ID"="S"."PROD_ID")
   
   

标量子查询完胜！

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

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