ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【八】查询变换

【八】查询变换

原创 Oracle 作者：哎呀我的天呐 时间：2015-03-04 18:02:15 0 删除 编辑

星型转换在数据仓库中用到。
有人说返回值少的时候用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博客 ” ，链接：https://blog.itpub.net/29990276/viewspace-1448367/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Transformation之（Group Pruning ）group的修剪GP【十】
下一篇： 10046【十一】
请登录后发表评论 登录
全部评论
哎呀我的天呐
专家姓名：姚崇
https://cloud.tencent.com/developer/inventory/14362

注册时间：2014-10-30

  • 博文量
    403
  • 访问量
    2528712

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593