Transformation之NUll-aware Anti-join（NAAJ）【九】

哎呀我的天吶發表於2015-02-28
ORMNull

只用在anti join not in not exists 這種 deptno在emp這張表上有空值的情況，或者可以為空的情況



這樣查不出來。因為emp表

ANTI NA這種join會考慮到空值的這種情況。這種transform在資料倉儲的系統中有極其大的作用

點選(此處)摺疊或開啟

  1. *** 2015-02-28 07:39:41.562
  2. *** SESSION ID:(59.3553) 2015-02-28 07:39:41.562
  3. *** CLIENT ID:() 2015-02-28 07:39:41.562
  4. *** SERVICE NAME:(SYS$USERS) 2015-02-28 07:39:41.562
  5. *** MODULE NAME:(SQL*Plus) 2015-02-28 07:39:41.562
  6. *** ACTION NAME:() 2015-02-28 07:39:41.562
  7.  
  8. Registered qb: SEL$1 0x2ed166d0 (PARSER)
  9. ---------------------
  10. QUERY BLOCK SIGNATURE
  11. ---------------------
  12.   signature (): qb_name=SEL$1 nbfros=1 flg=0
  13.     fro(0): flg=4 objn=75333 hint_alias=\"DEPT\"@\"SEL$1\"

  15. Registered qb: SEL$2 0x2ed114a0 (PARSER)
  16. ---------------------
  17. QUERY BLOCK SIGNATURE
  18. ---------------------
  19.   signature (): qb_name=SEL$2 nbfros=1 flg=0
  20.     fro(0): flg=4 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  22. SPM: statement not found in SMB

  24. **************************
  25. Automatic degree of parallelism (ADOP)
  26. **************************
  27. Automatic degree of parallelism is disabled: Parameter.

  29. PM: Considering predicate move-around in query block SEL$1 (#0)
  30. **************************
  31. Predicate Move-Around (PM)
  32. **************************
  33. OPTIMIZER INFORMATION

  35. ******************************************
  36. ----- Current SQL Statement for this session (sql_id=296c8ug3jwdcz) -----
  37. select * from dept where deptno not in (select deptno from emp)
  38. *******************************************
  39. Legend
  40. The following abbreviations are used by optimizer trace.
  41. CBQT - cost-based query transformation
  42. JPPD - join predicate push-down
  43. OJPPD - old-style (non-cost-based) JPPD
  44. FPD - filter push-down
  45. PM - predicate move-around
  46. CVM - complex view merging
  47. SPJ - select-project-join
  48. SJC - set join conversion
  49. SU - subquery unnesting
  50. OBYE - order by elimination
  51. OST - old style star transformation
  52. ST - new (cbqt) star transformation
  53. CNT - count(col) to count(*) transformation
  54. JE - Join Elimination
  55. JF - join factorization
  56. SLP - select list pruning
  57. DP - distinct placement
  58. qb - query block
  59. LB - leaf blocks
  60. DK - distinct keys
  61. LB/K - average number of leaf blocks per key
  62. DB/K - average number of data blocks per key
  63. CLUF - clustering factor
  64. NDV - number of distinct values
  65. Resp - response cost
  66. Card - cardinality
  67. Resc - resource cost
  68. NL - nested loops (join)
  69. SM - sort merge (join)
  70. HA - hash (join)
  71. CPUSPEED - CPU Speed
  72. IOTFRSPEED - I/O transfer speed
  73. IOSEEKTIM - I/O seek time
  74. SREADTIM - average single block read time
  75. MREADTIM - average multiblock read time
  76. MBRC - average multiblock read count
  77. MAXTHR - maximum I/O system throughput
  78. SLAVETHR - average slave I/O throughput
  79. dmeth - distribution method
  80.   1: no partitioning required
  81.   2: value partitioned
  82.   4: right is random (round-robin)
  83.   128: left is random (round-robin)
  84.   8: broadcast right and partition left
  85.   16: broadcast left and partition right
  86.   32: partition left using partitioning of right
  87.   64: partition right using partitioning of left
  88.   256: run the join in serial
  89.   0: invalid distribution method
  90. sel - selectivity
  91. ptn - partition
  92. ***************************************
  93. PARAMETERS USED BY THE OPTIMIZER
  94. ********************************
  95.   *************************************
  96.   PARAMETERS WITH ALTERED VALUES
  97.   ******************************
  98. Compilation Environment Dump
  99. Bug Fix Control Environment


  102.   *************************************
  103.   PARAMETERS WITH DEFAULT VALUES
  104.   ******************************


  107.   ***************************************
  108.   PARAMETERS IN OPT_PARAM HINT
  109.   ****************************
  110. ***************************************
  111. Column Usage Monitoring is ON: tracking level = 1
  112. ***************************************

  114. Considering Query Transformations on query block SEL$1 (#0)
  115. **************************
  116. Query transformations (QT)
  117. **************************
  118. JF: Checking validity of join factorization for query block SEL$2 (#0)
  119. JF: Bypassed: not a UNION or UNION-ALL query block.
  120. ST: not valid since star transformation parameter is FALSE
  121. TE: Checking validity of table expansion for query block SEL$2 (#0)
  122. TE: Bypassed: No partitioned table in query block.
  123. CBQT: Validity checks passed for 296c8ug3jwdcz.
  124. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  125. *************************
  126. Common Subexpression elimination (CSE)
  127. *************************
  128. CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
  129. *************************
  130. Common Subexpression elimination (CSE)
  131. *************************
  132. CSE: CSE not performed on query block SEL$2 (#0).
  133. CSE: CSE not performed on query block SEL$1 (#0).
  134. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
  135. ***************************
  136. Order-by elimination (OBYE)
  137. ***************************
  138. OBYE: OBYE bypassed: no order by to eliminate.
  139. OJE: Begin: find best directive for query block SEL$1 (#0)
  140. OJE: End: finding best directive for query block SEL$1 (#0)
  141. OJE: Begin: find best directive for query block SEL$2 (#0)
  142. OJE: End: finding best directive for query block SEL$2 (#0)
  143. query block SEL$1 (#0) unchanged
  144. Considering Query Transformations on query block SEL$1 (#0)
  145. **************************
  146. Query transformations (QT)
  147. **************************
  148. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  149. *************************
  150. Common Subexpression elimination (CSE)
  151. *************************
  152. CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
  153. *************************
  154. Common Subexpression elimination (CSE)
  155. *************************
  156. CSE: CSE not performed on query block SEL$2 (#0).
  157. CSE: CSE not performed on query block SEL$1 (#0).
  158. query block SEL$1 (#0) unchanged
  159. apadrv-start sqlid=2637289499962258847
  160.   :
  161.     call(in-use=1760, alloc=16344), compile(in-use=61552, alloc=62688), execution(in-use=3456, alloc=4032)

  163. *******************************************
  164. Peeked values of the binds in SQL statement
  165. *******************************************

  167. CBQT: Considering cost-based transformation on query block SEL$1 (#0)
  168. ********************************
  169. COST-BASED QUERY TRANSFORMATIONS
  170. ********************************
  171. FPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0)
  172. FPD: Current where clause predicates ??

  174. FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
  175. FPD: Current where clause predicates DEPT.DEPTNO<>ALL (SELECT EMP.DEPTNO FROM EMP EMP)

  177. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
  178. ***************************
  179. Order-by elimination (OBYE)
  180. ***************************
  181. OBYE: OBYE bypassed: no order by to eliminate.
  182. Considering Query Transformations on query block SEL$1 (#0)
  183. **************************
  184. Query transformations (QT)
  185. **************************
  186. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  187. *************************
  188. Common Subexpression elimination (CSE)
  189. *************************
  190. CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
  191. *************************
  192. Common Subexpression elimination (CSE)
  193. *************************
  194. CSE: CSE not performed on query block SEL$2 (#0).
  195. CSE: CSE not performed on query block SEL$1 (#0).
  196. kkqctdrvTD-start on query block SEL$1 (#0)
  197. kkqctdrvTD-start: :
  198.     call(in-use=1760, alloc=16344), compile(in-use=104000, alloc=108064), execution(in-use=3456, alloc=4032)

  200. Registered qb: SEL$1 0x2ed90f98 (COPY SEL$1)
  201. ---------------------
  202. QUERY BLOCK SIGNATURE
  203. ---------------------
  204.   signature(): NULL
  205. Registered qb: SEL$2 0x2ed91878 (COPY SEL$2)
  206. ---------------------
  207. QUERY BLOCK SIGNATURE
  208. ---------------------
  209.   signature(): NULL
  210. *****************************
  211. Cost-Based Subquery Unnesting
  212. *****************************
  213. SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
  214. Subquery removal for query block SEL$2 (#2)
  215. RSW: Not valid for subquery removal SEL$2 (#2)
  216. Subquery unchanged.
  217. Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
  218. SU: Considering subquery unnest on query block SEL$1 (#1).
  219. SU: Checking validity of unnesting subquery SEL$2 (#2)
  220. SU: Passed validity checks.
  221. SU: Transform ALL subquery to a null-aware antijoin.
  222. SU: Checking validity of unnesting subquery SEL$2 (#3)
  223. SU: Validity checks failed.
  224. Registered qb: SEL$5DA710D3 0x2ed90f98 (SUBQUERY UNNEST SEL$1; SEL$2)
  225. ---------------------
  226. QUERY BLOCK SIGNATURE
  227. ---------------------
  228.   signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
  229.     fro(0): flg=0 objn=75333 hint_alias="DEPT"@"SEL$1"
  230.     fro(1): flg=0 objn=75335 hint_alias="EMP"@"SEL$2"

  232. *******************************
  233. Cost-Based Complex View Merging
  234. *******************************
  235. CVM: Finding query blocks in query block SEL$5DA710D3 (#1) that are valid to merge.
  236. OJE: Begin: find best directive for query block SEL$5DA710D3 (#1)
  237. OJE: End: finding best directive for query block SEL$5DA710D3 (#1)
  238. OJE: Begin: find best directive for query block SEL$2 (#3)
  239. OJE: End: finding best directive for query block SEL$2 (#3)
  240. kkqctdrvTD-cleanup: transform(in-use=5224, alloc=8392) :
  241.     call(in-use=2864, alloc=16344), compile(in-use=126952, alloc=131752), execution(in-use=3456, alloc=4032)

  243. kkqctdrvTD-end:
  244.     call(in-use=2864, alloc=16344), compile(in-use=118736, alloc=131752), execution(in-use=3456, alloc=4032)

  246. Subquery removal for query block SEL$2 (#2)
  247. RSW: Not valid for subquery removal SEL$2 (#2)
  248. Subquery unchanged.
  249. SU: Transform ALL subquery to a null-aware antijoin.
  250. SJC: Considering set-join conversion in query block SEL$5DA710D3 (#1)
  251. *************************
  252. Set-Join Conversion (SJC)
  253. *************************
  254. SJC: Considering set-join conversion in query block SEL$2 (#4)
  255. *************************
  256. Set-Join Conversion (SJC)
  257. *************************
  258. SJC: not performed
  259. SJC: not performed
  260. OJE: Begin: find best directive for query block SEL$5DA710D3 (#1)
  261. OJE: End: finding best directive for query block SEL$5DA710D3 (#1)
  262. OJE: Begin: find best directive for query block SEL$2 (#4)
  263. OJE: End: finding best directive for query block SEL$2 (#4)
  264. JE: Considering Join Elimination on query block SEL$5DA710D3 (#1)
  265. *************************
  266. Join Elimination (JE)
  267. *************************
  268. SQL:******* UNPARSED QUERY IS *******
  269. SELECT DEPT.DEPTNO DEPTNO,DEPT.DNAME DNAME,DEPT.LOC LOC FROM EMP EMP,DEPT DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND NOT EXISTS (SELECT 0 FROM EMP EMP WHERE EMP.DEPTNO IS NULL)
  270. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  271. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  272. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  273. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  274. SQL:******* UNPARSED QUERY IS *******
  275. SELECT DEPT.DEPTNO DEPTNO,DEPT.DNAME DNAME,DEPT.LOC LOC FROM EMP EMP,DEPT DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND NOT EXISTS (SELECT 0 FROM EMP EMP WHERE EMP.DEPTNO IS NULL)
  276. Query block SEL$5DA710D3 (#1) unchanged
  277. PM: Considering predicate move-around in query block SEL$5DA710D3 (#1)
  278. **************************
  279. Predicate Move-Around (PM)
  280. **************************
  281. PM: PM bypassed: Outer query contains no views.
  282. PM: PM bypassed: Outer query contains no views.
  283. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  284. kkqctdrvTD-start: :
  285.     call(in-use=4440, alloc=16344), compile(in-use=130192, alloc=136000), execution(in-use=3624, alloc=4032)

  287. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  288.     call(in-use=4440, alloc=16344), compile(in-use=130936, alloc=136000), execution(in-use=3624, alloc=4032)

  290. kkqctdrvTD-end:
  291.     call(in-use=4440, alloc=16344), compile(in-use=131336, alloc=136000), execution(in-use=3624, alloc=4032)

  293. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  294. kkqctdrvTD-start: :
  295.     call(in-use=4440, alloc=16344), compile(in-use=131336, alloc=136000), execution(in-use=3624, alloc=4032)

  297. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  298.     call(in-use=4440, alloc=16344), compile(in-use=132024, alloc=136000), execution(in-use=3624, alloc=4032)

  300. kkqctdrvTD-end:
  301.     call(in-use=4440, alloc=16344), compile(in-use=132424, alloc=136000), execution(in-use=3624, alloc=4032)

  303. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  304. kkqctdrvTD-start: :
  305.     call(in-use=4440, alloc=16344), compile(in-use=132424, alloc=136000), execution(in-use=3624, alloc=4032)

  307. TE: Checking validity of table expansion for query block SEL$2 (#2)
  308. TE: Bypassed: No partitioned table in query block.
  309. TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
  310. TE: Bypassed: No partitioned table in query block.
  311. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  312.     call(in-use=4440, alloc=16344), compile(in-use=133112, alloc=136000), execution(in-use=3624, alloc=4032)

  314. kkqctdrvTD-end:
  315.     call(in-use=4440, alloc=16344), compile(in-use=133512, alloc=136000), execution(in-use=3624, alloc=4032)

  317. TE: Checking validity of table expansion for query block SEL$2 (#2)
  318. TE: Bypassed: No partitioned table in query block.
  319. TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
  320. TE: Bypassed: No partitioned table in query block.
  321. ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
  322. SELECT \"DEPT\".\"DEPTNO\" \"DEPTNO\",\"DEPT\".\"DNAME\" \"DNAME\",\"DEPT\".\"LOC\" \"LOC\" FROM \"SCOTT\".\"EMP\" \"EMP\",\"SCOTT\".\"DEPT\" \"DEPT\" WHERE \"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\" AND NOT EXISTS (SELECT 0 FROM \"SCOTT\".\"EMP\" \"EMP\" WHERE \"EMP\".\"DEPTNO\" IS NULL)
  323. ST: not valid since star transformation parameter is FALSE
  324. ST: not valid since star transformation parameter is FALSE
  325. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  326. kkqctdrvTD-start: :
  327.     call(in-use=4584, alloc=16344), compile(in-use=133512, alloc=136000), execution(in-use=3624, alloc=4032)

  329. JF: Checking validity of join factorization for query block SEL$2 (#2)
  330. JF: Bypassed: not a UNION or UNION-ALL query block.
  331. JF: Checking validity of join factorization for query block SEL$5DA710D3 (#1)
  332. JF: Bypassed: not a UNION or UNION-ALL query block.
  333. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  334.     call(in-use=4584, alloc=16344), compile(in-use=134224, alloc=136000), execution(in-use=3624, alloc=4032)

  336. kkqctdrvTD-end:
  337.     call(in-use=4584, alloc=16344), compile(in-use=134624, alloc=136000), execution(in-use=3624, alloc=4032)

  339. JPPD: Considering Cost-based predicate pushdown from query block SEL$5DA710D3 (#1)
  340. ************************************
  341. Cost-based predicate pushdown (JPPD)
  342. ************************************
  343. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  344. kkqctdrvTD-start: :
  345.     call(in-use=4584, alloc=16344), compile(in-use=134624, alloc=136000), execution(in-use=3624, alloc=4032)

  347. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  348.     call(in-use=4584, alloc=16344), compile(in-use=135336, alloc=136000), execution(in-use=3624, alloc=4032)

  350. kkqctdrvTD-end:
  351.     call(in-use=4584, alloc=16344), compile(in-use=135752, alloc=136000), execution(in-use=3624, alloc=4032)

  353. JPPD: Applying transformation directives
  354. query block SEL$1 transformed to SEL$5DA710D3 (#1)
  355. FPD: Considering simple filter push in query block SEL$5DA710D3 (#1)
  356. \"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\" AND NOT EXISTS (SELECT 0 FROM \"EMP\" \"EMP\")
  357. FPD: Considering simple filter push in query block SEL$2 (#2)
  358. \"EMP\".\"DEPTNO\" IS NULL
  359. try to generate transitive predicate from check constraints for query block SEL$2 (#2)
  360. finally: \"EMP\".\"DEPTNO\" IS NULL

  362. try to generate transitive predicate from check constraints for query block SEL$5DA710D3 (#1)
  363. finally: DEPT.DEPTNO=EMP.DEPTNO AND NOT EXISTS (SELECT 0 FROM EMP EMP)

  365. Final query after transformations:******* UNPARSED QUERY IS *******
  366. SELECT DEPT.DEPTNO DEPTNO,DEPT.DNAME DNAME,DEPT.LOC LOC FROM EMP EMP,DEPT DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND NOT EXISTS (SELECT 0 FROM SCOTT.EMP EMP WHERE EMP.DEPTNO IS NULL)
  367. *************************
  368. First K Rows: Setup begin
  369. kkoqbc: optimizing query block SEL$2 (#2)
  370.         
  371.         :
  372.     call(in-use=4920, alloc=16344), compile(in-use=141688, alloc=144288), execution(in-use=3704, alloc=4032)

  374. kkoqbc-subheap (create addr=0x2b692ed1faa0)
  375. ****************
  376. QUERY BLOCK TEXT
  377. ****************
  378. select deptno from emp
  379. ---------------------
  380. QUERY BLOCK SIGNATURE
  381. ---------------------
  382. signature (optimizer): qb_name=SEL$2 nbfros=1 flg=0
  383.   fro(0): flg=0 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  385. -----------------------------
  386. SYSTEM STATISTICS INFORMATION
  387. -----------------------------
  388.   Using NOWORKLOAD Stats
  389.   CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
  390.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  391.   IOSEEKTIM: 10 milliseconds (default is 10)
  392.   MBRC: NO VALUE blocks (default is 8)

  394. ***************************************
  395. BASE STATISTICAL INFORMATION
  396. ***********************
  397. Table Stats::
  398.   Table: EMP Alias: EMP
  399.     #Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
  400. Index Stats::
  401.   Index: PK_EMP Col#: 1
  402.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  403. Access path analysis for EMP
  404. ***************************************
  405. SINGLE TABLE ACCESS PATH
  406.   Single Table Cardinality Estimation for EMP[EMP]
  407.   Column (#8): DEPTNO(
  408.     AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 10 Max: 30
  409.   Table: EMP Alias: EMP
  410.     Card: Original: 14.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
  411.   Access Path: TableScan
  412.     Cost: 3.00 Resp: 3.00 Degree: 0
  413.       Cost_io: 3.00 Cost_cpu: 39947
  414.       Resp_io: 3.00 Resp_cpu: 39947
  415.   Best:: AccessPath: TableScan
  416.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 0.00 Bytes: 0

  418. ***************************************


  421. OPTIMIZER STATISTICS AND COMPUTATIONS
  422. ***************************************
  423. GENERAL PLANS
  424. ***************************************
  425. Considering cardinality-based initial join order.
  426. Permutations for Starting Table :0
  427. Join order[1]: EMP[EMP]#0
  428. ***********************
  429. Best so far: Table#: 0 cost: 3.0019 card: 0.0000 bytes: 3
  430. ***********************

  432. *********************************
  433. Number of join permutations tried: 1
  434. *********************************
  435. Enumerating distribution method (advanced)

  437. Or-Expansion validity checks failed on query block SEL$2 (#2) because NO_EXPAND hint
  438. Transfer Optimizer annotations for query block SEL$2 (#2)
  439. Final cost for query block SEL$2 (#2) - All Rows Plan:
  440.   Best join order: 1
  441.   Cost: 3.0019 Degree: 1 Card: 1.0000 Bytes: 3
  442.   Resc: 3.0019 Resc_io: 3.0000 Resc_cpu: 39947
  443.   Resp: 3.0019 Resp_io: 3.0000 Resc_cpu: 39947
  444. kkoqbc-subheap (delete addr=0x2b692ed1faa0, in-use=13504, alloc=16408)
  445. kkoqbc-end:
  446.         :
  447.     call(in-use=13504, alloc=32816), compile(in-use=142192, alloc=144288), execution(in-use=3704, alloc=4032)

  449. kkoqbc: finish optimizing query block SEL$2 (#2)
  450. kkoqbc: optimizing query block SEL$5DA710D3 (#1)
  451.         
  452.         :
  453.     call(in-use=11264, alloc=32816), compile(in-use=148104, alloc=148432), execution(in-use=3864, alloc=4032)

  455. kkoqbc-subheap (create addr=0x2b692ed1faa0)
  456. ****************
  457. QUERY BLOCK TEXT
  458. ****************
  459. select * from dept where deptno not in (select deptno from emp)
  460. ---------------------
  461. QUERY BLOCK SIGNATURE
  462. ---------------------
  463. signature (optimizer): qb_name=SEL$5DA710D3 nbfros=2 flg=0
  464.   fro(0): flg=0 objn=75333 hint_alias=\"DEPT\"@\"SEL$1\"
  465.   fro(1): flg=0 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  467. -----------------------------
  468. SYSTEM STATISTICS INFORMATION
  469. -----------------------------
  470.   Using NOWORKLOAD Stats
  471.   CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
  472.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  473.   IOSEEKTIM: 10 milliseconds (default is 10)
  474.   MBRC: NO VALUE blocks (default is 8)

  476. ***************************************
  477. BASE STATISTICAL INFORMATION
  478. ***********************
  479. Table Stats::
  480.   Table: DEPT Alias: DEPT
  481.     #Rows: 4 #Blks: 5 AvgRowLen: 20.00 ChainCnt: 0.00
  482.   Column (#1): DEPTNO(
  483.     AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.250000 Min: 10 Max: 40
  484. Index Stats::
  485.   Index: PK_DEPT Col#: 1
  486.     LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  487. ***********************
  488. Table Stats::
  489.   Table: EMP Alias: EMP
  490.     #Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
  491.   Column (#8): DEPTNO(
  492.     AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 10 Max: 30
  493. Index Stats::
  494.   Index: PK_EMP Col#: 1
  495.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  496. Access path analysis for EMP
  497. ***************************************
  498. SINGLE TABLE ACCESS PATH
  499.   Single Table Cardinality Estimation for EMP[EMP]
  500.   Table: EMP Alias: EMP
  501.     Card: Original: 14.000000 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
  502.   Access Path: TableScan
  503.     Cost: 3.00 Resp: 3.00 Degree: 0
  504.       Cost_io: 3.00 Cost_cpu: 39667
  505.       Resp_io: 3.00 Resp_cpu: 39667
  506.   Best:: AccessPath: TableScan
  507.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 14.00 Bytes: 0

  509. Access path analysis for DEPT
  510. ***************************************
  511. SINGLE TABLE ACCESS PATH
  512.   Single Table Cardinality Estimation for DEPT[DEPT]
  513.   Table: DEPT Alias: DEPT
  514.     Card: Original: 4.000000 Rounded: 4 Computed: 4.00 Non Adjusted: 4.00
  515.   Access Path: TableScan
  516.     Cost: 3.00 Resp: 3.00 Degree: 0
  517.       Cost_io: 3.00 Cost_cpu: 36367
  518.       Resp_io: 3.00 Resp_cpu: 36367
  519.   Best:: AccessPath: TableScan
  520.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.00 Bytes: 0

  522. ***************************************


  525. OPTIMIZER STATISTICS AND COMPUTATIONS
  526. ***************************************
  527. GENERAL PLANS
  528. ***************************************
  529. Considering cardinality-based initial join order.
  530. Permutations for Starting Table :0
  531. Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1

  533. ***************
  534. Now joining: EMP[EMP]#1
  535. ***************
  536. NL Join
  537.   Outer table: Card: 4.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
  538. Access path analysis for EMP
  539.   Inner table: EMP Alias: EMP
  540.   Access Path: TableScan
  541.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  542.       Cost_io: 10.00 Cost_cpu: 195036
  543.       Resp_io: 10.00 Resp_cpu: 195036

  545.   Best NL cost: 10.01
  546.           resc: 10.01 resc_io: 10.00 resc_cpu: 195036
  547.           resp: 10.01 resp_io: 10.00 resc_cpu: 195036
  548. >>> adjusting AJ/SJ sel based on min/max ranges: jsel=min(0.750000, 0.666667)
  549. Anti Join Card: 1.333333 = outer (4.000000) * (1 - sel (0.666667))
  550. Join Card - Rounded: 1 Computed: 1.33
  551.   Outer table: DEPT Alias: DEPT
  552.     resc: 3.00 card 4.00 bytes: 20 deg: 1 resp: 3.00
  553.   Inner table: EMP Alias: EMP
  554.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  555.     using dmeth: 2 #groups: 1
  556.     SORT ressource Sort statistics
  557.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  558.       Degree: 1
  559.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  560.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  561.       Total IO sort cost: 0 Total CPU sort cost: 21021466
  562.       Total Temp space used: 0
  563.     SORT ressource Sort statistics
  564.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  565.       Degree: 1
  566.       Blocks to Sort: 1 Row size: 14 Total Rows: 14
  567.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  568.       Total IO sort cost: 0 Total CPU sort cost: 21023507
  569.       Total Temp space used: 0
  570.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  571. SM Join
  572.   SM cost: 8.00
  573.      resc: 8.00 resc_io: 6.00 resc_cpu: 42121007
  574.      resp: 8.00 resp_io: 6.00 resp_cpu: 42121007
  575. SM Join (with index on outer)
  576.   Access Path: index (FullScan)
  577.     Index: PK_DEPT
  578.     resc_io: 2.00 resc_cpu: 15803
  579.     ix_sel: 1.000000 ix_sel_with_filters: 1.000000
  580.     Cost: 2.00 Resp: 2.00 Degree: 1
  581.   Outer table: DEPT Alias: DEPT
  582.     resc: 2.00 card 4.00 bytes: 20 deg: 1 resp: 2.00
  583.   Inner table: EMP Alias: EMP
  584.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  585.     using dmeth: 2 #groups: 1
  586.     SORT ressource Sort statistics
  587.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  588.       Degree: 1
  589.       Blocks to Sort: 1 Row size: 14 Total Rows: 14
  590.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  591.       Total IO sort cost: 0 Total CPU sort cost: 21023507
  592.       Total Temp space used: 0
  593.   SM join: Resc: 6.00 Resp: 6.00 [multiMatchCost=0.00]
  594.   Outer table: DEPT Alias: DEPT
  595.     resc: 3.00 card 4.00 bytes: 20 deg: 1 resp: 3.00
  596.   Inner table: EMP Alias: EMP
  597.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  598.     using dmeth: 2 #groups: 1
  599.     Cost per ptn: 0.50 #ptns: 1
  600.     hash_area: 124 (max=14336) buildfrag: 1 probefrag: 1 ppasses: 1
  601.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  602. HA Join
  603.   HA cost: 6.50
  604.      resc: 6.50 resc_io: 6.00 resc_cpu: 10588587
  605.      resp: 6.50 resp_io: 6.00 resp_cpu: 10588587
  606. Best:: JoinMethod: SortMergeNullAwareAnti
  607.        Cost: 6.00 Degree: 1 Resp: 6.00 Card: 1.33 Bytes: 23
  608. ***********************
  609. Best so far: Table#: 0 cost: 2.0008 card: 4.0000 bytes: 80
  610.               Table#: 1 cost: 6.0028 card: 1.3333 bytes: 23
  611. ***********************

  613. *********************************
  614. Number of join permutations tried: 1
  615. *********************************
  616. Consider using bloom filter between DEPT[DEPT] and EMP[EMP] with ??
  617. kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables predicates
  618. Enumerating distribution method (advanced)
  619. --- Distribution method for
  620. join between DEPT[DEPT](serial) and EMP[EMP](serial); jm = 10; right side access path = TableScan
  621. ---- serial Sort-Merge-Join -> NONE

  623. Final adjusted join cardinality: 1, sq. fil. factor: 20.000000
  624. (newjo-save) [0 1 ]
  625. Or-Expansion validity checks failed on query block SEL$5DA710D3 (#1) because NO_EXPAND hint
  626. Transfer Optimizer annotations for query block SEL$5DA710D3 (#1)
  627. Final cost for query block SEL$5DA710D3 (#1) - All Rows Plan:
  628.   Best join order: 1
  629.   Cost: 6.0028 Degree: 1 Card: 1.0000 Bytes: 23
  630.   Resc: 6.0028 Resc_io: 5.0000 Resc_cpu: 21078977
  631.   Resp: 6.0028 Resp_io: 5.0000 Resc_cpu: 21078977
  632. kkoqbc-subheap (delete addr=0x2b692ed1faa0, in-use=24856, alloc=32840)
  633. kkoqbc-end:
  634.         :
  635.     call(in-use=25128, alloc=82128), compile(in-use=149096, alloc=152576), execution(in-use=3864, alloc=4032)

  637. kkoqbc: finish optimizing query block SEL$5DA710D3 (#1)
  638. First K Rows: Setup end
  639. ***********************
  640. kkoqbc: optimizing query block SEL$2 (#2)
  641.         
  642.         :
  643.     call(in-use=22920, alloc=82128), compile(in-use=149224, alloc=152576), execution(in-use=3864, alloc=4032)

  645. kkoqbc-subheap (create addr=0x2b692ed1faa0)
  646. ****************
  647. QUERY BLOCK TEXT
  648. ****************
  649. select deptno from emp
  650. ---------------------
  651. QUERY BLOCK SIGNATURE
  652. ---------------------
  653. signature (optimizer): qb_name=SEL$2 nbfros=1 flg=0
  654.   fro(0): flg=0 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  656. -----------------------------
  657. SYSTEM STATISTICS INFORMATION
  658. -----------------------------
  659.   Using NOWORKLOAD Stats
  660.   CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
  661.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  662.   IOSEEKTIM: 10 milliseconds (default is 10)
  663.   MBRC: NO VALUE blocks (default is 8)

  665. ***************************************
  666. BASE STATISTICAL INFORMATION
  667. ***********************
  668. Table Stats::
  669.   Table: EMP Alias: EMP
  670.     #Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
  671.   Column (#8): DEPTNO(
  672.     AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 10 Max: 30
  673. Index Stats::
  674.   Index: PK_EMP Col#: 1
  675.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  676. Access path analysis for EMP
  677. ***************************************
  678. SINGLE TABLE ACCESS PATH
  679.   Single Table Cardinality Estimation for EMP[EMP]
  680.   Table: EMP Alias: EMP
  681.     Card: Original: 14.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
  682.   Access Path: TableScan
  683.     Cost: 3.00 Resp: 3.00 Degree: 0
  684.       Cost_io: 3.00 Cost_cpu: 39947
  685.       Resp_io: 3.00 Resp_cpu: 39947
  686.   Best:: AccessPath: TableScan
  687.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 0.00 Bytes: 0

  689. ***************************************


  692. OPTIMIZER STATISTICS AND COMPUTATIONS
  693. ***************************************
  694. GENERAL PLANS
  695. ***************************************
  696. Considering cardinality-based initial join order.
  697. Permutations for Starting Table :0
  698. Join order[1]: EMP[EMP]#0
  699. ***********************
  700. Best so far: Table#: 0 cost: 3.0019 card: 0.0000 bytes: 3
  701. ***********************
  702. (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

  704. *********************************
  705. Number of join permutations tried: 1
  706. *********************************
  707. Enumerating distribution method (advanced)

  709. Trying or-Expansion on query block SEL$2 (#2)
  710. Transfer Optimizer annotations for query block SEL$2 (#2)
  711. id=0 frofand predicate=\"EMP\".\"DEPTNO\" IS NULL
  712. Final cost for query block SEL$2 (#2) - All Rows Plan:
  713.   Best join order: 1
  714.   Cost: 3.0019 Degree: 1 Card: 1.0000 Bytes: 3
  715.   Resc: 3.0019 Resc_io: 3.0000 Resc_cpu: 39947
  716.   Resp: 3.0019 Resp_io: 3.0000 Resc_cpu: 39947
  717. kkoqbc-subheap (delete addr=0x2b692ed1faa0, in-use=13504, alloc=16408)
  718. kkoqbc-end:
  719.         :
  720.     call(in-use=29752, alloc=82128), compile(in-use=149552, alloc=152576), execution(in-use=3864, alloc=4032)

  722. kkoqbc: finish optimizing query block SEL$2 (#2)
  723. kkoqbc: optimizing query block SEL$5DA710D3 (#1)
  724.         
  725.         :
  726.     call(in-use=29752, alloc=82128), compile(in-use=149552, alloc=152576), execution(in-use=3864, alloc=4032)

  728. kkoqbc-subheap (create addr=0x2b692ed1faa0)
  729. ****************
  730. QUERY BLOCK TEXT
  731. ****************
  732. select * from dept where deptno not in (select deptno from emp)
  733. ---------------------
  734. QUERY BLOCK SIGNATURE
  735. ---------------------
  736. signature (optimizer): qb_name=SEL$5DA710D3 nbfros=2 flg=0
  737.   fro(0): flg=0 objn=75333 hint_alias=\"DEPT\"@\"SEL$1\"
  738.   fro(1): flg=0 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  740. -----------------------------
  741. SYSTEM STATISTICS INFORMATION
  742. -----------------------------
  743.   Using NOWORKLOAD Stats
  744.   CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
  745.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  746.   IOSEEKTIM: 10 milliseconds (default is 10)
  747.   MBRC: NO VALUE blocks (default is 8)

  749. ***************************************
  750. BASE STATISTICAL INFORMATION
  751. ***********************
  752. Table Stats::
  753.   Table: DEPT Alias: DEPT
  754.     #Rows: 4 #Blks: 5 AvgRowLen: 20.00 ChainCnt: 0.00
  755.   Column (#3): LOC(
  756.     AvgLen: 8 NDV: 4 Nulls: 0 Density: 0.250000
  757.   Column (#2): DNAME(
  758.     AvgLen: 10 NDV: 4 Nulls: 0 Density: 0.250000
  759.   Column (#1): DEPTNO(
  760.     AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.250000 Min: 10 Max: 40
  761. Index Stats::
  762.   Index: PK_DEPT Col#: 1
  763.     LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  764. ***********************
  765. Table Stats::
  766.   Table: EMP Alias: EMP
  767.     #Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
  768.   Column (#8): DEPTNO(
  769.     AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 10 Max: 30
  770. Index Stats::
  771.   Index: PK_EMP Col#: 1
  772.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  773. Access path analysis for EMP
  774. ***************************************
  775. SINGLE TABLE ACCESS PATH
  776.   Single Table Cardinality Estimation for EMP[EMP]
  777.   Table: EMP Alias: EMP
  778.     Card: Original: 14.000000 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
  779.   Access Path: TableScan
  780.     Cost: 3.00 Resp: 3.00 Degree: 0
  781.       Cost_io: 3.00 Cost_cpu: 39667
  782.       Resp_io: 3.00 Resp_cpu: 39667
  783.   Best:: AccessPath: TableScan
  784.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 14.00 Bytes: 0

  786. Access path analysis for DEPT
  787. ***************************************
  788. SINGLE TABLE ACCESS PATH
  789.   Single Table Cardinality Estimation for DEPT[DEPT]
  790.   Table: DEPT Alias: DEPT
  791.     Card: Original: 4.000000 Rounded: 4 Computed: 4.00 Non Adjusted: 4.00
  792.   Access Path: TableScan
  793.     Cost: 3.00 Resp: 3.00 Degree: 0
  794.       Cost_io: 3.00 Cost_cpu: 36367
  795.       Resp_io: 3.00 Resp_cpu: 36367
  796.   Best:: AccessPath: TableScan
  797.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.00 Bytes: 0

  799. ***************************************


  802. OPTIMIZER STATISTICS AND COMPUTATIONS
  803. ***************************************
  804. GENERAL PLANS
  805. ***************************************
  806. Considering cardinality-based initial join order.
  807. Permutations for Starting Table :0
  808. Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1

  810. ***************
  811. Now joining: EMP[EMP]#1
  812. ***************
  813. NL Join
  814.   Outer table: Card: 4.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
  815. Access path analysis for EMP
  816.   Inner table: EMP Alias: EMP
  817.   Access Path: TableScan
  818.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  819.       Cost_io: 10.00 Cost_cpu: 195036
  820.       Resp_io: 10.00 Resp_cpu: 195036

  822.   Best NL cost: 13.01
  823.           resc: 10.01 resc_io: 10.00 resc_cpu: 195036
  824.           resp: 10.01 resp_io: 10.00 resc_cpu: 195036
  825. >>> adjusting AJ/SJ sel based on min/max ranges: jsel=min(0.750000, 0.666667)
  826. Anti Join Card: 1.333333 = outer (4.000000) * (1 - sel (0.666667))
  827. Join Card - Rounded: 1 Computed: 1.33
  828.   Outer table: DEPT Alias: DEPT
  829.     resc: 3.00 card 4.00 bytes: 20 deg: 1 resp: 3.00
  830.   Inner table: EMP Alias: EMP
  831.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  832.     using dmeth: 2 #groups: 1
  833.     SORT ressource Sort statistics
  834.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  835.       Degree: 1
  836.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  837.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  838.       Total IO sort cost: 0 Total CPU sort cost: 21021466
  839.       Total Temp space used: 0
  840.     SORT ressource Sort statistics
  841.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  842.       Degree: 1
  843.       Blocks to Sort: 1 Row size: 14 Total Rows: 14
  844.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  845.       Total IO sort cost: 0 Total CPU sort cost: 21023507
  846.       Total Temp space used: 0
  847.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  848. SM Join
  849.   SM cost: 8.00
  850.      resc: 8.00 resc_io: 6.00 resc_cpu: 42121007
  851.      resp: 8.00 resp_io: 6.00 resp_cpu: 42121007
  852. SM Join (with index on outer)
  853.   Access Path: index (FullScan)
  854.     Index: PK_DEPT
  855.     resc_io: 2.00 resc_cpu: 15803
  856.     ix_sel: 1.000000 ix_sel_with_filters: 1.000000
  857.     Cost: 2.00 Resp: 2.00 Degree: 1
  858.   Outer table: DEPT Alias: DEPT
  859.     resc: 2.00 card 4.00 bytes: 20 deg: 1 resp: 2.00
  860.   Inner table: EMP Alias: EMP
  861.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  862.     using dmeth: 2 #groups: 1
  863.     SORT ressource Sort statistics
  864.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  865.       Degree: 1
  866.       Blocks to Sort: 1 Row size: 14 Total Rows: 14
  867.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  868.       Total IO sort cost: 0 Total CPU sort cost: 21023507
  869.       Total Temp space used: 0
  870.   SM join: Resc: 6.00 Resp: 6.00 [multiMatchCost=0.00]
  871.   Outer table: DEPT Alias: DEPT
  872.     resc: 3.00 card 4.00 bytes: 20 deg: 1 resp: 3.00
  873.   Inner table: EMP Alias: EMP
  874.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  875.     using dmeth: 2 #groups: 1
  876.     Cost per ptn: 0.50 #ptns: 1
  877.     hash_area: 124 (max=14336) buildfrag: 1 probefrag: 1 ppasses: 1
  878.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  879. HA Join
  880.   HA cost: 6.50
  881.      resc: 6.50 resc_io: 6.00 resc_cpu: 10588587
  882.      resp: 6.50 resp_io: 6.00 resp_cpu: 10588587
  883. Best:: JoinMethod: SortMergeNullAwareAnti
  884.        Cost: 6.00 Degree: 1 Resp: 6.00 Card: 1.33 Bytes: 23
  885. ***********************
  886. Best so far: Table#: 0 cost: 2.0008 card: 4.0000 bytes: 80
  887.               Table#: 1 cost: 6.0028 card: 1.3333 bytes: 23
  888. ***********************
  889. (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

  891. *********************************
  892. Number of join permutations tried: 1
  893. *********************************
  894. Consider using bloom filter between DEPT[DEPT] and EMP[EMP] with ??
  895. kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables predicates
  896. Enumerating distribution method (advanced)
  897. --- Distribution method for
  898. join between DEPT[DEPT](serial) and EMP[EMP](serial); jm = 10; right side access path = TableScan
  899. ---- serial Sort-Merge-Join -> NONE

  901. (newjo-save) [0 1 ]
  902. Trying or-Expansion on query block SEL$5DA710D3 (#1)
  903. Transfer Optimizer annotations for query block SEL$5DA710D3 (#1)
  904. id=0 frofkksm[i] (sort-merge/hash) predicate=\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  905. id=0 frosand (sort-merge/hash) predicate=\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  906. Final cost for query block SEL$5DA710D3 (#1) - All Rows Plan:
  907.   Best join order: 1
  908.   Cost: 6.0028 Degree: 1 Card: 1.0000 Bytes: 23
  909.   Resc: 6.0028 Resc_io: 5.0000 Resc_cpu: 21078977
  910.   Resp: 6.0028 Resp_io: 5.0000 Resc_cpu: 21078977
  911. kkoqbc-subheap (delete addr=0x2b692ed1faa0, in-use=24856, alloc=32840)
  912. kkoqbc-end:
  913.         :
  914.     call(in-use=38760, alloc=98600), compile(in-use=151272, alloc=152576), execution(in-use=3864, alloc=4032)

  916. kkoqbc: finish optimizing query block SEL$5DA710D3 (#1)
  917. apadrv-end
  918.           :
  919.     call(in-use=38760, alloc=98600), compile(in-use=152256, alloc=152576), execution(in-use=3864, alloc=4032)


  922. Starting SQL statement dump

  924. user_id=83 user_name=SCOTT module=SQL*Plus action=
  925. sql_id=296c8ug3jwdcz plan_hash_value=-2064285032 problem_type=3
  926. ----- Current SQL Statement for this session (sql_id=296c8ug3jwdcz) -----
  927. select * from dept where deptno not in (select deptno from emp)
  928. sql_text_length=64
  929. sql=select * from dept where deptno not in (select deptno from emp)
  930. ----- Explain Plan Dump -----
  931. ----- Plan Table -----
  932.  
  933. ============
  934. Plan Table
  935. ============
  936. -------------------------------------------------+--------------------------------+
  937. | Id | Operation                      | Name    | Rows | Bytes | Cost |     Time |
  938. -------------------------------------------------+--------------------------------+
  939. |  0 | SELECT STATEMENT               |         |      |       |    6 |          |
  940. |  1 |  MERGE JOIN ANTI NA            |         |    1 |    23 |    6 | 00:00:01 |
  941. |  2 |   SORT JOIN                    |         |    4 |    80 |    2 | 00:00:01 |
  942. |  3 |    TABLE ACCESS BY INDEX ROWID | DEPT    |    4 |    80 |    2 | 00:00:01 |
  943. |  4 |     INDEX FULL SCAN            | PK_DEPT |    4 |       |    1 | 00:00:01 |
  944. |  5 |   SORT UNIQUE                  |         |   14 |    42 |    4 | 00:00:01 |
  945. |  6 |    TABLE ACCESS FULL           | EMP     |   14 |    42 |    3 | 00:00:01 |
  946. -------------------------------------------------+--------------------------------+
  947. Predicate Information:
  948. ----------------------
  949. 5 - access("DEPTNO"="DEPTNO")
  950. 5 - filter("DEPTNO"="DEPTNO")
  951.  
  952. Content of other_xml column
  953. ===========================
  954.   db_version : 11.2.0.3
  955.   parse_schema : SCOTT
  956.   plan_hash : 2230682264
  957.   plan_hash_2 : 644022833
  958.   Outline Data:
  959.   /*+
  960.     BEGIN_OUTLINE_DATA
  961.       IGNORE_OPTIM_EMBEDDED_HINTS
  962.       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
  963.       DB_VERSION('11.2.0.3')
  964.       ALL_ROWS
  965.       OUTLINE_LEAF(@"SEL$5DA710D3")
  966.       UNNEST(@"SEL$2")
  967.       OUTLINE(@"SEL$1")
  968.       OUTLINE(@"SEL$2")
  969.       INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
  970.       FULL(@"SEL$5DA710D3" "EMP"@"SEL$2")
  971.       LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$1" "EMP"@"SEL$2")
  972.       USE_MERGE(@"SEL$5DA710D3" "EMP"@"SEL$2")
  973.     END_OUTLINE_DATA
  974.   */

ANTI NA 是中join的型別，他是拿語法寫不出來的，就是內部的一個演算法。以前oracle沒有這種join，10g都沒有，是11.1.0.6加進去的。
deptno列設定為not null我們看一下：（not null 執行計劃不會考慮是NA這種情況，就直接用的anti）




點選(此處)摺疊或開啟

  1. *** 2015-02-28 09:47:15.951
  2. Registered qb: SEL$1 0x772b66d0 (PARSER)
  3. ---------------------
  4. QUERY BLOCK SIGNATURE
  5. ---------------------
  6.   signature (): qb_name=SEL$1 nbfros=1 flg=0
  7.     fro(0): flg=4 objn=75333 hint_alias=\"DEPT\"@\"SEL$1\"

  9. Registered qb: SEL$2 0x772b1490 (PARSER)
  10. ---------------------
  11. QUERY BLOCK SIGNATURE
  12. ---------------------
  13.   signature (): qb_name=SEL$2 nbfros=1 flg=0
  14.     fro(0): flg=4 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  16. SPM: statement not found in SMB

  18. **************************
  19. Automatic degree of parallelism (ADOP)
  20. **************************
  21. Automatic degree of parallelism is disabled: Parameter.

  23. PM: Considering predicate move-around in query block SEL$1 (#0)
  24. **************************
  25. Predicate Move-Around (PM)
  26. **************************
  27. OPTIMIZER INFORMATION

  29. ******************************************
  30. ----- Current SQL Statement for this session (sql_id=0jfbpts9pskp8) -----
  31. select /* sample 9 */ * from dept where deptno not in (select deptno from emp)
  32. *******************************************
  33. Legend
  34. The following abbreviations are used by optimizer trace.
  35. CBQT - cost-based query transformation
  36. JPPD - join predicate push-down
  37. OJPPD - old-style (non-cost-based) JPPD
  38. FPD - filter push-down
  39. PM - predicate move-around
  40. CVM - complex view merging
  41. SPJ - select-project-join
  42. SJC - set join conversion
  43. SU - subquery unnesting
  44. OBYE - order by elimination
  45. OST - old style star transformation
  46. ST - new (cbqt) star transformation
  47. CNT - count(col) to count(*) transformation
  48. JE - Join Elimination
  49. JF - join factorization
  50. SLP - select list pruning
  51. DP - distinct placement
  52. qb - query block
  53. LB - leaf blocks
  54. DK - distinct keys
  55. LB/K - average number of leaf blocks per key
  56. DB/K - average number of data blocks per key
  57. CLUF - clustering factor
  58. NDV - number of distinct values
  59. Resp - response cost
  60. Card - cardinality
  61. Resc - resource cost
  62. NL - nested loops (join)
  63. SM - sort merge (join)
  64. HA - hash (join)
  65. CPUSPEED - CPU Speed
  66. IOTFRSPEED - I/O transfer speed
  67. IOSEEKTIM - I/O seek time
  68. SREADTIM - average single block read time
  69. MREADTIM - average multiblock read time
  70. MBRC - average multiblock read count
  71. MAXTHR - maximum I/O system throughput
  72. SLAVETHR - average slave I/O throughput
  73. dmeth - distribution method
  74.   1: no partitioning required
  75.   2: value partitioned
  76.   4: right is random (round-robin)
  77.   128: left is random (round-robin)
  78.   8: broadcast right and partition left
  79.   16: broadcast left and partition right
  80.   32: partition left using partitioning of right
  81.   64: partition right using partitioning of left
  82.   256: run the join in serial
  83.   0: invalid distribution method
  84. sel - selectivity
  85. ptn - partition
  86. ***************************************
  87. PARAMETERS USED BY THE OPTIMIZER
  88. ********************************
  89.   *************************************
  90.   PARAMETERS WITH ALTERED VALUES
  91.   ******************************
  92. Compilation Environment Dump
  93. Bug Fix Control Environment


  96.   *************************************
  97.   PARAMETERS WITH DEFAULT VALUES
  98.   ******************************
  99. Compilation Environment Dump
  100. optimizer_mode_hinted = false


  103.   ***************************************
  104.   PARAMETERS IN OPT_PARAM HINT
  105.   ****************************
  106. ***************************************
  107. Column Usage Monitoring is ON: tracking level = 1
  108. ***************************************

  110. Considering Query Transformations on query block SEL$1 (#0)
  111. **************************
  112. Query transformations (QT)
  113. **************************
  114. JF: Checking validity of join factorization for query block SEL$2 (#0)
  115. JF: Bypassed: not a UNION or UNION-ALL query block.
  116. ST: not valid since star transformation parameter is FALSE
  117. TE: Checking validity of table expansion for query block SEL$2 (#0)
  118. TE: Bypassed: No partitioned table in query block.
  119. CBQT: Validity checks passed for 0jfbpts9pskp8.
  120. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  121. *************************
  122. Common Subexpression elimination (CSE)
  123. *************************
  124. CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
  125. *************************
  126. Common Subexpression elimination (CSE)
  127. *************************
  128. CSE: CSE not performed on query block SEL$2 (#0).
  129. CSE: CSE not performed on query block SEL$1 (#0).
  130. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
  131. ***************************
  132. Order-by elimination (OBYE)
  133. ***************************
  134. OBYE: OBYE bypassed: no order by to eliminate.
  135. OJE: Begin: find best directive for query block SEL$1 (#0)
  136. OJE: End: finding best directive for query block SEL$1 (#0)
  137. OJE: Begin: find best directive for query block SEL$2 (#0)
  138. OJE: End: finding best directive for query block SEL$2 (#0)
  139. query block SEL$1 (#0) unchanged
  140. Considering Query Transformations on query block SEL$1 (#0)
  141. **************************
  142. Query transformations (QT)
  143. **************************
  144. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  145. *************************
  146. Common Subexpression elimination (CSE)
  147. *************************
  148. CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
  149. *************************
  150. Common Subexpression elimination (CSE)
  151. *************************
  152. CSE: CSE not performed on query block SEL$2 (#0).
  153. CSE: CSE not performed on query block SEL$1 (#0).
  154. query block SEL$1 (#0) unchanged
  155. apadrv-start sqlid=628663150943423144
  156.   :
  157.     call(in-use=1760, alloc=16344), compile(in-use=61568, alloc=62200), execution(in-use=3456, alloc=4032)

  159. *******************************************
  160. Peeked values of the binds in SQL statement
  161. *******************************************

  163. CBQT: Considering cost-based transformation on query block SEL$1 (#0)
  164. ********************************
  165. COST-BASED QUERY TRANSFORMATIONS
  166. ********************************
  167. FPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0)
  168. FPD: Current where clause predicates ??

  170. FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
  171. FPD: Current where clause predicates \"DEPT\".\"DEPTNO\"<>ALL (SELECT \"EMP\".\"DEPTNO\" FROM \"EMP\" \"EMP\")

  173. OBYE: Considering Order-by Elimination from view SEL$1 (#0)
  174. ***************************
  175. Order-by elimination (OBYE)
  176. ***************************
  177. OBYE: OBYE bypassed: no order by to eliminate.
  178. Considering Query Transformations on query block SEL$1 (#0)
  179. **************************
  180. Query transformations (QT)
  181. **************************
  182. CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
  183. *************************
  184. Common Subexpression elimination (CSE)
  185. *************************
  186. CSE: Considering common sub-expression elimination in query block SEL$2 (#0)
  187. *************************
  188. Common Subexpression elimination (CSE)
  189. *************************
  190. CSE: CSE not performed on query block SEL$2 (#0).
  191. CSE: CSE not performed on query block SEL$1 (#0).
  192. kkqctdrvTD-start on query block SEL$1 (#0)
  193. kkqctdrvTD-start: :
  194.     call(in-use=1760, alloc=16344), compile(in-use=104008, alloc=107576), execution(in-use=3456, alloc=4032)

  196. Registered qb: SEL$1 0x77351f98 (COPY SEL$1)
  197. ---------------------
  198. QUERY BLOCK SIGNATURE
  199. ---------------------
  200.   signature(): NULL
  201. Registered qb: SEL$2 0x77352878 (COPY SEL$2)
  202. ---------------------
  203. QUERY BLOCK SIGNATURE
  204. ---------------------
  205.   signature(): NULL
  206. *****************************
  207. Cost-Based Subquery Unnesting
  208. *****************************
  209. SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
  210. Subquery removal for query block SEL$2 (#2)
  211. RSW: Not valid for subquery removal SEL$2 (#2)
  212. Subquery unchanged.
  213. Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
  214. SU: Considering subquery unnest on query block SEL$1 (#1).
  215. SU: Checking validity of unnesting subquery SEL$2 (#2)
  216. SU: Passed validity checks.
  217. SU: Transform ALL subquery to a regular antijoin.
  218. Registered qb: SEL$5DA710D3 0x77351f98 (SUBQUERY UNNEST SEL$1; SEL$2)
  219. ---------------------
  220. QUERY BLOCK SIGNATURE
  221. ---------------------
  222.   signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
  223.     fro(0): flg=0 objn=75333 hint_alias=\"DEPT\"@\"SEL$1\"
  224.     fro(1): flg=0 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  226. *******************************
  227. Cost-Based Complex View Merging
  228. *******************************
  229. CVM: Finding query blocks in query block SEL$5DA710D3 (#1) that are valid to merge.
  230. OJE: Begin: find best directive for query block SEL$5DA710D3 (#1)
  231. OJE: End: finding best directive for query block SEL$5DA710D3 (#1)
  232. kkqctdrvTD-cleanup: transform(in-use=4528, alloc=8392) :
  233.     call(in-use=2456, alloc=16344), compile(in-use=121344, alloc=131264), execution(in-use=3456, alloc=4032)

  235. kkqctdrvTD-end:
  236.     call(in-use=2456, alloc=16344), compile(in-use=113128, alloc=131264), execution(in-use=3456, alloc=4032)

  238. Subquery removal for query block SEL$2 (#2)
  239. RSW: Not valid for subquery removal SEL$2 (#2)
  240. Subquery unchanged.
  241. SU: Transform ALL subquery to a regular antijoin.
  242. SJC: Considering set-join conversion in query block SEL$5DA710D3 (#1)
  243. *************************
  244. Set-Join Conversion (SJC)
  245. *************************
  246. SJC: not performed
  247. OJE: Begin: find best directive for query block SEL$5DA710D3 (#1)
  248. OJE: End: finding best directive for query block SEL$5DA710D3 (#1)
  249. JE: Considering Join Elimination on query block SEL$5DA710D3 (#1)
  250. *************************
  251. Join Elimination (JE)
  252. *************************
  253. SQL:******* UNPARSED QUERY IS *******
  254. SELECT \"DEPT\".\"DEPTNO\" \"DEPTNO\",\"DEPT\".\"DNAME\" \"DNAME\",\"DEPT\".\"LOC\" \"LOC\" FROM \"SCOTT\".\"EMP\" \"EMP\",\"SCOTT\".\"DEPT\" \"DEPT\" WHERE \"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  255. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  256. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  257. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  258. JE: cfro: EMP objn:75333 col#:8 dfro:DEPT dcol#:1
  259. JE: cfro: DEPT objn:75335 col#:1 dfro:EMP dcol#:8
  260. SQL:******* UNPARSED QUERY IS *******
  261. SELECT DEPT.DEPTNO DEPTNO,DEPT.DNAME DNAME,DEPT.LOC LOC FROM EMP EMP,SCOTT.DEPT DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO
  262. Query block SEL$5DA710D3 (#1) unchanged
  263. PM: Considering predicate move-around in query block SEL$5DA710D3 (#1)
  264. **************************
  265. Predicate Move-Around (PM)
  266. **************************
  267. PM: PM bypassed: Outer query contains no views.
  268. PM: PM bypassed: Outer query contains no views.
  269. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  270. kkqctdrvTD-start: :
  271.     call(in-use=3432, alloc=16344), compile(in-use=115208, alloc=131264), execution(in-use=3624, alloc=4032)

  273. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  274.     call(in-use=3432, alloc=16344), compile(in-use=115800, alloc=131264), execution(in-use=3624, alloc=4032)

  276. kkqctdrvTD-end:
  277.     call(in-use=3432, alloc=16344), compile(in-use=116080, alloc=131264), execution(in-use=3624, alloc=4032)

  279. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  280. kkqctdrvTD-start: :
  281.     call(in-use=3432, alloc=16344), compile(in-use=116080, alloc=131264), execution(in-use=3624, alloc=4032)

  283. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  284.     call(in-use=3432, alloc=16344), compile(in-use=116648, alloc=131264), execution(in-use=3624, alloc=4032)

  286. kkqctdrvTD-end:
  287.     call(in-use=3432, alloc=16344), compile(in-use=116928, alloc=131264), execution(in-use=3624, alloc=4032)

  289. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  290. kkqctdrvTD-start: :
  291.     call(in-use=3432, alloc=16344), compile(in-use=116928, alloc=131264), execution(in-use=3624, alloc=4032)

  293. TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
  294. TE: Bypassed: No partitioned table in query block.
  295. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  296.     call(in-use=3432, alloc=16344), compile(in-use=117520, alloc=131264), execution(in-use=3624, alloc=4032)

  298. kkqctdrvTD-end:
  299.     call(in-use=3432, alloc=16344), compile(in-use=117800, alloc=131264), execution(in-use=3624, alloc=4032)

  301. TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
  302. TE: Bypassed: No partitioned table in query block.
  303. ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
  304. SELECT \"DEPT\".\"DEPTNO\" \"DEPTNO\",\"DEPT\".\"DNAME\" \"DNAME\",\"DEPT\".\"LOC\" \"LOC\" FROM \"SCOTT\".\"EMP\" \"EMP\",\"SCOTT\".\"DEPT\" \"DEPT\" WHERE \"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  305. ST: not valid since star transformation parameter is FALSE
  306. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  307. kkqctdrvTD-start: :
  308.     call(in-use=3480, alloc=16344), compile(in-use=117800, alloc=131264), execution(in-use=3624, alloc=4032)

  310. JF: Checking validity of join factorization for query block SEL$5DA710D3 (#1)
  311. JF: Bypassed: not a UNION or UNION-ALL query block.
  312. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  313.     call(in-use=3480, alloc=16344), compile(in-use=118368, alloc=131264), execution(in-use=3624, alloc=4032)

  315. kkqctdrvTD-end:
  316.     call(in-use=3480, alloc=16344), compile(in-use=118648, alloc=131264), execution(in-use=3624, alloc=4032)

  318. JPPD: Considering Cost-based predicate pushdown from query block SEL$5DA710D3 (#1)
  319. ************************************
  320. Cost-based predicate pushdown (JPPD)
  321. ************************************
  322. kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
  323. kkqctdrvTD-start: :
  324.     call(in-use=3480, alloc=16344), compile(in-use=118648, alloc=131264), execution(in-use=3624, alloc=4032)

  326. kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
  327.     call(in-use=3480, alloc=16344), compile(in-use=119216, alloc=131264), execution(in-use=3624, alloc=4032)

  329. kkqctdrvTD-end:
  330.     call(in-use=3480, alloc=16344), compile(in-use=119496, alloc=131264), execution(in-use=3624, alloc=4032)

  332. JPPD: Applying transformation directives
  333. query block SEL$1 transformed to SEL$5DA710D3 (#1)
  334. FPD: Considering simple filter push in query block SEL$5DA710D3 (#1)
  335. \"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  336. try to generate transitive predicate from check constraints for query block SEL$5DA710D3 (#1)
  337. finally: DEPT.DEPTNO=EMP.DEPTNO

  339. Final query after transformations:******* UNPARSED QUERY IS *******
  340. SELECT DEPT.DEPTNO DEPTNO,DEPT.DNAME DNAME,DEPT.LOC LOC FROM EMP EMP,DEPT DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO
  341. kkoqbc: optimizing query block SEL$5DA710D3 (#1)
  342.         
  343.         :
  344.     call(in-use=3720, alloc=16344), compile(in-use=120800, alloc=131264), execution(in-use=3624, alloc=4032)

  346. kkoqbc-subheap (create addr=0x2af4772bfaa0)
  347. ****************
  348. QUERY BLOCK TEXT
  349. ****************
  350. select /* sample 9 */ * from dept where deptno not in (select deptno from emp)
  351. ---------------------
  352. QUERY BLOCK SIGNATURE
  353. ---------------------
  354. signature (optimizer): qb_name=SEL$5DA710D3 nbfros=2 flg=0
  355.   fro(0): flg=0 objn=75333 hint_alias=\"DEPT\"@\"SEL$1\"
  356.   fro(1): flg=0 objn=75335 hint_alias=\"EMP\"@\"SEL$2\"

  358. -----------------------------
  359. SYSTEM STATISTICS INFORMATION
  360. -----------------------------
  361.   Using NOWORKLOAD Stats
  362.   CPUSPEEDNW: 1752 millions instructions/sec (default is 100)
  363.   IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  364.   IOSEEKTIM: 10 milliseconds (default is 10)
  365.   MBRC: NO VALUE blocks (default is 8)

  367. ***************************************
  368. BASE STATISTICAL INFORMATION
  369. ***********************
  370. Table Stats::
  371.   Table: DEPT Alias: DEPT
  372.     #Rows: 4 #Blks: 5 AvgRowLen: 20.00 ChainCnt: 0.00
  373.   Column (#1): DEPTNO(
  374.     AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.250000 Min: 10 Max: 40
  375. Index Stats::
  376.   Index: PK_DEPT Col#: 1
  377.     LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  378. ***********************
  379. Table Stats::
  380.   Table: EMP Alias: EMP
  381.     #Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
  382.   Column (#8): DEPTNO(
  383.     AvgLen: 3 NDV: 3 Nulls: 0 Density: 0.333333 Min: 10 Max: 30
  384. Index Stats::
  385.   Index: PK_EMP Col#: 1
  386.     LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
  387. Access path analysis for EMP
  388. ***************************************
  389. SINGLE TABLE ACCESS PATH
  390.   Single Table Cardinality Estimation for EMP[EMP]
  391.   Table: EMP Alias: EMP
  392.     Card: Original: 14.000000 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
  393.   Access Path: TableScan
  394.     Cost: 3.00 Resp: 3.00 Degree: 0
  395.       Cost_io: 3.00 Cost_cpu: 39667
  396.       Resp_io: 3.00 Resp_cpu: 39667
  397.   Best:: AccessPath: TableScan
  398.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 14.00 Bytes: 0

  400. Access path analysis for DEPT
  401. ***************************************
  402. SINGLE TABLE ACCESS PATH
  403.   Single Table Cardinality Estimation for DEPT[DEPT]
  404.   Table: DEPT Alias: DEPT
  405.     Card: Original: 4.000000 Rounded: 4 Computed: 4.00 Non Adjusted: 4.00
  406.   Access Path: TableScan
  407.     Cost: 3.00 Resp: 3.00 Degree: 0
  408.       Cost_io: 3.00 Cost_cpu: 36367
  409.       Resp_io: 3.00 Resp_cpu: 36367
  410.   Best:: AccessPath: TableScan
  411.          Cost: 3.00 Degree: 1 Resp: 3.00 Card: 4.00 Bytes: 0

  413. ***************************************


  416. OPTIMIZER STATISTICS AND COMPUTATIONS
  417. ***************************************
  418. GENERAL PLANS
  419. ***************************************
  420. Considering cardinality-based initial join order.
  421. Permutations for Starting Table :0
  422. Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1

  424. ***************
  425. Now joining: EMP[EMP]#1
  426. ***************
  427. NL Join
  428.   Outer table: Card: 4.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 20
  429. Access path analysis for EMP
  430.   Inner table: EMP Alias: EMP
  431.   Access Path: TableScan
  432.     NL Join: Cost: 10.01 Resp: 10.01 Degree: 1
  433.       Cost_io: 10.00 Cost_cpu: 195036
  434.       Resp_io: 10.00 Resp_cpu: 195036

  436.   Best NL cost: 10.01
  437.           resc: 10.01 resc_io: 10.00 resc_cpu: 195036
  438.           resp: 10.01 resp_io: 10.00 resc_cpu: 195036
  439. >>> adjusting AJ/SJ sel based on min/max ranges: jsel=min(0.750000, 0.666667)
  440. Anti Join Card: 1.333333 = outer (4.000000) * (1 - sel (0.666667))
  441. Join Card - Rounded: 1 Computed: 1.33
  442.   Outer table: DEPT Alias: DEPT
  443.     resc: 3.00 card 4.00 bytes: 20 deg: 1 resp: 3.00
  444.   Inner table: EMP Alias: EMP
  445.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  446.     using dmeth: 2 #groups: 1
  447.     SORT ressource Sort statistics
  448.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  449.       Degree: 1
  450.       Blocks to Sort: 1 Row size: 32 Total Rows: 4
  451.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  452.       Total IO sort cost: 0 Total CPU sort cost: 21021466
  453.       Total Temp space used: 0
  454.     SORT ressource Sort statistics
  455.       Sort width: 334 Area size: 292864 Max Area size: 58720256
  456.       Degree: 1
  457.       Blocks to Sort: 1 Row size: 14 Total Rows: 14
  458.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  459.       Total IO sort cost: 0 Total CPU sort cost: 21023507
  460.       Total Temp space used: 0
  461.   SM join: Resc: 8.00 Resp: 8.00 [multiMatchCost=0.00]
  462. SM Join
  463.   SM cost: 8.00
  464.      resc: 8.00 resc_io: 6.00 resc_cpu: 42121007
  465.      resp: 8.00 resp_io: 6.00 resp_cpu: 42121007
  466. SM Join (with index on outer)
  467.   Access Path: index (FullScan)
  468.     Index: PK_DEPT
  469.     resc_io: 2.00 resc_cpu: 15803
  470.     ix_sel: 1.000000 ix_sel_with_filters: 1.000000
  471.     Cost: 2.00 Resp: 2.00 Degree: 1
  472.   Outer table: DEPT Alias: DEPT
  473.     resc: 2.00 card 4.00 bytes: 20 deg: 1 resp: 2.00
  474.   Inner table: EMP Alias: EMP
  475.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  476.     using dmeth: 2 #groups: 1
  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: 14 Total Rows: 14
  481.       Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
  482.       Total IO sort cost: 0 Total CPU sort cost: 21023507
  483.       Total Temp space used: 0
  484.   SM join: Resc: 6.00 Resp: 6.00 [multiMatchCost=0.00]
  485.   Outer table: DEPT Alias: DEPT
  486.     resc: 3.00 card 4.00 bytes: 20 deg: 1 resp: 3.00
  487.   Inner table: EMP Alias: EMP
  488.     resc: 3.00 card: 14.00 bytes: 3 deg: 1 resp: 3.00
  489.     using dmeth: 2 #groups: 1
  490.     Cost per ptn: 0.50 #ptns: 1
  491.     hash_area: 124 (max=14336) buildfrag: 1 probefrag: 1 ppasses: 1
  492.   Hash join: Resc: 6.50 Resp: 6.50 [multiMatchCost=0.00]
  493. HA Join
  494.   HA cost: 6.50
  495.      resc: 6.50 resc_io: 6.00 resc_cpu: 10588587
  496.      resp: 6.50 resp_io: 6.00 resp_cpu: 10588587
  497. Best:: JoinMethod: SortMergeAnti
  498.        Cost: 6.00 Degree: 1 Resp: 6.00 Card: 1.33 Bytes: 23
  499. ***********************
  500. Best so far: Table#: 0 cost: 2.0008 card: 4.0000 bytes: 80
  501.               Table#: 1 cost: 6.0028 card: 1.3333 bytes: 23
  502. ***********************
  503. (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

  505. *********************************
  506. Number of join permutations tried: 1
  507. *********************************
  508. Consider using bloom filter between DEPT[DEPT] and EMP[EMP] with ??
  509. kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables predicates
  510. Enumerating distribution method (advanced)
  511. --- Distribution method for
  512. join between DEPT[DEPT](serial) and EMP[EMP](serial); jm = 9; right side access path = TableScan
  513. ---- serial Sort-Merge-Join -> NONE

  515. (newjo-save) [0 1 ]
  516. Trying or-Expansion on query block SEL$5DA710D3 (#1)
  517. Transfer Optimizer annotations for query block SEL$5DA710D3 (#1)
  518. id=0 frofkksm[i] (sort-merge/hash) predicate=\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  519. id=0 frosand (sort-merge/hash) predicate=\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\"
  520. Final cost for query block SEL$5DA710D3 (#1) - All Rows Plan:
  521.   Best join order: 1
  522.   Cost: 6.0028 Degree: 1 Card: 1.0000 Bytes: 23
  523.   Resc: 6.0028 Resc_io: 5.0000 Resc_cpu: 21078977
  524.   Resp: 6.0028 Resp_io: 5.0000 Resc_cpu: 21078977
  525. kkoqbc-subheap (delete addr=0x2af4772bfaa0, in-use=24056, alloc=32840)
  526. kkoqbc-end:
  527.         :
  528.     call(in-use=17592, alloc=65656), compile(in-use=123016, alloc=131264), execution(in-use=3624, alloc=4032)

  530. kkoqbc: finish optimizing query block SEL$5DA710D3 (#1)
  531. apadrv-end
  532.           :
  533.     call(in-use=17592, alloc=65656), compile(in-use=124000, alloc=131264), execution(in-use=3624, alloc=4032)


  536. Starting SQL statement dump

  538. user_id=83 user_name=SCOTT module=SQL*Plus action=
  539. sql_id=0jfbpts9pskp8 plan_hash_value=1353548327 problem_type=3
  540. ----- Current SQL Statement for this session (sql_id=0jfbpts9pskp8) -----
  541. select /* sample 9 */ * from dept where deptno not in (select deptno from emp)
  542. sql_text_length=79
  543. sql=select /* sample 9 */ * from dept where deptno not in (select deptno from emp)
  544. ----- Explain Plan Dump -----
  545. ----- Plan Table -----
  546.  
  547. ============
  548. Plan Table
  549. ============
  550. ------------------------------------------------+--------------------------------+
  551. | Id | Operation                     | Name    | Rows | Bytes | Cost | Time     |
  552. ------------------------------------------------+--------------------------------+
  553. |  0 | SELECT STATEMENT              |         |      |       | 6    |          |
  554. |  1 |  MERGE JOIN ANTI              |         | 1    | 23    | 6    | 00:00:01 |
  555. |  2 |   TABLE ACCESS BY INDEX ROWID | DEPT    | 4    | 80    | 2    | 00:00:01 |
  556. |  3 |    INDEX FULL SCAN            | PK_DEPT | 4    |       | 1    | 00:00:01 |
  557. |  4 |   SORT UNIQUE                 |         | 14   | 42    | 4    | 00:00:01 |
  558. |  5 |    TABLE ACCESS FULL          | EMP     | 14   | 42    | 3    | 00:00:01 |
  559. ------------------------------------------------+--------------------------------+
  560. Predicate Information:
  561. ----------------------
  562. 4 - access("DEPTNO"="DEPTNO")
  563. 4 - filter("DEPTNO"="DEPTNO")
  564.  
  565. Content of other_xml column
  566. ===========================
  567.   db_version : 11.2.0.3
  568.   parse_schema : SCOTT
  569.   plan_hash : 1353548327
  570.   plan_hash_2 : 2151567395
  571.   Outline Data:
  572.   /*+
  573.     BEGIN_OUTLINE_DATA
  574.       IGNORE_OPTIM_EMBEDDED_HINTS
  575.       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
  576.       DB_VERSION('11.2.0.3')
  577.       ALL_ROWS
  578.       OUTLINE_LEAF(@\"SEL$5DA710D3\")
  579.       UNNEST(@\"SEL$2\")
  580.       OUTLINE(@\"SEL$1\")
  581.       OUTLINE(@\"SEL$2\")
  582.       INDEX(@\"SEL$5DA710D3\" \"DEPT\"@\"SEL$1\" (\"DEPT\".\"DEPTNO\"))
  583.       FULL(@\"SEL$5DA710D3\" \"EMP\"@\"SEL$2\")
  584.       LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$1" "EMP"@"SEL$2")
  585.       USE_MERGE(@"SEL$5DA710D3" "EMP"@"SEL$2")
  586.     END_OUTLINE_DATA
  587.   */
在業務建模的時候如果這個列可以為空，那麼就讓他為空，業務邏輯不允許他為空，那麼就不讓他為空，如果可以給他個預設值的話、儘量給一個預設值，別讓他為空。可能就是優化器行為導致一些結果可能有寫麻煩，比如第一個例子那就查不出結果來。執行計劃也就可能和你想象的完全不同，還有很多，比如你不等的時候，null值既不等於也不等於，null是不能比較的一個值，建模的時候null值一定要考慮清楚

下面我們將 這個引數關閉

點選(此處)摺疊或開啟

  1. alter session set "_optimizer_null_aware_antijoin" = false




先關的優化器引數
parameters：
_optimizer_null_aware_antijoin
_unnest_subquery
_optimizer_unnest_all_subqueries
比如null_ware anti-join這個功能關閉的話，一定要用_optimizer_null_aware_antijoin這個引數，一定不要用下面的那兩個，要是用那兩個引數可能帶來災難性的問題，除非你說你的應用寫的無比的好，沒有join 沒有view 不用jppd 不用view merge，那好那你可以關。bug解決後立馬把這個引數再開啟。
有些系統從低版本升級到高版本，有些客戶講高版本的一些引數關閉，來保證高板本和低版本一致，這樣絕得會沒問題，但是這樣是不對的。
Hints:這些hint會影響這個行為，但是不是為了這個行為而設定的。
No_query_transformation
unnest,no_unnest
push_subq
no_push_subq

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

相關文章