Transformation之Non-correlated（無關聯子查詢）=any的transform【五】

哎呀我的天吶發表於2015-02-27
ORM

                   Join
Row source
Inner join
Outer join
Left outer-join:L.c=R.c(+)
Semi-join ：一個邏輯概念，但是寫不出semi-join沒有一個表達方式，通常exist、in這兩種語法會變成semi-join ，所謂半連線就是用一個結果集過濾另一個結果集，但是這種過濾的方式使用join的方式過濾。一般情況如果找到一個合適的結果就不往下找了，這就是semi-join
Anti-join ：not exists、not in

Nested loop NL
Sort merge joiin -SM  笛卡爾屬於這種
Hash join Hj 

子查詢
Sbuquery   在query/DML內部的查詢  in (select.. .. )
Correlated 相關子查詢     列>... ...
Non-correlated 無關子查詢 你完全不能把這兩個表之間做什麼關係
Scalar view 標量 子查詢  select子句裡面帶的子查詢 通常效能不好
In-line view 

無關聯子查詢transformaton
=any 符合這個條件的就可以。有的時候這種無關聯的成本有時候比較高，因為得先吧把any中的全部查出來，再做比較，其實oracle會給你做成這種：

執行計劃是semi-join

點選( 此處 )摺疊或開啟

  1. set line 200

  2. set pages 200

  3. drop table emp1 ;

  4. create table emp1 as select * from emp ;

  5. alter session set events '10053 trace name context forever ,level 1';

  6. select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 ) ;

  7. alter session set events '10053 trace name context off';


  9. / *

  10. SELECT EMP . *

  11. FROM SCOTT . EMP1 EMP1 , SCOTT . EMP EMP

  12. WHERE EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20

  13. * /



點選( 此處 )摺疊或開啟

  1. Registered qb : SEL$1 0x873b6678 ( PARSER )

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

  3. QUERY BLOCK SIGNATURE

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

  5.   signature ( ) : qb_name = SEL$1 nbfros = 1 flg =

  6.     fro ( ) : flg = 4 objn = 75335 hint_alias = "EMP" @ "SEL$1"


  8. Registered qb : SEL$2 0x873b14a0 ( PARSER )

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

  10. QUERY BLOCK SIGNATURE

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

  12.   signature ( ) : qb_name = SEL$2 nbfros = 1 flg =

  13.     fro ( ) : flg = 4 objn = 78917 hint_alias = "EMP1" @ "SEL$2"


  15. SPM : statement not found in SMB


  17. * * * * * * * * * * * * * * * * * * * * * * * * * *

  18. Automatic degree of parallelism ( ADOP )

  19. * * * * * * * * * * * * * * * * * * * * * * * * * *

  20. Automatic degree of parallelism is disabled : Parameter .


  22. PM : Considering predicate move - around in query block SEL$1 ( # )

  23. * * * * * * * * * * * * * * * * * * * * * * * * * *

  24. Predicate Move - Around ( PM )

  25. * * * * * * * * * * * * * * * * * * * * * * * * * *

  26. OPTIMIZER INFORMATION


  28. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  29. - - - - - Current SQL Statement for this session ( sql_id = 0rbab21pk95av ) - - - - -

  30. select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )

  31. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  32. Legend

  33. The following abbreviations are used by optimizer trace .

  34. CBQT - cost - based query transformation

  35. JPPD - join predicate push - down

  36. OJPPD - old - style ( non - cost - based ) JPPD

  37. FPD - filter push - down

  38. PM - predicate move - around

  39. CVM - complex view merging

  40. SPJ - select - project - join

  41. SJC - set join conversion

  42. SU - subquery unnesting

  43. OBYE - order by elimination

  44. OST - old style star transformation

  45. ST - new ( cbqt ) star transformation

  46. CNT - count ( col ) to count ( * ) transformation

  47. JE - Join Elimination

  48. JF - join factorization

  49. SLP - select list pruning

  50. DP - distinct placement

  51. qb - query block

  52. LB - leaf blocks

  53. DK - distinct keys

  54. LB/K - average number of leaf blocks per key

  55. DB/K - average number of data blocks per key

  56. CLUF - clustering factor

  57. NDV - number of distinct values

  58. Resp - response cost

  59. Card - cardinality

  60. Resc - resource cost

  61. NL - nested loops ( join )

  62. SM - sort merge ( join )

  63. HA - hash ( join )

  64. CPUSPEED - CPU Speed

  65. IOTFRSPEED - I/O transfer speed

  66. IOSEEKTIM - I/O seek time

  67. SREADTIM - average single block read time

  68. MREADTIM - average multiblock read time

  69. MBRC - average multiblock read count

  70. MAXTHR - maximum I/O system throughput

  71. SLAVETHR - average slave I/O throughput

  72. dmeth - distribution method

  73.   1 : no partitioning required

  74.   2 : value partitioned

  75.   4 : right is random ( round - robin )

  76.   128 : left is random ( round - robin )

  77.   8 : broadcast right and partition left

  78.   16 : broadcast left and partition right

  79.   32 : partition left using partitioning of right

  80.   64 : partition right using partitioning of left

  81.   256 : run the join in serial

  82.   0 : invalid distribution method

  83. sel - selectivity

  84. ptn - partition

  85. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  86. PARAMETERS USED BY THE OPTIMIZER

  87. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  88.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  89.   PARAMETERS WITH ALTERED VALUES

  90.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *




  94.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  95.   PARAMETERS IN OPT_PARAM HINT

  96.    * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  97. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  98. Column Usage Monitoring is ON : tracking level = 1

  99. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


  101. Considering Query Transformations on query block SEL$1 ( # )

  102. * * * * * * * * * * * * * * * * * * * * * * * * * *

  103. Query transformations ( QT )

  104. * * * * * * * * * * * * * * * * * * * * * * * * * *

  105. JF : Checking validity of join factorization for query block SEL$2 ( # )

  106. JF : Bypassed : not a UNION or UNION - ALL query block .

  107. ST : not valid since star transformation parameter is FALSE

  108. TE : Checking validity of table expansion for query block SEL$2 ( # )

  109. TE : Bypassed : No partitioned table in query block .

  110. CBQT : Validity checks passed for 0rbab21pk95av .

  111. CSE : Considering common sub - expression elimination in query block SEL$1 ( # )

  112. * * * * * * * * * * * * * * * * * * * * * * * * *

  113. Common Subexpression elimination ( CSE )

  114. * * * * * * * * * * * * * * * * * * * * * * * * *

  115. CSE : Considering common sub - expression elimination in query block SEL$2 ( # )

  116. * * * * * * * * * * * * * * * * * * * * * * * * *

  117. Common Subexpression elimination ( CSE )

  118. * * * * * * * * * * * * * * * * * * * * * * * * *

  119. CSE : CSE not performed on query block SEL$2 ( # ) .

  120. CSE : CSE not performed on query block SEL$1 ( # ) .

  121. OBYE : Considering Order - by Elimination from view SEL$1 ( # )

  122. * * * * * * * * * * * * * * * * * * * * * * * * * * *

  123. Order - by elimination ( OBYE )

  124. * * * * * * * * * * * * * * * * * * * * * * * * * * *

  125. OBYE : OBYE bypassed : no order by to eliminate .

  126. OJE : Begin : find best directive for query block SEL$1 ( # )

  127. OJE : End : finding best directive for query block SEL$1 ( # )

  128. OJE : Begin : find best directive for query block SEL$2 ( # )

  129. OJE : End : finding best directive for query block SEL$2 ( # )

  130. query block SEL$1 ( # ) unchanged

  131. Considering Query Transformations on query block SEL$1 ( # )

  132. * * * * * * * * * * * * * * * * * * * * * * * * * *

  133. Query transformations ( QT )

  134. * * * * * * * * * * * * * * * * * * * * * * * * * *

  135. CSE : Considering common sub - expression elimination in query block SEL$1 ( # )

  136. * * * * * * * * * * * * * * * * * * * * * * * * *

  137. Common Subexpression elimination ( CSE )

  138. * * * * * * * * * * * * * * * * * * * * * * * * *

  139. CSE : Considering common sub - expression elimination in query block SEL$2 ( # )

  140. * * * * * * * * * * * * * * * * * * * * * * * * *

  141. Common Subexpression elimination ( CSE )

  142. * * * * * * * * * * * * * * * * * * * * * * * * *

  143. CSE : CSE not performed on query block SEL$2 ( # ) .

  144. CSE : CSE not performed on query block SEL$1 ( # ) .

  145. query block SEL$1 ( # ) unchanged

  146. apadrv - start sqlid = 841411239277270363

  147.    :

  148.      call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 64568 , alloc = 66920 ) , execution ( in - use = 3456 , alloc = 4032 )


  150. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  151. Peeked values of the binds in SQL statement

  152. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


  154. CBQT : Considering cost - based transformation on query block SEL$1 ( # )

  155. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  156. COST - BASED QUERY TRANSFORMATIONS

  157. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  158. FPD : Considering simple filter push ( pre rewrite ) in query block SEL$2 ( # )

  159. FPD : Current where clause predicates "EMP1" . "DEPTNO" = 20


  161. FPD : Considering simple filter push ( pre rewrite ) in query block SEL$1 ( # )

  162. FPD : Current where clause predicates "EMP" . "HIREDATE" = ANY ( SELECT "EMP1" . "HIREDATE" FROM "EMP1" "EMP1" )


  164. OBYE : Considering Order - by Elimination from view SEL$1 ( # )

  165. * * * * * * * * * * * * * * * * * * * * * * * * * * *

  166. Order - by elimination ( OBYE )

  167. * * * * * * * * * * * * * * * * * * * * * * * * * * *

  168. OBYE : OBYE bypassed : no order by to eliminate .

  169. Considering Query Transformations on query block SEL$1 ( # )

  170. * * * * * * * * * * * * * * * * * * * * * * * * * *

  171. Query transformations ( QT )

  172. * * * * * * * * * * * * * * * * * * * * * * * * * *

  173. CSE : Considering common sub - expression elimination in query block SEL$1 ( # )

  174. * * * * * * * * * * * * * * * * * * * * * * * * *

  175. Common Subexpression elimination ( CSE )

  176. * * * * * * * * * * * * * * * * * * * * * * * * *

  177. CSE : Considering common sub - expression elimination in query block SEL$2 ( # )

  178. * * * * * * * * * * * * * * * * * * * * * * * * *

  179. Common Subexpression elimination ( CSE )

  180. * * * * * * * * * * * * * * * * * * * * * * * * *

  181. CSE : CSE not performed on query block SEL$2 ( # ) .

  182. CSE : CSE not performed on query block SEL$1 ( # ) .

  183. kkqctdrvTD - start on query block SEL$1 ( # )

  184. kkqctdrvTD - start : :

  185.      call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 107016 , alloc = 110912 ) , execution ( in - use = 3456 , alloc = 4032 )


  187. Registered qb : SEL$1 0x8741cf18 ( COPY SEL$1 )

  188. - - - - - - - - - - - - - - - - - - - - -

  189. QUERY BLOCK SIGNATURE

  190. - - - - - - - - - - - - - - - - - - - - -

  191.   signature ( ) : NULL

  192. Registered qb : SEL$2 0x8741db40 ( COPY SEL$2 )

  193. - - - - - - - - - - - - - - - - - - - - -

  194. QUERY BLOCK SIGNATURE

  195. - - - - - - - - - - - - - - - - - - - - -

  196.   signature ( ) : NULL

  197. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  198. Cost - Based Subquery Unnesting

  199. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  200. SU : Unnesting query blocks in query block SEL$1 ( # 1 ) that are valid to unnest .

  201. Subquery removal for query block SEL$2 ( # 2 )

  202. RSW : Not valid for subquery removal SEL$2 ( # 2 )

  203. Subquery unchanged .

  204. Subquery Unnesting on query block SEL$1 ( # 1 ) SU : Performing unnesting that does not require costing .

  205. SU : Considering subquery unnest on query block SEL$1 ( # 1 ) .

  206. SU : Checking validity of unnesting subquery SEL$2 ( # 2 )

  207. SU : Passed validity checks .

  208. SU : Transforming ANY subquery to a join .

  209. Registered qb : SEL$5DA710D3 0x8741cf18 ( SUBQUERY UNNEST SEL$1 ; SEL$2 )

  210. - - - - - - - - - - - - - - - - - - - - -

  211. QUERY BLOCK SIGNATURE

  212. - - - - - - - - - - - - - - - - - - - - -

  213.   signature ( ) : qb_name = SEL$5DA710D3 nbfros = 2 flg =

  214.     fro ( ) : flg = 0 objn = 75335 hint_alias = \ "EMP\" @ \ "SEL$1\"

  215.     fro ( 1 ) : flg = 0 objn = 78917 hint_alias = \ "EMP1\" @ \ "SEL$2\"


  217. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  218. Cost - Based Complex View Merging

  219. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  220. CVM : Finding query blocks in query block SEL$5DA710D3 ( # 1 ) that are valid to merge .

  221. OJE : Begin : find best directive for query block SEL$5DA710D3 ( # 1 )

  222. OJE : End : finding best directive for query block SEL$5DA710D3 ( # 1 )

  223. kkqctdrvTD - cleanup : transform ( in - use = 5064 , alloc = 8392 ) :

  224.      call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 125432 , alloc = 135984 ) , execution ( in - use = 3496 , alloc = 4032 )


  226. kkqctdrvTD - end :

  227.      call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 117456 , alloc = 135984 ) , execution ( in - use = 3496 , alloc = 4032 )


  229. Subquery removal for query block SEL$2 ( # 2 )

  230. RSW : Not valid for subquery removal SEL$2 ( # 2 )

  231. Subquery unchanged .

  232. SU : Transforming ANY subquery to a join .

  233. SJC : Considering set - join conversion in query block SEL$5DA710D3 ( # 1 )

  234. * * * * * * * * * * * * * * * * * * * * * * * * *

  235. Set - Join Conversion ( SJC )

  236. * * * * * * * * * * * * * * * * * * * * * * * * *

  237. SJC : not performed

  238. OJE : Begin : find best directive for query block SEL$5DA710D3 ( # 1 )

  239. OJE : End : finding best directive for query block SEL$5DA710D3 ( # 1 )

  240. JE : Considering Join Elimination on query block SEL$5DA710D3 ( # 1 )

  241. * * * * * * * * * * * * * * * * * * * * * * * * *

  242. Join Elimination ( JE )

  243. * * * * * * * * * * * * * * * * * * * * * * * * *

  244. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  245. SELECT EMP . EMPNO   EMPNO , EMP . ENAME   ENAME , EMP . JOB   JOB , EMP . MGR   MGR , EMP . HIREDATE   HIREDATE , EMP . SAL   SAL , EMP . COMM   COMM , EMP . DEPTNO   DEPTNO  FROM SCOTT . EMP1   EMP1 , SCOTT . EMP   EMP  WHERE EMP . HIREDATE = EMP1 . HIREDATE   AND EMP1 . DEPTNO = 20

  246. JE : cfro : EMP1 objn : 75335 col# : 5 dfro : EMP dcol# : 5

  247. JE : cfro : EMP1 objn : 75335 col# : 5 dfro : EMP dcol# : 5

  248. JE : cfro : EMP objn : 78917 col# : 5 dfro : EMP1 dcol# : 5

  249. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *

  250. SELECT EMP . EMPNO   EMPNO , EMP . ENAME   ENAME , EMP . JOB   JOB , EMP . MGR   MGR , EMP . HIREDATE   HIREDATE , EMP . SAL   SAL , EMP . COMM   COMM , EMP . DEPTNO   DEPTNO  FROM SCOTT . EMP1   EMP1 , SCOTT . EMP   EMP  WHERE EMP . HIREDATE = EMP1 . HIREDATE   AND EMP1 . DEPTNO = 20

  251. Query block SEL$5DA710D3 ( # 1 ) unchanged

  252. PM : Considering predicate move - around in query block SEL$5DA710D3 ( # 1 )

  253. * * * * * * * * * * * * * * * * * * * * * * * * * *

  254. Predicate Move - Around ( PM )

  255. * * * * * * * * * * * * * * * * * * * * * * * * * *

  256. PM : PM bypassed : Outer query contains no views .

  257. PM : PM bypassed : Outer query contains no views .

  258. kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )

  259. kkqctdrvTD - start : :

  260.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 119176 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  262. kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :

  263.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 120008 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  265. kkqctdrvTD - end :

  266.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 120528 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  268. kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )

  269. kkqctdrvTD - start : :

  270.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 120528 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  272. kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :

  273.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 121344 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  275. kkqctdrvTD - end :

  276.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 121864 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  278. kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )

  279. kkqctdrvTD - start : :

  280.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 121864 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  282. TE : Checking validity of table expansion for query block SEL$5DA710D3 ( # 1 )

  283. TE : Bypassed : No partitioned table in query block .

  284. kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :

  285.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 122672 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  287. kkqctdrvTD - end :

  288.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 123192 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  290. TE : Checking validity of table expansion for query block SEL$5DA710D3 ( # 1 )

  291. TE : Bypassed : No partitioned table in query block .

  292. ST : Query in kkqstardrv : * * * * * * * UNPARSED QUERY IS * * * * * * *

  293. SELECT EMP . EMPNO   EMPNO , EMP . ENAME   ENAME , EMP . JOB   JOB , EMP . MGR   MGR , EMP . HIREDATE   HIREDATE , EMP . SAL   SAL , EMP . COMM   COMM , EMP . DEPTNO   DEPTNO  FROM SCOTT . EMP1   EMP1 , SCOTT . EMP   EMP  WHERE EMP . HIREDATE = EMP1 . HIREDATE   AND EMP1 . DEPTNO = 20

  294. ST : not valid since star transformation parameter is FALSE

  295. kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )

  296. kkqctdrvTD - start : :

  297.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 123192 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  299. JF : Checking validity of join factorization for query block SEL$5DA710D3 ( # 1 )

  300. JF : Bypassed : not a UNION or UNION - ALL query block .

  301. kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :

  302.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 124000 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  304. kkqctdrvTD - end :

  305.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 124520 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  307. JPPD : Considering Cost - based predicate pushdown from query block SEL$5DA710D3 ( # 1 )

  308. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  309. Cost - based predicate pushdown ( JPPD )

  310. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  311. kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )

  312. kkqctdrvTD - start : :

  313.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 124520 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  315. kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :

  316.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 125328 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  318. kkqctdrvTD - end :

  319.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 125872 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  321. JPPD : Applying transformation directives

  322. query block SEL$1 transformed to SEL$5DA710D3 ( # 1 )

  323. FPD : Considering simple filter push in query block SEL$5DA710D3 ( # 1 )

  324. EMP . HIREDATE = EMP1 . HIREDATE   AND EMP1 . DEPTNO = 20

  325. try to generate transitive predicate from check constraints for query block SEL$5DA710D3 ( # 1 )

  326. finally : EMP . HIREDATE = EMP1 . HIREDATE   AND EMP1 . DEPTNO = 20                 finally關鍵字，複雜的子查詢會有多個finally，因為是針對query block優化的


  328. Final query after transformations : * * * * * * * UNPARSED QUERY IS * * * * * * *

  329. SELECT EMP . EMPNO   EMPNO , EMP . ENAME   ENAME , EMP . JOB   JOB , EMP . MGR   MGR , EMP . HIREDATE   HIREDATE , EMP . SAL   SAL , EMP . COMM   COMM , EMP . DEPTNO   DEPTNO  FROM SCOTT . EMP1   EMP1 , SCOTT . EMP   EMP  WHERE EMP . HIREDATE = EMP1 . HIREDATE   AND EMP1 . DEPTNO = 20

  330. kkoqbc : optimizing query block SEL$5DA710D3 ( # 1 )

  331.         

  332.          :

  333.      call ( in - use = 3952 , alloc = 16344 ) , compile ( in - use = 127176 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )


  335. kkoqbc - subheap ( create addr = 0x2b02873bfaa0 )

  336. * * * * * * * * * * * * * * * *

  337. QUERY BLOCK TEXT

  338. * * * * * * * * * * * * * * * *

  339. select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )

  340. - - - - - - - - - - - - - - - - - - - - -

  341. QUERY BLOCK SIGNATURE

  342. - - - - - - - - - - - - - - - - - - - - -

  343. signature ( optimizer ) : qb_name = SEL$5DA710D3 nbfros = 2 flg =

  344.   fro ( ) : flg = 0 objn = 75335 hint_alias = "EMP" @ "SEL$1"

  345.   fro ( 1 ) : flg = 0 objn = 78917 hint_alias = "EMP1" @ "SEL$2"


  347. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  348. SYSTEM STATISTICS INFORMATION

  349. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

  350.   Using NOWORKLOAD Stats

  351.   CPUSPEEDNW : 1752 millions instructions/sec ( default is 100 )

  352.   IOTFRSPEED : 4096 bytes per millisecond ( default is 4096 )

  353.   IOSEEKTIM : 10 milliseconds ( default is 10 )

  354.   MBRC : NO VALUE blocks ( default is 8 )


  356. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  357. BASE STATISTICAL INFORMATION

  358. * * * * * * * * * * * * * * * * * * * * * * *

  359. Table Stats : :

  360.   Table : EMP  Alias : EMP

  361.     #Rows : 14  #Blks : 5  AvgRowLen : 38 . 00  ChainCnt : 0 . 00

  362.   Column ( # 5 ) : HIREDATE (

  363.     AvgLen : 8 NDV : 13 Nulls : 0 Density : 0 . 076923 Min : 2444591 Max : 2446939

  364. Index Stats : :

  365.   Index : PK_EMP  Col# : 1

  366.     LVLS : 0  #LB : 1  #DK : 14  LB/K : 1 . 00  DB/K : 1 . 00  CLUF : 1 . 00

  367. * * * * * * * * * * * * * * * * * * * * * * *

  368. Table Stats : :

  369.   Table : EMP1  Alias : EMP1 ( NOT ANALYZED )

  370.     #Rows : 327  #Blks : 4  AvgRowLen : 100 . 00  ChainCnt : 0 . 00

  371.   Column ( # 5 ) : HIREDATE ( NO STATISTICS ( using defaults )

  372.     AvgLen : 9 NDV : 10 Nulls : 0 Density : 0 . 097859

  373. Access path analysis for EMP1

  374. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  375. SINGLE TABLE ACCESS PATH

  376.   Single Table Cardinality Estimation for EMP1 [ EMP1 ]


  378. * * * 2015 - 02 - 27 01 : 51 : 40 . 125

  379. * * Performing dynamic sampling initial checks . * *

  380.   Column ( # 8 ) : DEPTNO ( NO STATISTICS ( using defaults )

  381.     AvgLen : 13 NDV : 10 Nulls : 0 Density : 0 . 097859

  382. * * Dynamic sampling initial checks returning TRUE ( level = 2 ) .

  383. * * Dynamic sampling updated table stats . : blocks = 4


  385. * * * 2015 - 02 - 27 01 : 51 : 40 . 126

  386. * * Generated dynamic sampling query :

  387.     query text :

  388. SELECT / * OPT_DYN_SAMP * / / * + ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL ( SAMPLESUB ) opt_param ( 'parallel_execution_enabled' , 'false' ) NO_PARALLEL_INDEX ( SAMPLESUB ) NO_SQL_TUNE * / NVL ( SUM ( C1 ) , ) , NVL ( SUM ( C2 ) , ) , COUNT ( DISTINCT C3 ) , NVL ( SUM ( CASE WHEN C3 IS NULL THEN 1 ELSE 0 END ) , ) FROM ( SELECT / * + IGNORE_WHERE_CLAUSE NO_PARALLEL ( "EMP1" ) FULL ( "EMP1" ) NO_PARALLEL_INDEX ( "EMP1" ) * / 1 AS C1 , CASE WHEN EMP1 . DEPTNO = 20 THEN 1 ELSE 0 END AS C2 , "EMP1" . "HIREDATE" AS C3 FROM "SCOTT" . "EMP1" "EMP1" ) SAMPLESUB


  390. * * * 2015 - 02 - 27 01 : 51 : 40 . 127

  391. * * Executed dynamic sampling query :

  392.     level : 2

  393.     sample pct . : 100 . 000000

  394.     actual sample size : 14

  395.     filtered sample card . : 5

  396.     orig . card . : 327

  397.     block cnt . table stat . : 4

  398.     block cnt . for sampling : 4

  399.     max . sample block cnt . : 64

  400.     sample block cnt . : 4

  401.     ndv C3 : 13

  402.         scaled : 13 . 00

  403.     nulls C4 : 0

  404.         scaled : 0 . 00

  405.     min . sel . est . : 0 . 01000000

  406. * * Dynamic sampling col . stats . :

  407.   Column ( # 5 ) : HIREDATE ( Part# : 0

  408.     AvgLen : 7 NDV : 13 Nulls : 0 Density : 0 . 076923

  409. * * Using dynamic sampling NULLs estimates .

  410. * * Using dynamic sampling NDV estimates .

  411.    Scaled NDVs using cardinality = 14 .

  412. * * Using dynamic sampling card . : 14

  413. * * Dynamic sampling updated table card .

  414. * * Using single table dynamic sel . est . : 0 . 35714286

  415.   Table : EMP1  Alias : EMP1

  416.     Card : Original : 14 . 000000  Rounded : 5  Computed : 5 . 00  Non Adjusted : 5 . 00

  417.   Access Path : TableScan

  418.     Cost : 3 . 00  Resp : 3 . 00  Degree : 0

  419.       Cost_io : 3 . 00  Cost_cpu : 33246

  420.       Resp_io : 3 . 00  Resp_cpu : 33246

  421.   Best : : AccessPath : TableScan

  422.          Cost : 3 . 00  Degree : 1  Resp : 3 . 00  Card : 5 . 00  Bytes : 0


  424. Access path analysis for EMP

  425. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  426. SINGLE TABLE ACCESS PATH

  427.   Single Table Cardinality Estimation for EMP [ EMP ]

  428.   Table : EMP  Alias : EMP

  429.     Card : Original : 14 . 000000  Rounded : 14  Computed : 14 . 00  Non Adjusted : 14 . 00

  430.   Access Path : TableScan

  431.     Cost : 3 . 00  Resp : 3 . 00  Degree : 0

  432.       Cost_io : 3 . 00  Cost_cpu : 39667

  433.       Resp_io : 3 . 00  Resp_cpu : 39667

  434.   Best : : AccessPath : TableScan

  435.          Cost : 3 . 00  Degree : 1  Resp : 3 . 00  Card : 14 . 00  Bytes : 0


  437. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



  440. OPTIMIZER STATISTICS AND COMPUTATIONS

  441. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  442. GENERAL PLANS

  443. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  444. Considering cardinality - based initial join order .

  445. Permutations for Starting Table :

  446. Join order [ 1 ] : EMP [ EMP ] # 0  EMP1 [ EMP1 ] # 1


  448. * * * * * * * * * * * * * * *

  449. Now joining : EMP1 [ EMP1 ] # 1

  450. * * * * * * * * * * * * * * *

  451. NL Join

  452.   Outer table : Card : 14 . 00  Cost : 3 . 00  Resp : 3 . 00  Degree : 1  Bytes : 38

  453. Access path analysis for EMP1

  454.    Inner table : EMP1  Alias : EMP1

  455.   Access Path : TableScan

  456.     NL Join : Cost : 20 . 02  Resp : 20 . 02  Degree : 1

  457.       Cost_io : 20 . 00  Cost_cpu : 505108

  458.       Resp_io : 20 . 00  Resp_cpu : 505108


  460.   Best NL cost : 20 . 02

  461.           resc : 20 . 02  resc_io : 20 . 00 resc_cpu : 505108       resc_cpu指的是cpu cost  resc_io是IOcost，cost很好關心

  462.           resp : 20 . 02  resp_io : 20 . 00  resc_cpu : 505108

  463. Semi Join Card : 5 . 384615 = outer ( 14 . 000000 ) * sel ( . 384615 )

  464. Join Card - Rounded : 5 Computed : 5 . 38

  465.   Outer table : EMP  Alias : EMP

  466.     resc : 3 . 00  card 14 . 00  bytes : 38  deg : 1  resp : 3 . 00

  467.    Inner table : EMP1  Alias : EMP1

  468.     resc : 3 . 00  card : 5 . 00  bytes : 22  deg : 1  resp : 3 . 00

  469.     using dmeth : 2  #groups : 1

  470.     SORT ressource         Sort statistics

  471.       Sort width : 334 Area size : 292864 Max Area size : 58720256

  472.       Degree : 1

  473.       Blocks to Sort : 1 Row size : 52 Total Rows : 14

  474.       Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  475.       Total IO sort cost : 0      Total CPU sort cost : 21023507

  476.       Total Temp space used : 0

  477.     SORT ressource         Sort statistics

  478.       Sort width : 334 Area size : 292864 Max Area size : 58720256

  479.       Degree : 1

  480.       Blocks to Sort : 1 Row size : 35 Total Rows : 5

  481.       Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  482.       Total IO sort cost : 0      Total CPU sort cost : 21021629

  483.       Total Temp space used : 0

  484.   SM join : Resc : 8 . 00  Resp : 8 . 00 [ multiMatchCost = . 00 ]

  485. SM Join

  486.   SM cost : 8 . 00

  487.      resc : 8 . 00 resc_io : 6 . 00 resc_cpu : 42118049

  488.      resp : 8 . 00 resp_io : 6 . 00 resp_cpu : 42118049

  489.   Outer table : EMP  Alias : EMP

  490.     resc : 3 . 00  card 14 . 00  bytes : 38  deg : 1  resp : 3 . 00

  491.    Inner table : EMP1  Alias : EMP1

  492.     resc : 3 . 00  card : 5 . 00  bytes : 22  deg : 1  resp : 3 . 00

  493.     using dmeth : 2  #groups : 1

  494.     Cost per ptn : 0 . 50  #ptns : 1

  495.     hash_area : 124 ( max = 14336 ) buildfrag : 1  probefrag : 1  ppasses : 1

  496.   Hash join : Resc : 6 . 50  Resp : 6 . 50 [ multiMatchCost = . 00 ]

  497. HA Join

  498.   HA cost : 6 . 50

  499.      resc : 6 . 50 resc_io : 6 . 00 resc_cpu : 10586066

  500.      resp : 6 . 50 resp_io : 6 . 00 resp_cpu : 10586066

  501. Best : : JoinMethod : HashSemi                                                                       best

  502.         Cost : 6 . 50  Degree : 1  Resp : 6 . 50  Card : 5 . 38 Bytes : 60

  503. * * * * * * * * * * * * * * * * * * * * * * *

  504. Best so far : Table# : 0  cost : 3 . 0019  card : 14 . 0000  bytes : 532

  505.               Table# : 1  cost : 6 . 5036  card : 5 . 3846  bytes : 300

  506. * * * * * * * * * * * * * * * * * * * * * * *

  507. Join order [ 2 ] : EMP1 [ EMP1 ] # 1  EMP [ EMP ] #

  508.     SORT ressource         Sort statistics

  509.       Sort width : 334 Area size : 292864 Max Area size : 58720256

  510.       Degree : 1

  511.       Blocks to Sort : 1 Row size : 35 Total Rows : 5

  512.       Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  513.       Total IO sort cost : 0      Total CPU sort cost : 21021629

  514.       Total Temp space used : 0


  516. * * * * * * * * * * * * * * *

  517. Now joining : EMP [ EMP ] #

  518. * * * * * * * * * * * * * * *

  519. NL Join

  520.   Outer table : Card : 5 . 00  Cost : 4 . 00  Resp : 4 . 00  Degree : 1  Bytes : 22

  521. Access path analysis for EMP

  522.   Inner table : EMP  Alias : EMP

  523.   Access Path : TableScan

  524.     NL Join : Cost : 10 . 01  Resp : 10 . 01  Degree : 1

  525.       Cost_io : 9 . 00  Cost_cpu : 21173876

  526.       Resp_io : 9 . 00  Resp_cpu : 21173876


  528.   Best NL cost : 10 . 01

  529.           resc : 10 . 01  resc_io : 9 . 00  resc_cpu : 21173876

  530.           resp : 10 . 01  resp_io : 9 . 00  resc_cpu : 21173876

  531. Join Card : 5 . 384615 = outer ( 5 . 000000 ) * inner ( 14 . 000000 ) * sel ( . 076923 )

  532. Join Card - Rounded : 5 Computed : 5 . 38

  533.   Outer table : EMP1  Alias : EMP1

  534.     resc : 4 . 00  card 5 . 00  bytes : 22  deg : 1  resp : 4 . 00

  535.   Inner table : EMP  Alias : EMP

  536.     resc : 3 . 00  card : 14 . 00  bytes : 38  deg : 1  resp : 3 . 00

  537.     using dmeth : 2  #groups : 1

  538.     SORT ressource         Sort statistics

  539.       Sort width : 334 Area size : 292864 Max Area size : 58720256

  540.       Degree : 1

  541.       Blocks to Sort : 1 Row size : 35 Total Rows : 3

  542.       Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  543.       Total IO sort cost : 0      Total CPU sort cost : 21021320

  544.       Total Temp space used : 0

  545.     SORT ressource         Sort statistics

  546.       Sort width : 334 Area size : 292864 Max Area size : 58720256

  547.       Degree : 1

  548.       Blocks to Sort : 1 Row size : 52 Total Rows : 14

  549.       Initial runs : 1 Merge passes : 0 IO Cost / pass : 0

  550.       Total IO sort cost : 0      Total CPU sort cost : 21023507

  551.       Total Temp space used : 0

  552.   SM join : Resc : 9 . 00  Resp : 9 . 00 [ multiMatchCost = . 00 ]

  553. SM Join

  554.   SM cost : 9 . 00

  555.      resc : 9 . 00 resc_io : 6 . 00 resc_cpu : 63139368

  556.      resp : 9 . 00 resp_io : 6 . 00 resp_cpu : 63139368

  557.   Outer table : EMP1  Alias : EMP1

  558.     resc : 4 . 00  card 5 . 00  bytes : 22  deg : 1  resp : 4 . 00

  559.   Inner table : EMP  Alias : EMP

  560.     resc : 3 . 00  card : 14 . 00  bytes : 38  deg : 1  resp : 3 . 00

  561.     using dmeth : 2  #groups : 1

  562.     Cost per ptn : 0 . 50  #ptns : 1

  563.     hash_area : 124 ( max = 14336 ) buildfrag : 1  probefrag : 1  ppasses : 1

  564.   Hash join : Resc : 7 . 50  Resp : 7 . 50 [ multiMatchCost = . 00 ]

  565. HA Join

  566.   HA cost : 7 . 50

  567.      resc : 7 . 50 resc_io : 6 . 00 resc_cpu : 31606944

  568.      resp : 7 . 50 resp_io : 6 . 00 resp_cpu : 31606944

  569. Join order aborted : cost > best plan cost

  570. * * * * * * * * * * * * * * * * * * * * * * *

  571. ( newjo - stop - 1 ) k : , spcnt : , perm : 2 , maxperm : 2000


  573. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  574. Number of join permutations tried : 2

  575. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

  576. Consider using bloom filter between EMP [ EMP ] and EMP1 [ EMP1 ] with ? ?

  577. kkoBloomFilter : join ( lcdn : 14 rcdn : 5 jcdn : 5 limit : 35 )

  578. Computing bloom ndv for creator : EMP [ EMP ] ccdn : 14 . 0 and user : EMP1 [ EMP1 ] ucdn : 5 .

  579. kkopqComputeBloomNdv : predicate ( bndv : 13 ndv : 13 ) and ( bndv : 13 ndv : 5 )

  580. kkopqComputeBloomNdv : pred cnt : 2 ndv : 14 reduction : 3

  581. kkoBloomFilter : join ndv : 0 reduction : 2 . 800000 ( limit : . 500000 ) rejected because distinct value ratio

  582. Enumerating distribution method ( advanced )

  583. - - - Distribution method for

  584. join between EMP [ EMP ] ( serial ) and EMP1 [ EMP1 ] ( serial ) ; jm = 2 ; right side access path = TableScan

  585. - - - - serial Hash - Join - > NONE


  587. ( newjo - save ) [ 0 1 ]

  588. Trying or - Expansion on query block SEL$5DA710D3 ( # 1 )

  589. Transfer Optimizer annotations for query block SEL$5DA710D3 ( # 1 )

  590. id = 0 frofkksm [ i ] ( sort - merge/hash ) predicate = EMP . HIREDATE = EMP1 . HIREDATE

  591. id = 0 frosand ( sort - merge/hash ) predicate = EMP . HIREDATE = EMP1 . HIREDATE

  592. id = 0 frofand predicate = EMP1 . DEPTNO = 20

  593. Final cost for query block SEL$5DA710D3 ( # 1 ) - All Rows Plan :

  594.   Best join order : 1

  595.   Cost : 6 . 5036  Degree : 1  Card : 5 . 0000  Bytes : 300

  596.   Resc : 6 . 5036  Resc_io : 6 . 0000  Resc_cpu : 10586066

  597.   Resp : 6 . 5036  Resp_io : 6 . 0000  Resc_cpu : 10586066

  598. kkoqbc - subheap ( delete addr = 0x2b02873bfaa0 , in - use = 26480 , alloc = 32840 )

  599. kkoqbc - end :

  600.          :

  601.      call ( in - use = 27832 , alloc = 82024 ) , compile ( in - use = 130344 , alloc = 135984 ) , execution ( in - use = 4384 , alloc = 8088 )


  603. kkoqbc : finish optimizing query block SEL$5DA710D3 ( # 1 )

  604. apadrv - end

  605.            :

  606.      call ( in - use = 27832 , alloc = 82024 ) , compile ( in - use = 131424 , alloc = 135984 ) , execution ( in - use = 4384 , alloc = 8088 )



  609. Starting SQL statement dump


  611. user_id = 83 user_name = SCOTT module = SQL * Plus action =

  612. sql_id = 0rbab21pk95av plan_hash_value = 1992738195 problem_type = 3

  613. - - - - - Current SQL Statement for this session ( sql_id = 0rbab21pk95av ) - - - - -

  614. select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )

  615. sql_text_length = 94

  616. sql = select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )

  617. - - - - - Explain Plan Dump - - - - -

  618. - - - - - Plan Table - - - - -

  619.  

  620. = = = = = = = = = = = =

  621. Plan Table

  622. = = = = = = = = = = = =

  623. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +

  624. | Id | Operation            |   Name | Rows | Bytes | Cost |      Time |

  625. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +

  626. |   0 | SELECT STATEMENT     |          |        |         |     7 |            |

  627. |   1 |    HASH JOIN SEMI      |          |     5 |    300 |     7 | 00 : 00 : 01 |

  628. |   2 |    TABLE ACCESS FULL |     EMP |    14 |    532 |     3 | 00 : 00 : 01 |

  629. |   3 |    TABLE ACCESS FULL |    EMP1 |     5 |    110 |     3 | 00 : 00 : 01 |

  630. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +

  631. Predicate Information :

  632. - - - - - - - - - - - - - - - - - - - - - -

  633. 1 - access ( "HIREDATE" = "HIREDATE" )

  634. 3 - filter ( "DEPTNO\" = 20 )

  635.  

  636. Content of other_xml column

  637. = = = = = = = = = = = = = = = = = = = = = = = = = = =

  638.   db_version : 11 . 2 . . 3

  639.   parse_schema : SCOTT

  640.   dynamic_sampling : 2

  641.   plan_hash : 1992738195

  642.   plan_hash_2 : 2144276133

  643.   Outline Data :

  644.   / * +

  645.     BEGIN_OUTLINE_DATA

  646.       IGNORE_OPTIM_EMBEDDED_HINTS

  647.       OPTIMIZER_FEATURES_ENABLE ( '11.2.0.3' )

  648.       DB_VERSION ( '11.2.0.3' )

  649.       ALL_ROWS

  650.       OUTLINE_LEAF ( @ "SEL$5DA710D3" )

  651.       UNNEST ( @ "SEL$2" )

  652.       OUTLINE ( @ "SEL$1" )

  653.       OUTLINE ( @ "SEL$2" )

  654.       FULL ( @ "SEL$5DA710D3" \ "EMP\" @ \ "SEL$1" )

  655.       FULL ( @ "SEL$5DA710D3" "EMP1" @ "SEL$2" )

  656.       LEADING ( @ "SEL$5DA710D3" "EMP" @ "SEL$1" "EMP1" @ "SEL$2" )

  657.       USE_HASH ( @ "SEL$5DA710D3" "EMP1" @ "SEL$2" )

  658.     END_OUTLINE_DATA

  659.    * /



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

相關文章