用LEFT JOIN優化標量子查詢

hooca發表於2016-04-06
優化
《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

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

相關文章