Transformation之Subquery Un-nesting（子查詢的非巢狀）SU【六】

                   把subquery merge到它的外面的query（啟發）

網上說exist要比in快之列的優化法則，但是至少oracle 10g後，這些事情oracle會去做，但是有時候不去做，所以要是能寫成exist，那麼就儘量寫成exist。

點選( 此處 )摺疊或開啟

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

2. select * from dept where deptno in ( select deptno from emp where sal > 3000 ) ;
   

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

點選( 此處 )摺疊或開啟

1. Trace file /u01/app/oracle/diag/rdbms/testdb12/TestDB12/trace/TestDB12_ora_27778 . trc
   

2. Oracle Database 11g Enterprise Edition Release 11 . 2 . . 3 . 0 - 64bit Production
   

3. With the Partitioning , Oracle Label Security , OLAP , Data Mining ,
   

4. Oracle Database Vault and Real Application Testing options
   

5. ORACLE_HOME = /u01/app/oracle/product/11 . 2 . 0/dbhome_1
   

6. System name :     Linux
   

7. Node name :     test11g
   

8. Release :     2 . 6 . 18 - 194 . el5
   

9. Version :     #1 SMP Tue Mar 16 21 : 52 : 39 EDT 2010
   

10. Machine :     x86_64
    

11. Instance name : TestDB12
    

12. Redo thread mounted by this instance : 1
    

13. Oracle process number : 32
    

14. Unix process pid : 27778 , image : oracle@test11g ( TNS V1 - V3 )
    

15. 
    

16. 
    

17. * * * 2015 - 02 - 27 03 : 28 : 42 . 034
    

18. * * * SESSION ID : ( 53 . 2993 ) 2015 - 02 - 27 03 : 28 : 42 . 034
    

19. * * * CLIENT ID : ( ) 2015 - 02 - 27 03 : 28 : 42 . 034
    

20. * * * SERVICE NAME : ( SYS$USERS ) 2015 - 02 - 27 03 : 28 : 42 . 034
    

21. * * * MODULE NAME : ( SQL * Plus ) 2015 - 02 - 27 03 : 28 : 42 . 034
    

22. * * * ACTION NAME : ( ) 2015 - 02 - 27 03 : 28 : 42 . 034
    

23.  
    

24. Registered qb : SEL$1 0x57631638 ( PARSER )
    

25. - - - - - - - - - - - - - - - - - - - - -
    

26. QUERY BLOCK SIGNATURE
    

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

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

29.     fro ( ) : flg = 4 objn = 75333 hint_alias = \ "DEPT\" @ \ "SEL$1\"
    

30. 
    

31. Registered qb : SEL$2 0x5763c120 ( PARSER )
    

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

33. QUERY BLOCK SIGNATURE
    

34. - - - - - - - - - - - - - - - - - - - - -
    

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

36.     fro ( ) : flg = 4 objn = 75335 hint_alias = \ "EMP\" @ \ "SEL$2\"
    

37. 
    

38. SPM : statement not found in SMB
    

39. 
    

40. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

41. Automatic degree of parallelism ( ADOP )
    

42. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

43. Automatic degree of parallelism is disabled : Parameter .
    

44. 
    

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

46. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

47. Predicate Move - Around ( PM )
    

48. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

49. OPTIMIZER INFORMATION
    

50. 
    

51. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

52. - - - - - Current SQL Statement for this session ( sql_id = dnkzmnvvdxgtm ) - - - - -
    

53. select * from dept where deptno in ( select deptno from emp where sal > 3000 )
    

54. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

55. Legend
    

56. The following abbreviations are used by optimizer trace .
    

57. CBQT - cost - based query transformation
    

58. JPPD - join predicate push - down
    

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

60. FPD - filter push - down
    

61. PM - predicate move - around
    

62. CVM - complex view merging
    

63. SPJ - select - project - join
    

64. SJC - set join conversion
    

65. SU - subquery unnesting
    

66. OBYE - order by elimination
    

67. OST - old style star transformation
    

68. ST - new ( cbqt ) star transformation
    

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

70. JE - Join Elimination
    

71. JF - join factorization
    

72. SLP - select list pruning
    

73. DP - distinct placement
    

74. qb - query block
    

75. LB - leaf blocks
    

76. DK - distinct keys
    

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

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

79. CLUF - clustering factor
    

80. NDV - number of distinct values
    

81. Resp - response cost
    

82. Card - cardinality
    

83. Resc - resource cost
    

84. NL - nested loops ( join )
    

85. SM - sort merge ( join )
    

86. HA - hash ( join )
    

87. CPUSPEED - CPU Speed
    

88. IOTFRSPEED - I/O transfer speed
    

89. IOSEEKTIM - I/O seek time
    

90. SREADTIM - average single block read time
    

91. MREADTIM - average multiblock read time
    

92. MBRC - average multiblock read count
    

93. MAXTHR - maximum I/O system throughput
    

94. SLAVETHR - average slave I/O throughput
    

95. dmeth - distribution method
    

96.   1 : no partitioning required
    

97.   2 : value partitioned
    

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

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

100.   8 : broadcast right and partition left
     

101.   16 : broadcast left and partition right
     

102.   32 : partition left using partitioning of right
     

103.   64 : partition right using partitioning of left
     

104.   256 : run the join in serial
     

105.   0 : invalid distribution method
     

106. sel - selectivity
     

107. ptn - partition
     

108. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

109. PARAMETERS USED BY THE OPTIMIZER
     

110. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

111.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

112.   PARAMETERS WITH ALTERED VALUES
     

113.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

114. Compilation Environment Dump
     

115. Bug Fix Control Environment
     

116. 
     

117. 
     

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

119.   PARAMETERS WITH DEFAULT VALUES
     

120.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

121. Compilation Environment Dump
     

122. optimizer_mode_hinted = false
     

123. optimizer_features_hinted = 0 . .
     

124. parallel_execution_enabled = true
     

125. parallel_query_forced_dop = 0
     

126. parallel_dml_forced_dop = 0
     

127. parallel_ddl_forced_degree = 0
     

128. parallel_ddl_forced_instances = 0
     

129. _query_rewrite_fudge = 90
     

130. optimizer_features_enable = 11 . 2 . . 3
     

131. _optimizer_search_limit = 5
     

132. cpu_count = 1
     

133. active_instance_count = 1
     

134. parallel_threads_per_cpu = 2
     

135. hash_area_size = 131072
     

136. bitmap_merge_area_size = 1048576
     

137. sort_area_size = 65536
     

138. sort_area_retained_size = 0
     

139. _sort_elimination_cost_ratio = 0
     

140. _optimizer_block_size = 8192
     

141. _sort_multiblock_read_count = 2
     

142. _hash_multiblock_io_count = 0
     

143. _db_file_optimizer_read_count = 8
     

144. _optimizer_max_permutations = 2000
     

145. pga_aggregate_target = 286720 KB
     

146. _pga_max_size = 204800 KB
     

147. _query_rewrite_maxdisjunct = 257
     

148. _smm_auto_min_io_size = 56 KB
     

149. _smm_auto_max_io_size = 248 KB
     

150. _smm_min_size = 286 KB
     

151. _smm_max_size = 57344 KB
     

152. _smm_px_max_size = 143360 KB
     

153. _cpu_to_io = 0
     

154. _optimizer_undo_cost_change = 11 . 2 . . 3
     

155. parallel_query_mode = enabled
     

156. parallel_dml_mode = disabled
     

157. parallel_ddl_mode = enabled
     

158. optimizer_mode = all_rows
     

159. sqlstat_enabled = false
     

160. _optimizer_percent_parallel = 101
     

161. _always_anti_join = choose
     

162. _always_semi_join = choose
     

163. _optimizer_mode_force = true
     

164. _partition_view_enabled = true
     

165. _always_star_transformation = false
     

166. _query_rewrite_or_error = false
     

167. _hash_join_enabled = true
     

168. cursor_sharing = exact
     

169. _b_tree_bitmap_plans = true
     

170. star_transformation_enabled = false
     

171. _optimizer_cost_model = choose
     

172. _new_sort_cost_estimate = true
     

173. _complex_view_merging = true
     

174. _unnest_subquery = true
     

175. _eliminate_common_subexpr = true
     

176. _pred_move_around = true
     

177. _convert_set_to_join = false
     

178. _push_join_predicate = true
     

179. _push_join_union_view = true
     

180. _fast_full_scan_enabled = true
     

181. _optim_enhance_nnull_detection = true
     

182. _parallel_broadcast_enabled = true
     

183. _px_broadcast_fudge_factor = 100
     

184. _ordered_nested_loop = true
     

185. _no_or_expansion = false
     

186. optimizer_index_cost_adj = 100
     

187. optimizer_index_caching = 0
     

188. _system_index_caching = 0
     

189. _disable_datalayer_sampling = false
     

190. query_rewrite_enabled = true
     

191. query_rewrite_integrity = enforced
     

192. _query_cost_rewrite = true
     

193. _query_rewrite_2 = true
     

194. _query_rewrite_1 = true
     

195. _query_rewrite_expression = true
     

196. _query_rewrite_jgmigrate = true
     

197. _query_rewrite_fpc = true
     

198. _query_rewrite_drj = true
     

199. _full_pwise_join_enabled = true
     

200. _partial_pwise_join_enabled = true
     

201. _left_nested_loops_random = true
     

202. _improved_row_length_enabled = true
     

203. _index_join_enabled = true
     

204. _enable_type_dep_selectivity = true
     

205. _improved_outerjoin_card = true
     

206. _optimizer_adjust_for_nulls = true
     

207. _optimizer_degree = 0
     

208. _use_column_stats_for_function = true
     

209. _subquery_pruning_enabled = true
     

210. _subquery_pruning_mv_enabled = false
     

211. _or_expand_nvl_predicate = true
     

212. _like_with_bind_as_equality = false
     

213. _table_scan_cost_plus_one = true
     

214. _cost_equality_semi_join = true
     

215. _default_non_equality_sel_check = true
     

216. _new_initial_join_orders = true
     

217. _oneside_colstat_for_equijoins = true
     

218. _optim_peek_user_binds = true
     

219. _minimal_stats_aggregation = true
     

220. _force_temptables_for_gsets = false
     

221. workarea_size_policy = auto
     

222. _smm_auto_cost_enabled = true
     

223. _gs_anti_semi_join_allowed = true
     

224. _optim_new_default_join_sel = true
     

225. optimizer_dynamic_sampling = 2
     

226. _pre_rewrite_push_pred = true
     

227. _optimizer_new_join_card_computation = true
     

228. _union_rewrite_for_gs = yes_gset_mvs
     

229. _generalized_pruning_enabled = true
     

230. _optim_adjust_for_part_skews = true
     

231. _force_datefold_trunc = false
     

232. statistics_level = typical
     

233. _optimizer_system_stats_usage = true
     

234. skip_unusable_indexes = true
     

235. _remove_aggr_subquery = true
     

236. _optimizer_push_down_distinct = 0
     

237. _dml_monitoring_enabled = true
     

238. _optimizer_undo_changes = false
     

239. _predicate_elimination_enabled = true
     

240. _nested_loop_fudge = 100
     

241. _project_view_columns = true
     

242. _local_communication_costing_enabled = true
     

243. _local_communication_ratio = 50
     

244. _query_rewrite_vop_cleanup = true
     

245. _slave_mapping_enabled = true
     

246. _optimizer_cost_based_transformation = linear
     

247. _optimizer_mjc_enabled = true
     

248. _right_outer_hash_enable = true
     

249. _spr_push_pred_refspr = true
     

250. _optimizer_cache_stats = false
     

251. _optimizer_cbqt_factor = 50
     

252. _optimizer_squ_bottomup = true
     

253. _fic_area_size = 131072
     

254. _optimizer_skip_scan_enabled = true
     

255. _optimizer_cost_filter_pred = false
     

256. _optimizer_sortmerge_join_enabled = true
     

257. _optimizer_join_sel_sanity_check = true
     

258. _mmv_query_rewrite_enabled = true
     

259. _bt_mmv_query_rewrite_enabled = true
     

260. _add_stale_mv_to_dependency_list = true
     

261. _distinct_view_unnesting = false
     

262. _optimizer_dim_subq_join_sel = true
     

263. _optimizer_disable_strans_sanity_checks = 0
     

264. _optimizer_compute_index_stats = true
     

265. _push_join_union_view2 = true
     

266. _optimizer_ignore_hints = false
     

267. _optimizer_random_plan = 0
     

268. _query_rewrite_setopgrw_enable = true
     

269. _optimizer_correct_sq_selectivity = true
     

270. _disable_function_based_index = false
     

271. _optimizer_join_order_control = 3
     

272. _optimizer_cartesian_enabled = true
     

273. _optimizer_starplan_enabled = true
     

274. _extended_pruning_enabled = true
     

275. _optimizer_push_pred_cost_based = true
     

276. _optimizer_null_aware_antijoin = true
     

277. _optimizer_extend_jppd_view_types = true
     

278. _sql_model_unfold_forloops = run_time
     

279. _enable_dml_lock_escalation = false
     

280. _bloom_filter_enabled = true
     

281. _update_bji_ipdml_enabled = 0
     

282. _optimizer_extended_cursor_sharing = udo
     

283. _dm_max_shared_pool_pct = 1
     

284. _optimizer_cost_hjsmj_multimatch = true
     

285. _optimizer_transitivity_retain = true
     

286. _px_pwg_enabled = true
     

287. optimizer_secure_view_merging = true
     

288. _optimizer_join_elimination_enabled = true
     

289. flashback_table_rpi = non_fbt
     

290. _optimizer_cbqt_no_size_restriction = true
     

291. _optimizer_enhanced_filter_push = true
     

292. _optimizer_filter_pred_pullup = true
     

293. _rowsrc_trace_level = 0
     

294. _simple_view_merging = true
     

295. _optimizer_rownum_pred_based_fkr = true
     

296. _optimizer_better_inlist_costing = all
     

297. _optimizer_self_induced_cache_cost = false
     

298. _optimizer_min_cache_blocks = 10
     

299. _optimizer_or_expansion = depth
     

300. _optimizer_order_by_elimination_enabled = true
     

301. _optimizer_outer_to_anti_enabled = true
     

302. _selfjoin_mv_duplicates = true
     

303. _dimension_skip_null = true
     

304. _force_rewrite_enable = false
     

305. _optimizer_star_tran_in_with_clause = true
     

306. _optimizer_complex_pred_selectivity = true
     

307. _optimizer_connect_by_cost_based = true
     

308. _gby_hash_aggregation_enabled = true
     

309. _globalindex_pnum_filter_enabled = true
     

310. _px_minus_intersect = true
     

311. _fix_control_key = 0
     

312. _force_slave_mapping_intra_part_loads = false
     

313. _force_tmp_segment_loads = false
     

314. _query_mmvrewrite_maxpreds = 10
     

315. _query_mmvrewrite_maxintervals = 5
     

316. _query_mmvrewrite_maxinlists = 5
     

317. _query_mmvrewrite_maxdmaps = 10
     

318. _query_mmvrewrite_maxcmaps = 20
     

319. _query_mmvrewrite_maxregperm = 512
     

320. _query_mmvrewrite_maxqryinlistvals = 500
     

321. _disable_parallel_conventional_load = false
     

322. _trace_virtual_columns = false
     

323. _replace_virtual_columns = true
     

324. _virtual_column_overload_allowed = true
     

325. _kdt_buffering = true
     

326. _first_k_rows_dynamic_proration = true
     

327. _optimizer_sortmerge_join_inequality = true
     

328. _optimizer_aw_stats_enabled = true
     

329. _bloom_pruning_enabled = true
     

330. result_cache_mode = MANUAL
     

331. _px_ual_serial_input = true
     

332. _optimizer_skip_scan_guess = false
     

333. _enable_row_shipping = true
     

334. _row_shipping_threshold = 80
     

335. _row_shipping_explain = false
     

336. transaction_isolation_level = read_commited
     

337. _optimizer_distinct_elimination = true
     

338. _optimizer_multi_level_push_pred = true
     

339. _optimizer_group_by_placement = true
     

340. _optimizer_rownum_bind_default = 10
     

341. _enable_query_rewrite_on_remote_objs = true
     

342. _optimizer_extended_cursor_sharing_rel = simple
     

343. _optimizer_adaptive_cursor_sharing = true
     

344. _direct_path_insert_features = 0
     

345. _optimizer_improve_selectivity = true
     

346. optimizer_use_pending_statistics = false
     

347. _optimizer_enable_density_improvements = true
     

348. _optimizer_aw_join_push_enabled = true
     

349. _optimizer_connect_by_combine_sw = true
     

350. _enable_pmo_ctas = 0
     

351. _optimizer_native_full_outer_join = force
     

352. _bloom_predicate_enabled = true
     

353. _optimizer_enable_extended_stats = true
     

354. _is_lock_table_for_ddl_wait_lock = 0
     

355. _pivot_implementation_method = choose
     

356. optimizer_capture_sql_plan_baselines = false
     

357. optimizer_use_sql_plan_baselines = true
     

358. _optimizer_star_trans_min_cost = 0
     

359. _optimizer_star_trans_min_ratio = 0
     

360. _with_subquery = OPTIMIZER
     

361. _optimizer_fkr_index_cost_bias = 10
     

362. _optimizer_use_subheap = true
     

363. parallel_degree_policy = manual
     

364. parallel_degree = 0
     

365. parallel_min_time_threshold = 10
     

366. _parallel_time_unit = 10
     

367. _optimizer_or_expansion_subheap = true
     

368. _optimizer_free_transformation_heap = true
     

369. _optimizer_reuse_cost_annotations = true
     

370. _result_cache_auto_size_threshold = 100
     

371. _result_cache_auto_time_threshold = 1000
     

372. _optimizer_nested_rollup_for_gset = 100
     

373. _nlj_batching_enabled = 1
     

374. parallel_query_default_dop = 0
     

375. is_recur_flags = 0
     

376. optimizer_use_invisible_indexes = false
     

377. flashback_data_archive_internal_cursor = 0
     

378. _optimizer_extended_stats_usage_control = 192
     

379. _parallel_syspls_obey_force = true
     

380. cell_offload_processing = true
     

381. _rdbms_internal_fplib_enabled = false
     

382. db_file_multiblock_read_count = 97
     

383. _bloom_folding_enabled = true
     

384. _mv_generalized_oj_refresh_opt = true
     

385. cell_offload_compaction = ADAPTIVE
     

386. cell_offload_plan_display = AUTO
     

387. _bloom_predicate_pushdown_to_storage = true
     

388. _bloom_vector_elements = 0
     

389. _bloom_pushing_max = 512
     

390. parallel_degree_limit = 65535
     

391. parallel_force_local = false
     

392. parallel_max_degree = 2
     

393. total_cpu_count = 1
     

394. _optimizer_coalesce_subqueries = true
     

395. _optimizer_fast_pred_transitivity = true
     

396. _optimizer_fast_access_pred_analysis = true
     

397. _optimizer_unnest_disjunctive_subq = true
     

398. _optimizer_unnest_corr_set_subq = true
     

399. _optimizer_distinct_agg_transform = true
     

400. _aggregation_optimization_settings = 0
     

401. _optimizer_connect_by_elim_dups = true
     

402. _optimizer_eliminate_filtering_join = true
     

403. _connect_by_use_union_all = true
     

404. dst_upgrade_insert_conv = true
     

405. advanced_queuing_internal_cursor = 0
     

406. _optimizer_unnest_all_subqueries = true
     

407. parallel_autodop = 0
     

408. parallel_ddldml = 0
     

409. _parallel_cluster_cache_policy = adaptive
     

410. _parallel_scalability = 50
     

411. iot_internal_cursor = 0
     

412. _optimizer_instance_count = 0
     

413. _optimizer_connect_by_cb_whr_only = false
     

414. _suppress_scn_chk_for_cqn = nosuppress_1466
     

415. _optimizer_join_factorization = true
     

416. _optimizer_use_cbqt_star_transformation = true
     

417. _optimizer_table_expansion = true
     

418. _and_pruning_enabled = true
     

419. _deferred_constant_folding_mode = DEFAULT
     

420. _optimizer_distinct_placement = true
     

421. partition_pruning_internal_cursor = 0
     

422. parallel_hinted = none
     

423. _sql_compatibility = 0
     

424. _optimizer_use_feedback = true
     

425. _optimizer_try_st_before_jppd = true
     

426. _dml_frequency_tracking = false
     

427. _optimizer_interleave_jppd = true
     

428. kkb_drop_empty_segments = 0
     

429. _px_partition_scan_enabled = true
     

430. _px_partition_scan_threshold = 64
     

431. _optimizer_false_filter_pred_pullup = true
     

432. _bloom_minmax_enabled = true
     

433. only_move_row = 0
     

434. _optimizer_enable_table_lookup_by_nl = true
     

435. deferred_segment_creation = true
     

436. _optimizer_filter_pushdown = true
     

437. _allow_level_without_connect_by = false
     

438. _max_rwgs_groupings = 8192
     

439. _optimizer_outer_join_to_inner = true
     

440. _optimizer_full_outer_join_to_outer = true
     

441. _px_numa_support_enabled = true
     

442. total_processor_group_count = 1

443. 
     

444.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

445.   PARAMETERS IN OPT_PARAM HINT
     

446.    * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

447. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

449. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

450. 
     

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

452. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

453. Query transformations ( QT )
     

454. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

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

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

460. CBQT : Validity checks passed for dnkzmnvvdxgtm .
     

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

462. * * * * * * * * * * * * * * * * * * * * * * * * *
     

463. Common Subexpression elimination ( CSE )
     

464. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

466. * * * * * * * * * * * * * * * * * * * * * * * * *
     

467. Common Subexpression elimination ( CSE )
     

468. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

472. * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

473. Order - by elimination ( OBYE )
     

474. * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

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

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

480. query block SEL$1 ( # ) unchanged
     

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

482. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

483. Query transformations ( QT )
     

484. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

486. * * * * * * * * * * * * * * * * * * * * * * * * *
     

487. Common Subexpression elimination ( CSE )
     

488. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

490. * * * * * * * * * * * * * * * * * * * * * * * * *
     

491. Common Subexpression elimination ( CSE )
     

492. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

495. query block SEL$1 ( # ) unchanged
     

496. apadrv - start sqlid = 15729934021953503027
     

497.    :
     

498.      call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 62144 , alloc = 62632 ) , execution ( in - use = 3456 , alloc = 4032 )
     

499. 
     

500. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

501. Peeked values of the binds in SQL statement
     

502. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

503. 
     

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

505. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

506. COST - BASED QUERY TRANSFORMATIONS
     

507. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

509. FPD : Current where clause predicates \ "EMP\" . \ "SAL\" > 3000
     

510. 
     

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

512. FPD : Current where clause predicates \ "DEPT\" . \ "DEPTNO\" = ANY ( SELECT \ "EMP\" . \ "DEPTNO\" FROM \ "EMP\" \ "EMP\" )
     

513. 
     

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

515. * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

516. Order - by elimination ( OBYE )
     

517. * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

520. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

521. Query transformations ( QT )
     

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

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

524. * * * * * * * * * * * * * * * * * * * * * * * * *
     

525. Common Subexpression elimination ( CSE )
     

526. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

528. * * * * * * * * * * * * * * * * * * * * * * * * *
     

529. Common Subexpression elimination ( CSE )
     

530. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

534. kkqctdrvTD - start : :
     

535.      call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 104584 , alloc = 108008 ) , execution ( in - use = 3456 , alloc = 4032 )
     

536. 
     

537. Registered qb : SEL$1 0x576c9f98 ( COPY SEL$1 )
     

538. - - - - - - - - - - - - - - - - - - - - -
     

539. QUERY BLOCK SIGNATURE
     

540. - - - - - - - - - - - - - - - - - - - - -
     

541.   signature ( ) : NULL
     

542. Registered qb : SEL$2 0x576ca878 ( COPY SEL$2 )
     

543. - - - - - - - - - - - - - - - - - - - - -
     

544. QUERY BLOCK SIGNATURE
     

545. - - - - - - - - - - - - - - - - - - - - -
     

546.   signature ( ) : NULL
     

547. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

548. Cost - Based Subquery Unnesting
     

549. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

553. Subquery unchanged .
     

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

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

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

557. SU : Passed validity checks .
     

558. SU : Transforming ANY subquery to a join .
     

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

560. - - - - - - - - - - - - - - - - - - - - -
     

561. QUERY BLOCK SIGNATURE
     

562. - - - - - - - - - - - - - - - - - - - - -
     

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

564.     fro ( ) : flg = 0 objn = 75333 hint_alias = \ "DEPT\" @ \ "SEL$1\"
     

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

566. 
     

567. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

568. Cost - Based Complex View Merging
     

569. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

573. kkqctdrvTD - cleanup : transform ( in - use = 4792 , alloc = 8392 ) :
     

574.      call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 120856 , alloc = 131696 ) , execution ( in - use = 3496 , alloc = 4032 )
     

575. 
     

576. kkqctdrvTD - end :
     

577.      call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 112680 , alloc = 131696 ) , execution ( in - use = 3496 , alloc = 4032 )
     

578. 
     

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

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

581. Subquery unchanged .
     

582. SU : Transforming ANY subquery to a join .
     

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

584. * * * * * * * * * * * * * * * * * * * * * * * * *
     

585. Set - Join Conversion ( SJC )
     

586. * * * * * * * * * * * * * * * * * * * * * * * * *
     

587. SJC : not performed
     

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

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

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

591. * * * * * * * * * * * * * * * * * * * * * * * * *
     

592. Join Elimination ( JE )
     

593. * * * * * * * * * * * * * * * * * * * * * * * * *
     

594. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

595. SELECT DEPT . DEPTNO   DEPTNO , DEPT . DNAME   DNAME , DEPT . LOC   LOC  FROM SCOTT . EMP   EMP , SCOTT . DEPT   DEPT  WHERE DEPT . DEPTNO = EMP . DEPTNO   AND EMP . SAL > 3000
     

596. JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
     

597. JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
     

598. JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
     

599. JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
     

600. JE : cfro : DEPT objn : 75335 col# : 1 dfro : EMP dcol# : 8
     

601. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

602. SELECT DEPT . DEPTNO   DEPTNO , DEPT . DNAME   DNAME , DEPT . LOC   LOC  FROM SCOTT . EMP   EMP , SCOTT . DEPT   DEPT  WHERE DEPT . DEPTNO = EMP . DEPTNO   AND EMP . SAL > 3000
     

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

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

605. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

606. Predicate Move - Around ( PM )
     

607. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

611. kkqctdrvTD - start : :
     

612.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114000 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

613. 
     

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

615.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114632 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

616. 
     

617. kkqctdrvTD - end :
     

618.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114952 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

619. 
     

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

621. kkqctdrvTD - start : :
     

622.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114952 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

623. 
     

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

625.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 115560 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

626. 
     

627. kkqctdrvTD - end :
     

628.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 115880 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

629. 
     

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

631. kkqctdrvTD - start : :
     

632.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 115880 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

633. 
     

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

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

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

637.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 116488 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

638. 
     

639. kkqctdrvTD - end :
     

640.      call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 116808 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

641. 
     

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

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

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

645. SELECT DEPT . DEPTNO   DEPTNO , DEPT . DNAME   DNAME , DEPT . LOC   LOC  FROM SCOTT . EMP   EMP , SCOTT . DEPT   DEPT  WHERE DEPT . DEPTNO = EMP . DEPTNO   AND EMP . SAL > 3000
     

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

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

648. kkqctdrvTD - start : :
     

649.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 116808 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

650. 
     

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

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

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

654.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 117416 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

655. 
     

656. kkqctdrvTD - end :
     

657.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 117736 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

658. 
     

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

660. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

661. Cost - based predicate pushdown ( JPPD )
     

662. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

664. kkqctdrvTD - start : :
     

665.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 117736 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

666. 
     

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

668.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 118344 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

669. 
     

670. kkqctdrvTD - end :
     

671.      call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 118664 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

672. 
     

673. JPPD : Applying transformation directives
     

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

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

676. DEPT . DEPTNO = EMP . DEPTNO   AND EMP . SAL > 3000
     

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

678. finally : DEPT . DEPTNO = EMP . DEPTNO   AND EMP . SAL > 3000
     

679. 
     

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

681. SELECT DEPT . DEPTNO   DEPTNO, DEPT . DNAME   DNAME , DEPT . LOC   LOC  FROM SCOTT . EMP   EMP , SCOTT . DEPT   DEPT  WHERE DEPT . DEPTNO = EMP . DEPTNO   AND EMP . SAL > 3000
     

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

683.         
     

684.          :
     

685.      call ( in - use = 3952 , alloc = 16344 ) , compile ( in - use = 119968 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

686. 
     

687. kkoqbc - subheap ( create addr = 0x2b265763a658 )
     

688. * * * * * * * * * * * * * * * *
     

689. QUERY BLOCK TEXT
     

690. * * * * * * * * * * * * * * * *
     

691. select * from dept where deptno in ( select deptno from emp where sal > 3000 )
     

692. - - - - - - - - - - - - - - - - - - - - -
     

693. QUERY BLOCK SIGNATURE
     

694. - - - - - - - - - - - - - - - - - - - - -
     

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

696.   fro ( ) : flg = 0 objn = 75333 hint_alias = \ "DEPT\" @ \ "SEL$1\"
     

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

698. 
     

699. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     

700. SYSTEM STATISTICS INFORMATION
     

701. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     

702.   Using NOWORKLOAD Stats
     

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

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

705.   IOSEEKTIM : 10 milliseconds ( default is 10 )
     

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

707. 
     

708. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

709. BASE STATISTICAL INFORMATION
     

710. * * * * * * * * * * * * * * * * * * * * * * *
     

711. Table Stats : :
     

712.   Table : DEPT  Alias : DEPT
     

713.     #Rows : 4  #Blks : 5  AvgRowLen : 20 . 00  ChainCnt : 0 . 00
     

714.   Column ( # 1 ) : DEPTNO (
     

715.     AvgLen : 3 NDV : 4 Nulls : 0 Density : 0 . 250000 Min : 10 Max : 40
     

716. Index Stats : :
     

717.   Index : PK_DEPT  Col# : 1
     

718.     LVLS : 0  #LB : 1  #DK : 4  LB/K : 1 . 00  DB/K : 1 . 00  CLUF : 1 . 00
     

719. * * * * * * * * * * * * * * * * * * * * * * *
     

720. Table Stats : :
     

721.   Table : EMP  Alias : EMP
     

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

723.   Column ( # 8 ) : DEPTNO (
     

724.     AvgLen : 3 NDV : 3 Nulls : 0 Density : 0 . 333333 Min : 10 Max : 30
     

725. Index Stats : :
     

726.   Index : PK_EMP  Col# : 1
     

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

728. Access path analysis for EMP
     

729. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

730. SINGLE TABLE ACCESS PATH
     

731.   Single Table Cardinality Estimation for EMP [ EMP ]
     

732.   Column ( # 6 ) : SAL (
     

733.     AvgLen : 4 NDV : 12 Nulls : 0 Density : 0 . 083333 Min : 800 Max : 5000
     

734.   Table : EMP  Alias : EMP
     

735.     Card : Original : 14 . 000000  Rounded : 7  Computed : 6 . 67  Non Adjusted : 6 . 67
     

736.   Access Path : TableScan
     

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

738.       Cost_io : 3 . 00  Cost_cpu : 40087
     

739.       Resp_io : 3 . 00  Resp_cpu : 40087
     

740.   Best : : AccessPath : TableScan
     

741.          Cost : 3 . 00  Degree : 1  Resp : 3 . 00  Card : 6 . 67  Bytes : 0
     

742. 
     

743. Access path analysis for DEPT
     

744. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

745. SINGLE TABLE ACCESS PATH
     

746.   Single Table Cardinality Estimation for DEPT [ DEPT ]
     

747.   Table : DEPT  Alias : DEPT
     

748.     Card : Original : 4 . 000000  Rounded : 4  Computed : 4 . 00  Non Adjusted : 4 . 00
     

749.   Access Path : TableScan
     

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

751.       Cost_io : 3 . 00  Cost_cpu : 36367
     

752.       Resp_io : 3 . 00  Resp_cpu : 36367
     

753.   Best : : AccessPath : TableScan
     

754.          Cost : 3 . 00  Degree : 1  Resp : 3 . 00  Card : 4 . 00  Bytes : 0
     

755. 
     

756. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

757. 
     

758. 
     

759. OPTIMIZER STATISTICS AND COMPUTATIONS
     

760. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

761. GENERAL PLANS
     

762. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

763. Considering cardinality - based initial join order .
     

764. Permutations for Starting Table :
     

765. Join order [ 1 ] : DEPT [ DEPT ] # 0  EMP [ EMP ] # 1
     

766. 
     

767. * * * * * * * * * * * * * * *
     

768. Now joining : EMP [ EMP ] # 1
     

769. * * * * * * * * * * * * * * *
     

770. NL Join
     

771.   Outer table : Card : 4 . 00  Cost : 3 . 00  Resp : 3 . 00  Degree : 1  Bytes : 20
     

772. Access path analysis for EMP
     

773.   Inner table : EMP  Alias : EMP
     

774.   Access Path : TableScan
     

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

776.       Cost_io : 10 . 00  Cost_cpu : 197836
     

777.       Resp_io : 10 . 00  Resp_cpu : 197836
     

778. 
     

779.   Best NL cost : 10 . 01
     

780.           resc : 10 . 01  resc_io : 10 . 00  resc_cpu : 197836
     

781.           resp : 10 . 01  resp_io : 10 . 00  resc_cpu : 197836
     

782. > > > adjusting AJ/SJ sel based on min/max ranges : jsel = min ( . 750000 , 0 . 666667 )
     

783. Semi Join Card : 2 . 666667 = outer ( 4 . 000000 ) * sel ( . 666667 )
     

784. Join Card - Rounded : 3 Computed : 2 . 67
     

785.   Outer table : DEPT  Alias : DEPT
     

786.     resc : 3 . 00  card 4 . 00  bytes : 20  deg : 1  resp : 3 . 00
     

787.   Inner table : EMP  Alias : EMP
     

788.     resc : 3 . 00  card : 6 . 67  bytes : 7  deg : 1  resp : 3 . 00
     

789.     using dmeth : 2  #groups : 1
     

790.     SORT ressource         Sort statistics
     

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

792.       Degree : 1
     

793.       Blocks to Sort : 1 Row size : 32 Total Rows : 4
     

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

795.       Total IO sort cost : 0      Total CPU sort cost : 21021466
     

796.       Total Temp space used : 0
     

797.     SORT ressource         Sort statistics
     

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

799.       Degree : 1
     

800.       Blocks to Sort : 1 Row size : 18 Total Rows : 7
     

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

802.       Total IO sort cost : 0      Total CPU sort cost : 21021991
     

803.       Total Temp space used : 0
     

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

805. SM Join
     

806.   SM cost : 8 . 00
     

807.      resc : 8 . 00 resc_io : 6 . 00 resc_cpu : 42119911
     

808.      resp : 8 . 00 resp_io : 6 . 00 resp_cpu : 42119911
     

809. SM Join ( with index on outer )
     

810.   Access Path : index ( FullScan )
     

811.     Index : PK_DEPT
     

812.     resc_io : 2 . 00  resc_cpu : 15803
     

813.     ix_sel : 1 . 000000  ix_sel_with_filters : 1 . 000000
     

814.     Cost : 2 . 00  Resp : 2 . 00  Degree : 1
     

815.   Outer table : DEPT  Alias : DEPT
     

816.     resc : 2 . 00  card 4 . 00  bytes : 20  deg : 1  resp : 2 . 00
     

817.   Inner table : EMP  Alias : EMP
     

818.     resc : 3 . 00  card : 6 . 67  bytes : 7  deg : 1  resp : 3 . 00
     

819.     using dmeth : 2  #groups : 1
     

820.     SORT ressource         Sort statistics
     

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

822.       Degree : 1
     

823.       Blocks to Sort : 1 Row size : 18 Total Rows : 7
     

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

825.       Total IO sort cost : 0      Total CPU sort cost : 21021991
     

826.       Total Temp space used : 0
     

827.   SM join : Resc : 6 . 00  Resp : 6 . 00 [ multiMatchCost = . 00 ]
     

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 : 6 . 67  bytes : 7  deg : 1  resp : 3 . 00
     

832.     using dmeth : 2  #groups : 1
     

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

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

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

836. HA Join
     

837.   HA cost : 6 . 50
     

838.      resc : 6 . 50 resc_io : 6 . 00 resc_cpu : 10588307
     

839.      resp : 6 . 50 resp_io : 6 . 00 resp_cpu : 10588307
     

840. Best : : JoinMethod : SortMergeSemi
     

841.        Cost : 6 . 00  Degree : 1  Resp : 6 . 00  Card : 2 . 67 Bytes : 27
     

842. * * * * * * * * * * * * * * * * * * * * * * *
     

843. Best so far : Table# : 0  cost : 2 . 0008  card : 4 . 0000  bytes : 80
     

844.               Table# : 1  cost : 6 . 0027  card : 2 . 6667  bytes : 81
     

845. * * * * * * * * * * * * * * * * * * * * * * *
     

846. Join order [ 2 ] : EMP [ EMP ] # 1  DEPT [ DEPT ] #
     

847.     SORT ressource         Sort statistics
     

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

849.       Degree : 1
     

850.       Blocks to Sort : 1 Row size : 18 Total Rows : 7
     

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

852.       Total IO sort cost : 0      Total CPU sort cost : 21021991
     

853.       Total Temp space used : 0
     

854. 
     

855. * * * * * * * * * * * * * * *
     

856. Now joining : DEPT [ DEPT ] #
     

857. * * * * * * * * * * * * * * *
     

858. NL Join
     

859.   Outer table : Card : 3 . 00  Cost : 4 . 00  Resp : 4 . 00  Degree : 1  Bytes : 7
     

860. Access path analysis for DEPT
     

861.   Inner table : DEPT  Alias : DEPT
     

862.   Access Path : TableScan
     

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

864.       Cost_io : 9 . 00  Cost_cpu : 21171180
     

865.       Resp_io : 9 . 00  Resp_cpu : 21171180
     

866.   Access Path : index ( UniqueScan )
     

867.     Index : PK_DEPT
     

868.     resc_io : 1 . 00  resc_cpu : 8361
     

869.     ix_sel : 0 . 250000  ix_sel_with_filters : 0 . 250000
     

870.     NL Join : Cost : 7 . 00  Resp : 7 . 00  Degree : 1
     

871.       Cost_io : 6 . 00  Cost_cpu : 21087162
     

872.       Resp_io : 6 . 00  Resp_cpu : 21087162
     

873.   Access Path : index ( AllEqUnique )
     

874.     Index : PK_DEPT
     

875.     resc_io : 1 . 00  resc_cpu : 8361
     

876.     ix_sel : 0 . 250000  ix_sel_with_filters : 0 . 250000
     

877.     NL Join : Cost : 7 . 00  Resp : 7 . 00  Degree : 1
     

878.       Cost_io : 6 . 00  Cost_cpu : 21087162
     

879.       Resp_io : 6 . 00  Resp_cpu : 21087162
     

880. 
     

881.   Best NL cost : 7 . 00
     

882.           resc : 7 . 00  resc_io : 6 . 00  resc_cpu : 21087162
     

883.           resp : 7 . 00  resp_io : 6 . 00  resc_cpu : 21087162
     

884. Join Card : 3 . 000000 = outer ( 3 . 000000 ) * inner ( 4 . 000000 ) * sel ( . 250000 )
     

885. Join Card - Rounded : 3 Computed : 3 . 00
     

886.   Outer table : EMP  Alias : EMP
     

887.     resc : 4 . 00  card 3 . 00  bytes : 7  deg : 1  resp : 4 . 00
     

888.   Inner table : DEPT  Alias : DEPT
     

889.     resc : 3 . 00  card : 4 . 00  bytes : 20  deg : 1  resp : 3 . 00
     

890.     using dmeth : 2  #groups : 1
     

891.     SORT ressource         Sort statistics
     

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

893.       Degree : 1
     

894.       Blocks to Sort : 1 Row size : 18 Total Rows : 3
     

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

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

897.       Total Temp space used : 0
     

898.     SORT ressource         Sort statistics
     

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

900.       Degree : 1
     

901.       Blocks to Sort : 1 Row size : 32 Total Rows : 4
     

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

903.       Total IO sort cost : 0      Total CPU sort cost : 21021466
     

904.       Total Temp space used : 0
     

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

906. SM Join
     

907.   SM cost : 9 . 00
     

908.      resc : 9 . 00 resc_io : 6 . 00 resc_cpu : 63141231
     

909.      resp : 9 . 00 resp_io : 6 . 00 resp_cpu : 63141231
     

910.   Outer table : EMP  Alias : EMP
     

911.     resc : 4 . 00  card 3 . 00  bytes : 7  deg : 1  resp : 4 . 00
     

912.   Inner table : DEPT  Alias : DEPT
     

913.     resc : 3 . 00  card : 4 . 00  bytes : 20  deg : 1  resp : 3 . 00
     

914.     using dmeth : 2  #groups : 1
     

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

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

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

918. HA Join
     

919.   HA cost : 7 . 50
     

920.      resc : 7 . 50 resc_io : 6 . 00 resc_cpu : 31609848
     

921.      resp : 7 . 50 resp_io : 6 . 00 resp_cpu : 31609848
     

922. Plan cardinality mismatch : best card = 2 . 66666666667   curr card = 3 . 00000000000
     

923. Join order aborted : cost > best plan cost
     

924. * * * * * * * * * * * * * * * * * * * * * * *
     

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

926. 
     

927. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

928. Number of join permutations tried : 2
     

929. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

930. Consider using bloom filter between DEPT [ DEPT ] and EMP [ EMP ] with ? ?
     

931. kkoBloomFilter : join ( lcdn : 4 rcdn : 7 jcdn : 3 limit : 13 )
     

932. Computing bloom ndv for creator : DEPT [ DEPT ] ccdn : 4 . 0 and user : EMP [ EMP ] ucdn : 6 . 7
     

933. kkopqComputeBloomNdv : predicate ( bndv : 4 ndv : 4 ) and ( bndv : 3 ndv : 3 )
     

934. kkopqComputeBloomNdv : pred cnt : 2 ndv : 4 reduction : 1
     

935. kkoBloomFilter : join ndv : 0 reduction : . 600000 ( limit : . 500000 ) rejected because distinct value ratio
     

936. Enumerating distribution method ( advanced )
     

937. - - - Distribution method for
     

938. join between DEPT [ DEPT ] ( serial ) and EMP [ EMP ] ( serial ) ; jm = 8 ; right side access path = TableScan
     

939. - - - - serial Sort - Merge - Join - > NONE
     

940. 
     

941. ( newjo - save ) [ 0 1 ]
     

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

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

944. id = 0 frofkksm [ i ] ( sort - merge/hash ) predicate = DEPT . DEPTNO = EMP . DEPTNO
     

945. id = 0 frosand ( sort - merge/hash ) predicate = DEPT . DEPTNO = EMP . DEPTNO
     

946. id = 0 frofand predicate = EMP . SAL > 3000
     

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

948.   Best join order : 1
     

949.   Cost : 6 . 0027  Degree : 1  Card : 3 . 0000  Bytes : 81
     

950.   Resc : 6 . 0027  Resc_io : 5 . 0000  Resc_cpu : 21077881
     

951.   Resp : 6 . 0027  Resp_io : 5 . 0000  Resc_cpu : 21077881
     

952. kkoqbc - subheap ( delete addr = 0x2b265763a658 , in - use = 26368 , alloc = 32840 )
     

953. kkoqbc - end :
     

954.          :
     

955.      call ( in - use = 19256 , alloc = 65656 ) , compile ( in - use = 123008 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

956. 
     

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

958. apadrv - end
     

959.            :
     

960.      call ( in - use = 19256 , alloc = 65656 ) , compile ( in - use = 124008 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
     

961. 
     

962. 
     

963. Starting SQL statement dump
     

964. 
     

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

966. sql_id = dnkzmnvvdxgtm plan_hash_value = 1090737117 problem_type = 3
     

967. - - - - - Current SQL Statement for this session ( sql_id = dnkzmnvvdxgtm ) - - - - -
     

968. select * from dept where deptno in ( select deptno from emp where sal > 3000 )
     

969. sql_text_length = 77
     

970. sql = select * from dept where deptno in ( select deptno from emp where sal > 3000 )
     

971. - - - - - Explain Plan Dump - - - - -
     

972. - - - - - Plan Table - - - - -
     

973.  
     

974. = = = = = = = = = = = =
     

975. Plan Table
     

976. = = = = = = = = = = = =
     

977. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

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

979. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

980. | 0    | SELECT STATEMENT               |           |       |         |       6 |            |
     

981. | 1   |   MERGE JOIN SEMI               |           |      3 |    81 |       6 | 00 : 00 : 01 |
     

982. | 2    |    TABLE ACCESS BY INDEX ROWID | DEPT     |      4 |     80 |       2 | 00 : 00 : 01 |
     

983. | 3    |     INDEX FULL SCAN             | PK_DEPT |      4 |         |       1 | 00 : 00 : 01 |
     

984. | 4    |   SORT UNIQUE                  |           |      7 |    49 |       4 | 00 : 00 : 01 |
     

985. | 5    |     TABLE ACCESS FULL           | EMP      |      7 |     49 |       3 | 00 : 00 : 01 |
     

986. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

987. Predicate Information :
     

988. - - - - - - - - - - - - - - - - - - - - - -
     

989. 4 - access ( "DEPTNO" = "DEPTNO" )     ---變成了join 條件就是它
     

990. 4 - filter ( "DEPTNO" = "DEPTNO" )
     

991. 5 - filter ( "SAL" > 3000 )
     

992.  
     

993. Content of other_xml column
     

994. = = = = = = = = = = = = = = = = = = = = = = = = = = =
     

995.   db_version : 11 . 2 . . 3
     

996.   parse_schema : SCOTT
     

997.   plan_hash : 1090737117
     

998.   plan_hash_2 : 1551854233
     

999.   Outline Data :
     

1000.   / * +
      

1001.     BEGIN_OUTLINE_DATA
      

1002.       IGNORE_OPTIM_EMBEDDED_HINTS
      

1003.       OPTIMIZER_FEATURES_ENABLE ( \ '11.2.0.3\' )
      

1004.       DB_VERSION ( \ '11.2.0.3\' )
      

1005.       ALL_ROWS
      

1006.       OUTLINE_LEAF ( @ \ "SEL$5DA710D3\" )
      

1007.       UNNEST ( @ \ "SEL$2\" )
      

1008.       OUTLINE ( @ \ "SEL$1\" )
      

1009.       OUTLINE ( @ \ "SEL$2\" )
      

1010.       INDEX ( @ \ "SEL$5DA710D3\" \ "DEPT\" @ \ "SEL$1\" ( \ "DEPT\" . \ "DEPTNO\" ) )
      

1011.       FULL ( @ \ "SEL$5DA710D3\" \ "EMP\" @ \ "SEL$2\" )
      

1012.       LEADING ( @ \ "SEL$5DA710D3\" \ "DEPT\" @ \ "SEL$1\" \ "EMP\" @ \ "SEL$2\" )
      

1013.       USE_MERGE ( @ \ "SEL$5DA710D3\" \ "EMP\" @ \ "SEL$2\" )
      

1014.     END_OUTLINE_DATA
      

1015.    * /
      

1016. 
      

1. JF  -  join factorization：join 共同的項提取出來，沒必要的項刪掉。

2. merge join 就是將兩個結果集排序，主要成本排序。

3. semi join 半連結，跟exist演算法一樣。當做merge但一個結果集存在另一個結果集，我們就把這個值提出來。如果有就提取，沒有就不要。

這就是semi和inner join的區別，如果是inner join 那麼挑選出來的是所有的（3，3，4，5，5，6）， 邏輯上是不一樣的，semi是個邏輯概念。這也是為什麼semi join 半連結比inner join、outer join快的原因。
anti join正好是反過來的

使用in和exists的等價查詢
相關引數
_unnest_subquery  關這些引數一般都是測試中
_optimizer_unnest_all_subqueries
_optimizer_squ_bottomup

相關hints
NO_QUERY_TRANSFORMATION   hints的作用就是不解套
unnest
no_unnest
push_subq no_push_subq
qb_name

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