用WITH…AS改寫標量子查詢

hooca發表於2016-04-07
示例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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章