【八】查詢變換

哎呀我的天吶發表於2015-03-04

星型轉換在資料倉儲中用到。
有人說返回值少的時候用in，返回值多的時候用exists這種說法是不對的。 前面提到過半連線/反連線，當時我說，大家不要去記什麼時候用in,exists,not in ,not exists。

子查詢非巢狀(subquery unnesting)
在學習subquery unnesting之前，先深入理解 執行計劃中的 FILTER ，可以這樣說， 絕大部分複雜的子查詢，效能問題基本上都是出現在 FILTER上面 。現在舉個例子解釋一下什麼是FILTER。(你們要模擬就自己建立一個test表，資料從dba_objects複製)

點選( 此處 )摺疊或開啟

  1. SQL > select * from test1 where owner = 'SCOTT' or object_id in ( select object_id from test2 where owner = 'SCOTT' ) ;


  3. 已選擇7行。


  5. 執行計劃

  6. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  7. Plan hash value : 989345917


  9. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  10. | Id | Operation                      | Name    |   Rows | Bytes | Cost ( % CPU ) |     Time |

  11. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  12. |  0 | SELECT STATEMENT               |       |   2529 |   229K  |      163 ( 4 ) | 00 : 00 : 02 |

  13. | * 1 |   FILTER                        |         |         |         |              |            |

  14. |  2 |    TABLE ACCESS FULL            | TEST1 | 50443 | 4581K  |      163 ( 4 ) | 00 : 00 : 02 |

  15. | * 3 |    TABLE ACCESS BY INDEX ROWID  | TEST2 |      1 |     11 |        2 ( ) | 00 : 00 : 01 |

  16. | * 4 |     INDEX RANGE SCAN            | IDX3   |      1 |         |        1 ( ) | 00 : 00 : 01 |

  17. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  19. Predicate Information ( identified by operation id ) :

  20. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  22.    1 - filter ( "OWNER" = 'SCOTT' OR EXISTS ( SELECT / * + * / 0 FROM "TEST2"

  23.                "TEST2" WHERE "OBJECT_ID" = : B1 AND "OWNER" = 'SCOTT' ) )

  24.    3 - filter ( "OWNER" = 'SCOTT' )

  25.    4 - access ( "OBJECT_ID" = : B1 )



  28. 統計資訊

  29. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  30.           1 recursive calls

  31.           0 db block gets

  32.      152116 consistent gets

  33.         111 physical reads

  34.           0 redo size

  35.        1430 bytes sent via SQL * Net to client

  36.         400 bytes received via SQL * Net from client

  37.           2 SQL * Net roundtrips to/from client

  38.           0 sorts ( memory )

  39.           0 sorts ( disk )

  40.           7 rows processed

點選( 此處 )摺疊或開啟

  1. 這個SQL語句的特殊執行計劃如下：


  3. SQL > select * from table ( dbms_xplan . display_cursor ( null , null , 'ALLSTATS LAST' ) ) ;


  5. PLAN_TABLE_OUTPUT

  6. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  7. SQL_ID a23r1uchdaafg , child number 0

  8. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  9. select * from test1 where owner = 'SCOTT' or object_id in ( select object_id from test2

  10. where owner = 'SCOTT' )


  12. Plan hash value : 989345917


  14. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  15. | Id   | Operation                      |    Name | Starts | E - Rows | A - Rows |      A - Time | Buffers |

  16. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  17. | *   1 |  FILTER                        |         |       1 |          |       7 | 00 : 00 : 00 . 25 |     152K  |

  18. |    2 |    TABLE ACCESS FULL            | TEST1 |       1 |   50443 |   50443 | 00 : 00 : 00 . 01 |     699   |

  19. | *   3 |    TABLE ACCESS BY INDEX ROWID  | TEST2 |   50436 |       1 |       0 | 00 : 00 : 00 . 19 |     151K  |

  20. | *   4 |    INDEX RANGE SCAN            | IDX3   |   50436 |       1 |   50436 | 00 : 00 : 00 . 12 |     100K  |

  21. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  23. Predicate Information ( identified by operation id ) :

  24. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  26.    1 - filter ( ( "OWNER" = 'SCOTT' OR IS NOT NULL ) )

  27.    3 - filter ( "OWNER" = 'SCOTT' )

  28.    4 - access ( "OBJECT_ID" = : B1 )

--starts 執行的次數
--E-Rows 最佳化器估算的行數
--A-Rows 實際返回的行數
--A-Time 某個操作執行的時間
--Buffers標示邏輯讀
filter其實就相當於 NESTED LOOPS ，但是又不完全是 NESTED LOOPS，nested loop是驅動表返回一行，被驅動表執行一次，但是filter不是這樣，你也不必要懂filter的底層原理，你就把filter當成nested loop就行了，

點選( 此處 )摺疊或開啟

  1. scott@TESTDB12 > select  *  from test1 where owner = 'SCOTT'   or  object_id  in ( select object_id from test2 where owner = 'SCOTT' ) ;


  3. 8 rows selected .



  6. Execution Plan

  7. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  8. Plan hash value :  1896454807


  10. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  11. |  Id  |  Operation           |   Name     |  Rows   |  Bytes  |  Cost  ( % CPU ) |  Time      |

  12. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  13. |   0  |  SELECT STATEMENT    |          |  5078   |   1026K |      301  ( 1 ) |  00 : 00 : 04  |

  14. | *  1  |   FILTER             |          |         |         |              |            |

  15. |   2  |    TABLE ACCESS FULL |  TEST1   |  85348  |     16M |      301  ( 1 ) |  00 : 00 : 04  |

  16. | *  3  |    TABLE ACCESS FULL |  TEST2   |      1  |     30  |      300  ( 1 ) |  00 : 00 : 04  |

  17. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  19. Predicate Information  ( identified by operation id ) :

  20. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  22.    1  -  filter ( "OWNER" = 'SCOTT'   OR  EXISTS  ( SELECT 0 FROM  "TEST2"   "TEST2"

  23.      WHERE  "OBJECT_ID" = : B1  AND   "OWNER" = 'SCOTT' ) )

  24.    3  -  filter ( "OBJECT_ID" = : B1  AND   "OWNER" = 'SCOTT' )


  26. Note

  27. - - - - -

  28.     -  dynamic sampling used  for   this  statement  ( level = 2 )



  31. Statistics

  32. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  33.      0 recursive calls

  34.      0 db block gets

  35.    80944276 consistent gets

  36.    80718384 physical reads

  37.      0 redo  size

  38.   1975 bytes sent via SQL * Net to client

  39.    523 bytes received via SQL * Net from client

  40.      2 SQL * Net roundtrips to/from client

  41.      0 sorts  ( memory )

  42.      0 sorts  ( disk )

  43.      8 rows processed


我們拿上面這 個執行計劃 為例：id=2這有8萬行資料，id=3要被全表掃面接近8萬次， 假設id=3這個表有10M資料，那麼8w*10MB=800G的資料量需要進行io。nested loop 驅動表返回一行，被驅動表執行一次，但是filter不確定，比如有肯能驅動表返回10w行，被驅動表執行4w次。 主要是看返回表 的NDV，這是小細節了，不用關心。

點選( 此處 )摺疊或開啟

  1. SELECT B . AREA_ID ,

  2.        A . PARTY_ID ,

  3.        B . AREA_NAME ,

  4.        C . NAME CHANNEL_NAME ,

  5.        B . NAME PARTY_NAME ,

  6.        B . ACCESS_NUMBER ,

  7.        B . PROD_SPEC ,

  8.        B . START_DT ,

  9.        A . BO_ACTION_NAME ,

  10.        A . SO_STAFF_ID ,

  11.        A . ATOM_ACTION_ID ,

  12.        A . PROD_ID

  13.   FROM DW_CHANNEL C , DW_CRM_DAY_USER B , DW_BO_ORDER A

  14.  WHERE A . PROD_ID = B . PROD_ID

  15.     AND A . CHANNEL_ID = C . CHANNEL_ID

  16.     AND A . SO_STAFF_ID LIKE '36%'

  17.     AND A . BO_ACTION_NAME IN ( '新裝' , '移機' , '資費變更' )

  18.     AND B . PROD_SPEC IN ( '普通電話' ,

  19.                         'ADSL' ,

  20.                         'LAN' ,

  21.                         '手機' ,

  22.                         'E8 - 2S' ,

  23.                         'E6移動版' ,

  24.                         'E9版1M(老版)' ,

  25.                         '普通E9' ,

  26.                         '普通新版E8' ,

  27.                         '全省_緊密融合型E9套餐產品規格' ,

  28.                         '(新) 全省_緊密融合型E9套餐產品規格' ,

  29.                         '新春歡樂送之E8套餐' ,

  30.                         '新春歡樂送之E6套餐' )

  31.     AND NOT EXISTS

  32.   ( SELECT *

  33.           FROM DW_BO_ORDER D

  34.          WHERE D . STAFF_ID LIKE '36%'

  35.             AND A . PARTY_ID = D . PARTY_ID

  36.             AND A . BO_ID ! = D . BO_ID

  37.             AND A . PROD_ID ! = D . PROD_ID

  38.             AND A . BO_ACTION_NAME IN ( '新裝' , \ '移機' , '資費變更' )

  39.             AND A . COMPLETE_DT - INTERVAL '7' DAY < D . COMPLETE_DT );

下面是表的統計：

點選( 此處 )摺疊或開啟

  1. SQL > select count ( * ) from dw_bo_order ;

  2.    

  3.   COUNT ( * )

  4. - - - - - - - - - -

  5.    2282548

  6.    

  7. SQL > select count ( * ) from dw_crm_day_user ;

  8.    

  9.   COUNT ( * )

  10. - - - - - - - - - -

  11.     420918

  12.    

  13. SQL > select count ( * ) from dw_channel ;

  14.    

  15.   COUNT ( * )

  16. - - - - - - - - - -

  17.      48031

下面是執行計劃

點選( 此處 )摺疊或開啟

  1. Plan hash value : 2142862569

  2.    

  3. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  4. |   Id | Operation               | Name              |   Rows | Bytes | Cost ( % CPU ) |      Time | Pstart | Pstop |

  5. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  6. |    0 | SELECT STATEMENT         |                   |    905 |   121K  |   4152K ( 2 )  | 13 : 50 : 32 |         |       |

  7. | *  1 |  FILTER                  |                   |         |         |              |          |         |       |

  8. | *   2 |   HASH JOIN              |                   |    905 |   121K  |   12616 ( 2 )  | 00 : 02 : 32 |         |         |

  9. | *   3 |     HASH JOIN             |                   |    905 | 99550 |  12448 ( 2 )  | 00 : 02 : 30 |         |         |

  10. |    4 |      PARTITION RANGE ALL  |                   |   1979  |   108K  |    9168 ( 2 )  | 00 : 01 : 51 |      1 |      5 |

  11. | *  5 |       TABLE ACCESS FULL   | DW_BO_ORDER      |   1979  |   108K  |    9168 ( 2 )  | 00 : 01 : 51 |     1 |      5 |

  12. | *   6 |      TABLE ACCESS FULL    | DW_CRM_DAY_USER |   309K  |    15M  |    3277 ( 2 )  | 00 : 00 : 40 |         |         |

  13. |   7 |     TABLE ACCESS FULL     | DW_CHANNEL       | 48425 | 1276K  |     168 ( 1 )  | 00 : 00 : 03 |       |         |

  14. | *   8 |    FILTER                 |                   |         |         |              |            |         |         |

  15. |    9 |     PARTITION RANGE ALL   |                   |      1 |     29 |    9147 ( 2 )  | 00 : 01 : 50 |      1 |      5 |

  16. | * 10 |      TABLE ACCESS FULL    | DW_BO_ORDER      |     1 |     29 |    9147 ( 2 )  | 00 : 01 : 50 |      1 |      5 |

  17. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  18.    

  19. Predicate Information ( identified by operation id ) :

  20. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  21.    

  22.    1 - filter ( NOT EXISTS ( SELECT / * + * / 0 FROM "DW_BO_ORDER" "D" WHERE ( : B1 = '新裝' OR : B2 = '移機' OR

  23.                : B3 = '資費變更' ) AND "D" . "PARTY_ID" = : B4 AND TO_CHAR ( "D" . "STAFF_ID" ) LIKE '36%' AND

  24.                "D" . "COMPLETE_DT" > : B5 - INTERVAL '+07 00:00:00' DAY ( 2 ) TO SECOND ( ) AND "D" . "PROD_ID" < > : B6 AND

  25.                "D" . "BO_ID" < > : B7 ) )

  26.    2 - access ( "A" . "CHANNEL_ID" = "C" . "CHANNEL_ID" )

  27.    3 - access ( "A" . "PROD_ID" = "B" . "PROD_ID" )

  28.    5 - filter ( "A" . "PROD_ID" IS NOT NULL AND ( "A" . "BO_ACTION_NAME" = '新裝' OR

  29.                "A" . "BO_ACTION_NAME" = '移機' OR "A" . "BO_ACTION_NAME" = '資費變更' ) AND TO_CHAR ( "A" . "SO_STAFF_ID" ) LIKE

  30.                '36%' )

  31.    6 - filter ( "B" . "PROD_SPEC" = '(新) 全省_緊密融合型E9套餐產品規格' OR "B" . "PROD_SPEC" = 'ADSL' OR

  32.                "B" . "PROD_SPEC\" = 'E6移動版' OR "B" . "PROD_SPEC" = 'E8 - 2S' OR "B" . "PROD_SPEC" = 'E9版1M(老版)' OR

  33.                "B" . "PROD_SPEC" = 'LAN' OR "B" . "PROD_SPEC" = '普通E9' OR "B" . "PROD_SPEC" = '普通電話' OR

  34.                "B" . "PROD_SPEC" = '普通新版E8' OR "B" . "PROD_SPEC" = '全省_緊密融合型E9套餐產品規格' OR "B" . "PROD_SPEC" = '手機' OR

  35.                "B" . "PROD_SPEC" = '新春歡樂送之E6套餐' OR "B" . "PROD_SPEC" = '新春歡樂送之E8套餐' )

  36.    8 - filter ( : B1 = '新裝' OR : B2 = '移機' OR : B3 = '資費變更' )

  37.   10 - filter ( "D" . "PARTY_ID" = : B1 AND TO_CHAR ( "D" . "STAFF_ID" ) LIKE '36%' AND

  38.                "D\" . "COMPLETE_DT" > : B2 - INTERVAL '+07 00:00:00' DAY ( 2 ) TO SECOND ( ) AND "D" . "PROD_ID" < > : B3 AND

  39.                "D" . "BO_ID" < > : B4 )

filter我們看有兒子的filter，這個相當於nested loop ，id=8的地方的filter只起到過濾的作用。
以後看到執行計劃裡有filter菊花一緊。

還有另外一種FILTER，單操作的FILTER，執行計劃如下：

點選( 此處 )摺疊或開啟

  1. PLAN_TABLE_OUTPUT

  2. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  3. |   Id | Operation                         | Name                  | Rows |   Bytes | Cost |

  4. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  5. |    0 | SELECT STATEMENT                  |                       |     1 |      81 | 1618 |

  6. |    1 |   SORT AGGREGATE                   |                       |     1 |      81 |        |

  7. | *   2 |    FILTER                         |                       |        |          |      |

  8. | *   3 |     HASH JOIN OUTER                |                       |        |          |        |

  9. |    4 |      NESTED LOOPS OUTER            |                     |  642 |   38520 |   838 |

  10. | *   5 |       INDEX FAST FULL SCAN         | PK_T_SEND_VEHICLE    |   413 |    8260 |    12 |

  11. |    6 |       TABLE ACCESS BY INDEX ROWID  | T_TASK_HEAD          |     2 |      80 |     2 |

  12. | *   7 |        INDEX RANGE SCAN            | IDX_TASK_VEHICLE_NO |     2 |          |    1 |

  13. |    8 |      TABLE ACCESS FULL             | T_TASK_DETAIL        |   162K |   3337K  |   777 |

  14. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  16. Predicate Information ( identified by operation id ) :

  17. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  19.    2 - filter ( "TTASKDETAI2_" . "IS_REAL" = 'N' OR "TTASKDETAI2_" . "IS_REAL" IS NULL )

  20.    3 - access ( "TRANSTASKH0_" . "TRANS_TASK_NO" = "TTASKDETAI2_" . "TRANS_TASK_NO" ( + ) )

  21.    5 - filter ( TRIM ( "SENDVEHICL1_" . "SEND_VEHICLE_NO" ) = '01037041212280054' )

  22.    7 - access ( "TRANSTASKH0_" . "SEND_VEHICLE_NO" ( + ) = "SENDVEHICL1_" . "SEND_VEHICLE_NO" )


下面我們看：
Subquery Unnesting(子查詢非巢狀)： 如果SQL語句中的where條件後面有子查詢，子查詢前面有in,not in,exists,not exists,,>= 等等，CBO很可能會對該子查詢進行等價改寫，改寫的過程其實就叫做子查詢擴充套件。Oracle始終認為SQL語句進行改寫之後，CBO能更好的最佳化，當然了，並不是所有的子查詢都會被改寫，子查詢中有些限制條件會阻止CBO進行改寫(因為改寫之後不等價)。 如何才能檢視SQL進行了subquery unnesting?
請自己翻閱之前講解執行計劃的章節，這裡再提一下，利用下面SQL可以檢視到

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));
Disable Subquery Unnesting:  alter session set "_unnest_subquery"=false;


點選( 此處 )摺疊或開啟

  1. select ename , deptno from emp where deptno in ( select deptno from dept where dname = 'CHICAGO' ) ;


  3. 執行計劃如下：


  5. SQL > explain plan for select ename , deptno from emp where deptno in ( select deptno from dept where dname = 'CHICAGO' ) ;


  7. Explained .


  9. SQL > select * from table ( dbms_xplan . display ) ;


  11. PLAN_TABLE_OUTPUT

  12. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  13. Plan hash value : 844388907


  15. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  16. | Id | Operation                     |      Name | Rows | Bytes | Cost ( % CPU ) |      Time |

  17. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  18. |  0 | SELECT STATEMENT               |           |     5 |    110 |       6 ( 17 ) | 00 : 00 : 01 |

  19. |   1 |   MERGE JOIN                    |         |     5 |    110 |       6 ( 17 ) | 00 : 00 : 01 |

  20. | * 2 |    TABLE ACCESS BY INDEX ROWID  |     DEPT |     1 |     13 |        2 ( ) | 00 : 00 : 01 |

  21. |   3 |     INDEX FULL SCAN             | PK_DEPT |     4 |         |        1 ( ) | 00 : 00 : 01 |

  22. | * 4 |    SORT JOIN                    |           |    14 |    126 |       4 ( 25 ) | 00 : 00 : 01 |

  23. |   5 |     TABLE ACCESS FULL           |      EMP |    14 |    126 |        3 ( ) | 00 : 00 : 01 |

  24. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  26. Predicate Information ( identified by operation id ) :

  27. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  29.    2 - filter ( "DNAME" = 'CHICAGO' )

  30.    4 - access ( "DEPTNO" = "DEPTNO" )

  31.        filter ( "DEPTNO" = "DEPTNO" )

select e.ename,e.deptno from emp e,dept d where e.deptno=d.deptno and d.dname='CHICAGO';
這個sql語句的執行計劃和上邊的執行計劃是一樣。

用hint NO_UNNEST 可以禁止CBO 進行 Subquery Unnesting  hint UNNEST 可以提示CBO進行Subquery Unnesting

上面HINT只能放在子查詢裡面

點選( 此處 )摺疊或開啟

  1. select ename , deptno from emp where deptno in ( select / * + NO_UNNEST * / deptno from dept where dname = \ 'CHICAGO\' ) ;


  3. SQL > select * from table ( dbms_xplan . display ) ;


  5. PLAN_TABLE_OUTPUT

  6. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  7. Plan hash value : 2809975276


  9. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  10. | Id | Operation                      | Name      | Rows   | Bytes | Cost ( % CPU ) |      Time |

  11. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  12. |  0 | SELECT STATEMENT               |           |      5 |     45 |        6 ( ) | 00 : 00 : 01 |

  13. | * 1 |   FILTER                        |           |         |         |              |           |

  14. |   2 |    TABLE ACCESS FULL            | EMP      |     14 |    126 |        3 ( ) | 00 : 00 : 01 |

  15. | * 3 |    TABLE ACCESS BY INDEX ROWID  |  DEPT     |      1  |     13 |        1 ( ) | 00 : 00 : 01 |

  16. | * 4 |     INDEX UNIQUE SCAN           | PK_DEPT |      1 |         |        0 ( ) | 00 : 00 : 01 |

  17. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  19. Predicate Information ( identified by operation id ) :

  20. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


  22.    1 - filter ( EXISTS ( SELECT / * + NO_UNNEST * / 0 FROM "DEPT" "DEPT" WHERE

  23.                "DEPTNO" = : B1 AND "DNAME" = 'CHICAGO' ) )

  24.    3 - filter ( "DNAME" = 'CHICAGO' )

  25.    4 - access ( "DEPTNO" = : B1 )

子查詢沒有被展開，執行計劃裡會有filter關鍵字。 id=1 ：B1繫結變數，當我傳入一個值的時候，id=4的地方就要被掃面一次。 這就是為什麼前面說的與nested loop不同的原因了 ，如果同樣值就不會多次掃描了。
只有半連結和反連線才會發生filter，所以filter返回的資料是主表的資料。
子查詢沒有展開通常效能比較差，但是也有用filter去最佳化的。
有filter一定有繫結變數。繫結變數成雙成對出現。這個關聯的列基本上是主鍵啦，所以看NDV有沒有必要，NDV當然非常高了。
其次看被驅動表是不是全表掃面，如果是，並且表的segments size很大，那就是操蛋了，讓被驅動表走索引，要不改sql，要不讓他解套。
不是說走filter一定不正常，filter用兒子的情況可能正常，filter沒兒子也可能正常。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1448367/，如需轉載，請註明出處，否則將追究法律責任。

相關文章