帶彙總的標量子查詢改寫

hooca發表於2016-04-06
示例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

  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

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章