Transformation之(Out-join消除)OJE【七】

                   out join消除，oracle做这些transform其实是基于一定的条件才会去做的，不是说任何情况都会去做transform。
有时候外连接是没什么意思的，oracle会给你消除掉，如下用外连接完全没有意义，d.deptno是主键，没有意思做外连接，oracle会知道你做外连接和做内连接得到的结果是一样的，那么oracle就不做out join。

点击( 此处 )折叠或打开

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

2. select / * sample 7 * / e . ename , d . dname
   

3. from emp e , dept d
   

4. where e . deptno = d . deptno ( + )
   

5. and d . deptno > 20 ;
   

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

点击( 此处 )折叠或打开

1. * * * 2015 - 02 - 27 22 : 11 : 47 . 017
   

2. * * * SESSION ID : ( 56 . 625 ) 2015 - 02 - 27 22 : 11 : 47 . 017
   

3. * * * CLIENT ID : ( ) 2015 - 02 - 27 22 : 11 : 47 . 017
   

4. * * * SERVICE NAME : ( SYS$USERS ) 2015 - 02 - 27 22 : 11 : 47 . 017
   

5. * * * MODULE NAME : ( SQL * Plus ) 2015 - 02 - 27 22 : 11 : 47 . 017
   

6. * * * ACTION NAME : ( ) 2015 - 02 - 27 22 : 11 : 47 . 017
   

7.  
   

8. Registered qb : SEL$1 0x96666678 ( PARSER )
   

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

10. QUERY BLOCK SIGNATURE
    

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

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

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

14.     fro ( 1 ) : flg = 4 objn = 75335 hint_alias = \ "E\" @ \ "SEL$1\"
    

15. 
    

16. SPM : statement not found in SMB
    

17. 
    

18. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

19. Automatic degree of parallelism ( ADOP )
    

20. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

21. Automatic degree of parallelism is disabled : Parameter .
    

22. 
    

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

24. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

25. Predicate Move - Around ( PM )
    

26. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

27. OPTIMIZER INFORMATION
    

28. 
    

29. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

30. - - - - - Current SQL Statement for this session ( sql_id = 36844nrb44f07 ) - - - - -
    

31. select / * sample 7 * / e . ename , d . dname
    

32. from emp e , dept d
    

33. where e . deptno = d . deptno ( + )
    

34. and d . deptno > 20
    

35. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

36. Legend
    

37. The following abbreviations are used by optimizer trace .
    

38. CBQT - cost - based query transformation
    

39. JPPD - join predicate push - down
    

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

41. FPD - filter push - down
    

42. PM - predicate move - around
    

43. CVM - complex view merging
    

44. SPJ - select - project - join
    

45. SJC - set join conversion
    

46. SU - subquery unnesting
    

47. OBYE - order by elimination
    

48. OST - old style star transformation
    

49. ST - new ( cbqt ) star transformation
    

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

51. JE - Join Elimination
    

52. JF - join factorization
    

53. SLP - select list pruning
    

54. DP - distinct placement
    

55. qb - query block
    

56. LB - leaf blocks
    

57. DK - distinct keys
    

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

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

60. CLUF - clustering factor
    

61. NDV - number of distinct values
    

62. Resp - response cost
    

63. Card - cardinality
    

64. Resc - resource cost
    

65. NL - nested loops ( join )
    

66. SM - sort merge ( join )
    

67. HA - hash ( join )
    

68. CPUSPEED - CPU Speed
    

69. IOTFRSPEED - I/O transfer speed
    

70. IOSEEKTIM - I/O seek time
    

71. SREADTIM - average single block read time
    

72. MREADTIM - average multiblock read time
    

73. MBRC - average multiblock read count
    

74. MAXTHR - maximum I/O system throughput
    

75. SLAVETHR - average slave I/O throughput
    

76. dmeth - distribution method
    

77.   1 : no partitioning required
    

78.   2 : value partitioned
    

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

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

81.   8 : broadcast right and partition left
    

82.   16 : broadcast left and partition right
    

83.   32 : partition left using partitioning of right
    

84.   64 : partition right using partitioning of left
    

85.   256 : run the join in serial
    

86.   0 : invalid distribution method
    

87. sel - selectivity
    

88. ptn - partition
    

89. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

90. PARAMETERS USED BY THE OPTIMIZER
    

91. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

92.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

93.   PARAMETERS WITH ALTERED VALUES
    

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

95. Compilation Environment Dump
    

96. Bug Fix Control Environment
    

97. 
    

98. 
    

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

100.   PARAMETERS WITH DEFAULT VALUES
     

101.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

102. Compilation Environment Dump
     

103. optimizer_mode_hinted = false
     

104. optimizer_features_hinted = 0 . .
     

105. parallel_execution_enabled = true
     

106. parallel_query_forced_dop = 0
     

107. parallel_dml_forced_dop = 0
     

108. parallel_ddl_forced_degree = 0
     

109. parallel_ddl_forced_instances = 0
     

110. _query_rewrite_fudge = 90
     

111. optimizer_features_enable = 11 . 2 . . 3
     

112. _optimizer_search_limit = 5
     

113. cpu_count = 1
     

114. active_instance_count = 1
     

115. parallel_threads_per_cpu = 2
     

116. hash_area_size = 131072
     

117. bitmap_merge_area_size = 1048576
     

118. sort_area_size = 65536
     

119. sort_area_retained_size = 0
     

120. _sort_elimination_cost_ratio = 0
     

121. _optimizer_block_size = 8192
     

122. _sort_multiblock_read_count = 2
     

123. _hash_multiblock_io_count = 0
     

124. _db_file_optimizer_read_count = 8
     

125. _optimizer_max_permutations = 2000
     

126. pga_aggregate_target = 286720 KB
     

127. _pga_max_size = 204800 KB
     

128. _query_rewrite_maxdisjunct = 257
     

129. _smm_auto_min_io_size = 56 KB
     

130. _smm_auto_max_io_size = 248 KB
     

131. _smm_min_size = 286 KB
     

132. _smm_max_size = 57344 KB
     

133. _smm_px_max_size = 143360 KB
     

134. _cpu_to_io = 0
     

135. _optimizer_undo_cost_change = 11 . 2 . . 3
     

136. parallel_query_mode = enabled
     

137. parallel_dml_mode = disabled
     

138. parallel_ddl_mode = enabled
     

139. optimizer_mode = all_rows
     

140. sqlstat_enabled = false
     

141. _optimizer_percent_parallel = 101
     

142. _always_anti_join = choose
     

143. _always_semi_join = choose
     

144. _optimizer_mode_force = true
     

145. _partition_view_enabled = true
     

146. _always_star_transformation = false
     

147. _query_rewrite_or_error = false
     

148. _hash_join_enabled = true
     

149. cursor_sharing = exact
     

150. _b_tree_bitmap_plans = true
     

151. star_transformation_enabled = false
     

152. _optimizer_cost_model = choose
     

153. _new_sort_cost_estimate = true
     

154. _complex_view_merging = true
     

155. _unnest_subquery = true
     

156. _eliminate_common_subexpr = true
     

157. _pred_move_around = true
     

158. _convert_set_to_join = false
     

159. _push_join_predicate = true
     

160. _push_join_union_view = true
     

161. _fast_full_scan_enabled = true
     

162. _optim_enhance_nnull_detection = true
     

163. _parallel_broadcast_enabled = true
     

164. _px_broadcast_fudge_factor = 100
     

165. _ordered_nested_loop = true
     

166. _no_or_expansion = false
     

167. optimizer_index_cost_adj = 100
     

168. optimizer_index_caching = 0
     

169. _system_index_caching = 0
     

170. _disable_datalayer_sampling = false
     

171. query_rewrite_enabled = true
     

172. query_rewrite_integrity = enforced
     

173. _query_cost_rewrite = true
     

174. _query_rewrite_2 = true
     

175. _query_rewrite_1 = true
     

176. _query_rewrite_expression = true
     

177. _query_rewrite_jgmigrate = true
     

178. _query_rewrite_fpc = true
     

179. _query_rewrite_drj = true
     

180. _full_pwise_join_enabled = true
     

181. _partial_pwise_join_enabled = true
     

182. _left_nested_loops_random = true
     

183. _improved_row_length_enabled = true
     

184. _index_join_enabled = true
     

185. _enable_type_dep_selectivity = true
     

186. _improved_outerjoin_card = true
     

187. _optimizer_adjust_for_nulls = true
     

188. _optimizer_degree = 0
     

189. _use_column_stats_for_function = true
     

190. _subquery_pruning_enabled = true
     

191. _subquery_pruning_mv_enabled = false
     

192. _or_expand_nvl_predicate = true
     

193. _like_with_bind_as_equality = false
     

194. _table_scan_cost_plus_one = true
     

195. _cost_equality_semi_join = true
     

196. _default_non_equality_sel_check = true
     

197. _new_initial_join_orders = true
     

198. _oneside_colstat_for_equijoins = true
     

199. _optim_peek_user_binds = true
     

200. _minimal_stats_aggregation = true
     

201. _force_temptables_for_gsets = false
     

202. workarea_size_policy = auto
     

203. _smm_auto_cost_enabled = true
     

204. _gs_anti_semi_join_allowed = true
     

205. _optim_new_default_join_sel = true
     

206. optimizer_dynamic_sampling = 2
     

207. _pre_rewrite_push_pred = true
     

208. _optimizer_new_join_card_computation = true
     

209. _union_rewrite_for_gs = yes_gset_mvs
     

210. _generalized_pruning_enabled = true
     

211. _optim_adjust_for_part_skews = true
     

212. _force_datefold_trunc = false
     

213. statistics_level = typical
     

214. _optimizer_system_stats_usage = true
     

215. skip_unusable_indexes = true
     

216. _remove_aggr_subquery = true
     

217. _optimizer_push_down_distinct = 0
     

218. _dml_monitoring_enabled = true
     

219. _optimizer_undo_changes = false
     

220. _predicate_elimination_enabled = true
     

221. _nested_loop_fudge = 100
     

222. _project_view_columns = true
     

223. _local_communication_costing_enabled = true
     

224. _local_communication_ratio = 50
     

225. _query_rewrite_vop_cleanup = true
     

226. _slave_mapping_enabled = true
     

227. _optimizer_cost_based_transformation = linear
     

228. _optimizer_mjc_enabled = true
     

229. _right_outer_hash_enable = true
     

230. _spr_push_pred_refspr = true
     

231. _optimizer_cache_stats = false
     

232. _optimizer_cbqt_factor = 50
     

233. _optimizer_squ_bottomup = true
     

234. _fic_area_size = 131072
     

235. _optimizer_skip_scan_enabled = true
     

236. _optimizer_cost_filter_pred = false
     

237. _optimizer_sortmerge_join_enabled = true
     

238. _optimizer_join_sel_sanity_check = true
     

239. _mmv_query_rewrite_enabled = true
     

240. _bt_mmv_query_rewrite_enabled = true
     

241. _add_stale_mv_to_dependency_list = true
     

242. _distinct_view_unnesting = false
     

243. _optimizer_dim_subq_join_sel = true
     

244. _optimizer_disable_strans_sanity_checks = 0
     

245. _optimizer_compute_index_stats = true
     

246. _push_join_union_view2 = true
     

247. _optimizer_ignore_hints = false
     

248. _optimizer_random_plan = 0
     

249. _query_rewrite_setopgrw_enable = true
     

250. _optimizer_correct_sq_selectivity = true
     

251. _disable_function_based_index = false
     

252. _optimizer_join_order_control = 3
     

253. _optimizer_cartesian_enabled = true
     

254. _optimizer_starplan_enabled = true
     

255. _extended_pruning_enabled = true
     

256. _optimizer_push_pred_cost_based = true
     

257. _optimizer_null_aware_antijoin = true
     

258. _optimizer_extend_jppd_view_types = true
     

259. _sql_model_unfold_forloops = run_time
     

260. _enable_dml_lock_escalation = false
     

261. _bloom_filter_enabled = true
     

262. _update_bji_ipdml_enabled = 0
     

263. _optimizer_extended_cursor_sharing = udo
     

264. _dm_max_shared_pool_pct = 1
     

265. _optimizer_cost_hjsmj_multimatch = true
     

266. _optimizer_transitivity_retain = true
     

267. _px_pwg_enabled = true
     

268. optimizer_secure_view_merging = true
     

269. _optimizer_join_elimination_enabled = true
     

270. flashback_table_rpi = non_fbt
     

271. _optimizer_cbqt_no_size_restriction = true
     

272. _optimizer_enhanced_filter_push = true
     

273. _optimizer_filter_pred_pullup = true
     

274. _rowsrc_trace_level = 0
     

275. _simple_view_merging = true
     

276. _optimizer_rownum_pred_based_fkr = true
     

277. _optimizer_better_inlist_costing = all
     

278. _optimizer_self_induced_cache_cost = false
     

279. _optimizer_min_cache_blocks = 10
     

280. _optimizer_or_expansion = depth
     

281. _optimizer_order_by_elimination_enabled = true
     

282. _optimizer_outer_to_anti_enabled = true
     

283. _selfjoin_mv_duplicates = true
     

284. _dimension_skip_null = true
     

285. _force_rewrite_enable = false
     

286. _optimizer_star_tran_in_with_clause = true
     

287. _optimizer_complex_pred_selectivity = true
     

288. _optimizer_connect_by_cost_based = true
     

289. _gby_hash_aggregation_enabled = true
     

290. _globalindex_pnum_filter_enabled = true
     

291. _px_minus_intersect = true
     

292. _fix_control_key = 0
     

293. _force_slave_mapping_intra_part_loads = false
     

294. _force_tmp_segment_loads = false
     

295. _query_mmvrewrite_maxpreds = 10
     

296. _query_mmvrewrite_maxintervals = 5
     

297. _query_mmvrewrite_maxinlists = 5
     

298. _query_mmvrewrite_maxdmaps = 10
     

299. _query_mmvrewrite_maxcmaps = 20
     

300. _query_mmvrewrite_maxregperm = 512
     

301. _query_mmvrewrite_maxqryinlistvals = 500
     

302. _disable_parallel_conventional_load = false
     

303. _trace_virtual_columns = false
     

304. _replace_virtual_columns = true
     

305. _virtual_column_overload_allowed = true
     

306. _kdt_buffering = true
     

307. _first_k_rows_dynamic_proration = true
     

308. _optimizer_sortmerge_join_inequality = true
     

309. _optimizer_aw_stats_enabled = true
     

310. _bloom_pruning_enabled = true
     

311. result_cache_mode = MANUAL
     

312. _px_ual_serial_input = true
     

313. _optimizer_skip_scan_guess = false
     

314. _enable_row_shipping = true
     

315. _row_shipping_threshold = 80
     

316. _row_shipping_explain = false
     

317. transaction_isolation_level = read_commited
     

318. _optimizer_distinct_elimination = true
     

319. _optimizer_multi_level_push_pred = true
     

320. _optimizer_group_by_placement = true
     

321. _optimizer_rownum_bind_default = 10
     

322. _enable_query_rewrite_on_remote_objs = true
     

323. _optimizer_extended_cursor_sharing_rel = simple
     

324. _optimizer_adaptive_cursor_sharing = true
     

325. _direct_path_insert_features = 0
     

326. _optimizer_improve_selectivity = true
     

327. optimizer_use_pending_statistics = false
     

328. _optimizer_enable_density_improvements = true
     

329. _optimizer_aw_join_push_enabled = true
     

330. _optimizer_connect_by_combine_sw = true
     

331. _enable_pmo_ctas = 0
     

332. _optimizer_native_full_outer_join = force
     

333. _bloom_predicate_enabled = true
     

334. _optimizer_enable_extended_stats = true
     

335. _is_lock_table_for_ddl_wait_lock = 0
     

336. _pivot_implementation_method = choose
     

337. optimizer_capture_sql_plan_baselines = false
     

338. optimizer_use_sql_plan_baselines = true
     

339. _optimizer_star_trans_min_cost = 0
     

340. _optimizer_star_trans_min_ratio = 0
     

341. _with_subquery = OPTIMIZER
     

342. _optimizer_fkr_index_cost_bias = 10
     

343. _optimizer_use_subheap = true
     

344. parallel_degree_policy = manual
     

345. parallel_degree = 0
     

346. parallel_min_time_threshold = 10
     

347. _parallel_time_unit = 10
     

348. _optimizer_or_expansion_subheap = true
     

349. _optimizer_free_transformation_heap = true
     

350. _optimizer_reuse_cost_annotations = true
     

351. _result_cache_auto_size_threshold = 100
     

352. _result_cache_auto_time_threshold = 1000
     

353. _optimizer_nested_rollup_for_gset = 100
     

354. _nlj_batching_enabled = 1
     

355. parallel_query_default_dop = 0
     

356. is_recur_flags = 0
     

357. optimizer_use_invisible_indexes = false
     

358. flashback_data_archive_internal_cursor = 0
     

359. _optimizer_extended_stats_usage_control = 192
     

360. _parallel_syspls_obey_force = true
     

361. cell_offload_processing = true
     

362. _rdbms_internal_fplib_enabled = false
     

363. db_file_multiblock_read_count = 97
     

364. _bloom_folding_enabled = true
     

365. _mv_generalized_oj_refresh_opt = true
     

366. cell_offload_compaction = ADAPTIVE
     

367. cell_offload_plan_display = AUTO
     

368. _bloom_predicate_pushdown_to_storage = true
     

369. _bloom_vector_elements = 0
     

370. _bloom_pushing_max = 512
     

371. parallel_degree_limit = 65535
     

372. parallel_force_local = false
     

373. parallel_max_degree = 2
     

374. total_cpu_count = 1
     

375. _optimizer_coalesce_subqueries = true
     

376. _optimizer_fast_pred_transitivity = true
     

377. _optimizer_fast_access_pred_analysis = true
     

378. _optimizer_unnest_disjunctive_subq = true
     

379. _optimizer_unnest_corr_set_subq = true
     

380. _optimizer_distinct_agg_transform = true
     

381. _aggregation_optimization_settings = 0
     

382. _optimizer_connect_by_elim_dups = true
     

383. _optimizer_eliminate_filtering_join = true
     

384. _connect_by_use_union_all = true
     

385. dst_upgrade_insert_conv = true
     

386. advanced_queuing_internal_cursor = 0
     

387. _optimizer_unnest_all_subqueries = true
     

388. parallel_autodop = 0
     

389. parallel_ddldml = 0
     

390. _parallel_cluster_cache_policy = adaptive
     

391. _parallel_scalability = 50
     

392. iot_internal_cursor = 0
     

393. _optimizer_instance_count = 0
     

394. _optimizer_connect_by_cb_whr_only = false
     

395. _suppress_scn_chk_for_cqn = nosuppress_1466
     

396. _optimizer_join_factorization = true
     

397. _optimizer_use_cbqt_star_transformation = true
     

398. _optimizer_table_expansion = true
     

399. _and_pruning_enabled = true
     

400. _deferred_constant_folding_mode = DEFAULT
     

401. _optimizer_distinct_placement = true
     

402. partition_pruning_internal_cursor = 0
     

403. parallel_hinted = none
     

404. _sql_compatibility = 0
     

405. _optimizer_use_feedback = true
     

406. _optimizer_try_st_before_jppd = true
     

407. _dml_frequency_tracking = false
     

408. _optimizer_interleave_jppd = true
     

409. kkb_drop_empty_segments = 0
     

410. _px_partition_scan_enabled = true
     

411. _px_partition_scan_threshold = 64
     

412. _optimizer_false_filter_pred_pullup = true
     

413. _bloom_minmax_enabled = true
     

414. only_move_row = 0
     

415. _optimizer_enable_table_lookup_by_nl = true
     

416. deferred_segment_creation = true
     

417. _optimizer_filter_pushdown = true
     

418. _allow_level_without_connect_by = false
     

419. _max_rwgs_groupings = 8192
     

420. _optimizer_outer_join_to_inner = true
     

421. _optimizer_full_outer_join_to_outer = true
     

422. _px_numa_support_enabled = true
     

423. total_processor_group_count = 1
     

424. Bug Fix Control Environment
     

425.     fix  3834770 = 1
     

426. 
     

427. 
     

428.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

429.   PARAMETERS IN OPT_PARAM HINT
     

430.    * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

431. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

433. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

434. 
     

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

436. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

437. Query transformations ( QT )
     

438. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

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

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

444. CBQT bypassed for query block SEL$1 ( # ) : no complex view , sub - queries or UNION ( ALL ) queries .
     

445. CBQT : Validity checks failed for 36844nrb44f07 .
     

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

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

448. Common Subexpression elimination ( CSE )
     

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

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

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

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

453. Order - by elimination ( OBYE )
     

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

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

456. JE : Considering Join Elimination on query block SEL$1 ( # )
     

457. * * * * * * * * * * * * * * * * * * * * * * * * *
     

458. Join Elimination ( JE )
     

459. * * * * * * * * * * * * * * * * * * * * * * * * *
     

460. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

461. SELECT E . ENAME   ENAME , D . DNAME   DNAME  FROM SCOTT . EMP   E , SCOTT . DEPT   D  WHERE E . DEPTNO = D . DEPTNO ( + ) AND D . DEPTNO > 20
     

462. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

463. SELECT E . ENAME   ENAME , D . DNAME   DNAME  FROM SCOTT . EMP   E , SCOTT . DEPT   D  WHERE E . DEPTNO = D . DEPTNO ( + ) AND D . DEPTNO > 20
     

464. Query block SEL$1 ( # ) unchanged
     

465. CVM : Considering view merge in query block SEL$1 ( # )
     

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

467. OJE : Considering outer - join elimination on query block SEL$1 ( # )
     

468. OJE : considering predicate E . DEPTNO = D . DEPTNO ( + )
     

469. 
     

470. rejected
     

471. OJE : considering predicate D . DEPTNO > 20
     

472. 
     

473. OJE : Converting outer join of DEPT and EMP to inner - join .
     

474. considered
     

475. Registered qb : SEL$AF7D52CD 0x96666678 ( OUTER - JOIN REMOVED FROM QUERY BLOCK SEL$1 ; SEL$1 ; \ "D\" @ \ "SEL$1\" )
     

476. - - - - - - - - - - - - - - - - - - - - -
     

477. QUERY BLOCK SIGNATURE
     

478. - - - - - - - - - - - - - - - - - - - - -
     

479.   signature ( ) : qb_name = SEL$AF7D52CD nbfros = 2 flg =
     

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

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

482. 
     

483. OJE : outer - join eliminated
     

484. OJE : End : finding best directive for query block SEL$AF7D52CD ( # )
     

485. JE : Considering Join Elimination on query block SEL$AF7D52CD ( # )
     

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

487. Join Elimination ( JE )
     

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

489. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

490. SELECT \ "E\" . \ "ENAME\" \ "ENAME\" , \ "D\" . \ "DNAME\" \ "DNAME\" FROM \ "SCOTT\" . \ "EMP\" \ "E\" , \ "SCOTT\" . \ "DEPT\" \ "D\" WHERE \ "E\" . \ "DEPTNO\" = \ "D\" . \ "DEPTNO\" AND \ "D\" . \ "DEPTNO\" > 20
     

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

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

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

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

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

496. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

497. SELECT \ "E\" . \ "ENAME\" \ "ENAME\" , \ "D\" . \ "DNAME\" \ "DNAME\" FROM \ "SCOTT\" . \ "EMP\" \ "E\" , \ "SCOTT\" . \ "DEPT\" \ "D\" WHERE \ "E\" . \ "DEPTNO\" = \ "D\" . \ "DEPTNO\" AND \ "D\" . \ "DEPTNO\" > 20
     

498. Query block SEL$AF7D52CD ( # ) unchanged
     

499. query block SEL$1 transformed to SEL$AF7D52CD ( # )
     

500. Considering Query Transformations on query block SEL$AF7D52CD ( # )
     

501. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

502. Query transformations ( QT )
     

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

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

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

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

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

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

509. CBQT bypassed for query block SEL$AF7D52CD ( # ) : no complex view , sub - queries or UNION ( ALL ) queries .
     

510. CBQT : Validity checks failed for 36844nrb44f07 .
     

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

512. * * * * * * * * * * * * * * * * * * * * * * * * *
     

513. Common Subexpression elimination ( CSE )
     

514. * * * * * * * * * * * * * * * * * * * * * * * * *
     

515. CSE : CSE not performed on query block SEL$AF7D52CD ( # ) .
     

516. SU : Considering subquery unnesting in query block SEL$AF7D52CD ( # )
     

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

518. Subquery Unnest ( SU )
     

519. * * * * * * * * * * * * * * * * * * * *
     

520. SJC : Considering set - join conversion in query block SEL$AF7D52CD ( # )
     

521. * * * * * * * * * * * * * * * * * * * * * * * * *
     

522. Set - Join Conversion ( SJC )
     

523. * * * * * * * * * * * * * * * * * * * * * * * * *
     

524. SJC : not performed
     

525. JE : Considering Join Elimination on query block SEL$AF7D52CD ( # )
     

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

527. Join Elimination ( JE )
     

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

529. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

530. SELECT E . ENAME   ENAME , D . DNAME   DNAME  FROM SCOTT . EMP   E , SCOTT . DEPT   D  WHERE E . DEPTNO = D . DEPTNO   AND D . DEPTNO > 20
     

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

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

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

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

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

536. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

537. SELECT E . ENAME   ENAME , D . DNAME   DNAME  FROM SCOTT . EMP   E , SCOTT . DEPT   D  WHERE E . DEPTNO = D . DEPTNO   AND D . DEPTNO > 20
     

538. Query block SEL$AF7D52CD ( # ) unchanged
     

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

540. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

541. Predicate Move - Around ( PM )
     

542. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

545. query block SEL$AF7D52CD ( # ) unchanged
     

546. FPD : Considering simple filter push in query block SEL$AF7D52CD ( # )
     

547. E . DEPTNO = D . DEPTNO   AND D . DEPTNO > 20
     

548. try to generate transitive predicate from check constraints for query block SEL$AF7D52CD ( # )
     

549. finally : E . DEPTNO = D . DEPTNO   AND D . DEPTNO > 20 AND E . DEPTNO > 20
     

550. 
     

551. FPD : transitive predicates are generated in query block SEL$AF7D52CD ( # )
     

552. E . DEPTNO = D . DEPTNO   AND D . DEPTNO > 20 AND E . DEPTNO > 20
     

553. apadrv - start sqlid = 3684090342988199943
     

554.    :
     

555.      call ( in - use = 2632 , alloc = 16344 ) , compile ( in - use = 62416 , alloc = 62776 ) , execution ( in - use = 3520 , alloc = 4032 )
     

556. 
     

557. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

558. Peeked values of the binds in SQL statement
     

559. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

560. 
     

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

562. SELECT E . ENAME   ENAME , D . DNAME   DNAME  FROM EMP   E , DEPT   D  WHERE E . DEPTNO = D . DEPTNO   AND D . DEPTNO > 20 AND  E . DEPTNO > 20
     

563. kkoqbc : optimizing query block SEL$AF7D52CD ( # )
     

564.         
     

565.          :
     

566.      call ( in - use = 2784 , alloc = 16344 ) , compile ( in - use = 63680 , alloc = 66920 ) , execution ( in - use = 3520 , alloc = 4032 )
     

567. 
     

568. kkoqbc - subheap ( create addr = 0x2b009666faa0 )
     

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

570. QUERY BLOCK TEXT
     

571. * * * * * * * * * * * * * * * *
     

572. select / * sample 7 * / e . ename , d . dname
     

573. from emp e , dept d
     

574. where e . deptno = d . deptno ( + )
     

575. and d . deptno > 20
     

576. - - - - - - - - - - - - - - - - - - - - -
     

577. QUERY BLOCK SIGNATURE
     

578. - - - - - - - - - - - - - - - - - - - - -
     

579. signature ( optimizer ) : qb_name = SEL$AF7D52CD nbfros = 2 flg =
     

580.   fro ( ) : flg = 0 objn = 75333 hint_alias = "D" @ "SEL$1"
     

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

582. 
     

583. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     

584. SYSTEM STATISTICS INFORMATION
     

585. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     

586.   Using NOWORKLOAD Stats
     

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

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

589.   IOSEEKTIM : 10 milliseconds ( default is 10 )
     

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

591. 
     

592. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

593. BASE STATISTICAL INFORMATION
     

594. * * * * * * * * * * * * * * * * * * * * * * *
     

595. Table Stats : :
     

596.   Table : DEPT  Alias : D
     

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

598.   Column ( # 1 ) : DEPTNO (
     

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

600. Index Stats : :
     

601.   Index : PK_DEPT  Col# : 1
     

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

603. * * * * * * * * * * * * * * * * * * * * * * *
     

604. Table Stats : :
     

605.   Table : EMP  Alias : E
     

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

607.   Column ( # 8 ) : DEPTNO (
     

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

609. Index Stats : :
     

610.   Index : PK_EMP  Col# : 1
     

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

612. Access path analysis for EMP
     

613. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

614. SINGLE TABLE ACCESS PATH
     

615.   Single Table Cardinality Estimation for EMP [ E ]
     

616.   Table : EMP  Alias : E
     

617.     Card : Original : 14 . 000000  Rounded : 7  Computed : 7 . 00  Non Adjusted : 7 . 00
     

618.   Access Path : TableScan
     

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

620.       Cost_io : 3 . 00  Cost_cpu : 40367
     

621.       Resp_io : 3 . 00  Resp_cpu : 40367
     

622.   Best : : AccessPath : TableScan
     

623.          Cost : 3 . 00  Degree : 1  Resp : 3 . 00  Card : 7 . 00  Bytes : 0
     

624. 
     

625. Access path analysis for DEPT
     

626. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

627. SINGLE TABLE ACCESS PATH
     

628.   Single Table Cardinality Estimation for DEPT [ D ]
     

629.   Table : DEPT  Alias : D
     

630.     Card : Original : 4 . 000000  Rounded : 3  Computed : 2 . 67  Non Adjusted : 2 . 67
     

631.   Access Path : TableScan
     

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

633.       Cost_io : 3 . 00  Cost_cpu : 36467
     

634.       Resp_io : 3 . 00  Resp_cpu : 36467
     

635.   Access Path : index ( RangeScan )
     

636.     Index : PK_DEPT
     

637.     resc_io : 2 . 00  resc_cpu : 15353
     

638.     ix_sel : 0 . 666667  ix_sel_with_filters : 0 . 666667
     

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

640.   Best : : AccessPath : IndexRange
     

641.   Index : PK_DEPT
     

642.          Cost : 2 . 00  Degree : 1  Resp : 2 . 00  Card : 2 . 67  Bytes : 0
     

643. 
     

644. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

645. 
     

646. 
     

647. OPTIMIZER STATISTICS AND COMPUTATIONS
     

648. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

649. GENERAL PLANS
     

650. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

651. Considering cardinality - based initial join order .
     

652. Permutations for Starting Table :
     

653. Join order [ 1 ] : DEPT [ D ] # 0  EMP [ E ] # 1
     

654. 
     

655. * * * * * * * * * * * * * * *
     

656. Now joining : EMP [ E ] # 1
     

657. * * * * * * * * * * * * * * *
     

658. NL Join
     

659.   Outer table : Card : 2 . 67  Cost : 2 . 00  Resp : 2 . 00  Degree : 1  Bytes : 13
     

660. Access path analysis for EMP
     

661.   Inner table : EMP  Alias : E
     

662.   Access Path : TableScan
     

663.     NL Join : Cost : 8 . 01  Resp : 8 . 01  Degree : 1
     

664.       Cost_io : 8 . 00  Cost_cpu : 136454
     

665.       Resp_io : 8 . 00  Resp_cpu : 136454
     

666. 
     

667.   Best NL cost : 8 . 01
     

668.           resc : 8 . 01  resc_io : 8 . 00  resc_cpu : 136454
     

669.           resp : 8 . 01  resp_io : 8 . 00  resc_cpu : 136454
     

670. Join Card : 6 . 222222 = outer ( 2 . 666667 ) * inner ( 7 . 000000 ) * sel ( . 333333 )
     

671. Join Card - Rounded : 6 Computed : 6 . 22
     

672.   Outer table : DEPT  Alias : D
     

673.     resc : 2 . 00  card 2 . 67  bytes : 13  deg : 1  resp : 2 . 00
     

674.   Inner table : EMP  Alias : E
     

675.     resc : 3 . 00  card : 7 . 00  bytes : 9  deg : 1  resp : 3 . 00
     

676.     using dmeth : 2  #groups : 1
     

677.     SORT ressource         Sort statistics
     

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

679.       Degree : 1
     

680.       Blocks to Sort : 1 Row size : 20 Total Rows : 7
     

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

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

683.       Total Temp space used : 0
     

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

685. SM Join
     

686.   SM cost : 6 . 00
     

687.      resc : 6 . 00 resc_io : 5 . 00 resc_cpu : 21077711
     

688.      resp : 6 . 00 resp_io : 5 . 00 resp_cpu : 21077711
     

689.   Outer table : DEPT  Alias : D
     

690.     resc : 2 . 00  card 2 . 67  bytes : 13  deg : 1  resp : 2 . 00
     

691.   Inner table : EMP  Alias : E
     

692.     resc : 3 . 00  card : 7 . 00  bytes : 9  deg : 1  resp : 3 . 00
     

693.     using dmeth : 2  #groups : 1
     

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

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

696.   Hash join : Resc : 5 . 50  Resp : 5 . 50 [ multiMatchCost = . 00 ]
     

697. HA Join
     

698.   HA cost : 5 . 50
     

699.      resc : 5 . 50 resc_io : 5 . 00 resc_cpu : 10567423
     

700.      resp : 5 . 50 resp_io : 5 . 00 resp_cpu : 10567423
     

701. Best : : JoinMethod : Hash
     

702.        Cost : 5 . 50  Degree : 1  Resp : 5 . 50  Card : 6 . 22 Bytes : 22
     

703. * * * * * * * * * * * * * * * * * * * * * * *
     

704. Best so far : Table# : 0  cost : 2 . 0007  card : 2 . 6667  bytes : 39
     

705.               Table# : 1  cost : 5 . 5027  card : 6 . 2222  bytes : 132
     

706. * * * * * * * * * * * * * * * * * * * * * * *
     

707. Join order [ 2 ] : EMP [ E ] # 1  DEPT [ D ] #
     

708. 
     

709. * * * * * * * * * * * * * * *
     

710. Now joining : DEPT [ D ] #
     

711. * * * * * * * * * * * * * * *
     

712. NL Join
     

713.   Outer table : Card : 7 . 00  Cost : 3 . 00  Resp : 3 . 00  Degree : 1  Bytes : 9
     

714. Access path analysis for DEPT
     

715.   Inner table : DEPT  Alias : D
     

716.   Access Path : TableScan
     

717.     NL Join : Cost : 14 . 01  Resp : 14 . 01  Degree : 1
     

718.       Cost_io : 14 . 00  Cost_cpu : 295638
     

719.       Resp_io : 14 . 00  Resp_cpu : 295638
     

720. kkofmx : index filter : \ "D\" . \ "DEPTNO\" > 20
     

721. 
     

722.   Access Path : index ( UniqueScan )
     

723.     Index : PK_DEPT
     

724.     resc_io : 1 . 00  resc_cpu : 8341
     

725.     ix_sel : 0 . 250000  ix_sel_with_filters : 0 . 250000
     

726.   * * * * * Logdef predicate Adjustment * * * * * *
     

727.  Final IO cst 0 . 00 , CPU cst 50 . 00
     

728.   * * * * * End Logdef Adjustment * * * * * *
     

729.     NL Join : Cost : 10 . 00  Resp : 10 . 00  Degree : 1
     

730.       Cost_io : 10 . 00  Cost_cpu : 99107
     

731.       Resp_io : 10 . 00  Resp_cpu : 99107
     

732. kkofmx : index filter : \ "D\" . \ "DEPTNO\" > 20
     

733. 
     

734.   Access Path : index ( AllEqUnique )
     

735.     Index : PK_DEPT
     

736.     resc_io : 1 . 00  resc_cpu : 8341
     

737.     ix_sel : 0 . 250000  ix_sel_with_filters : 0 . 166667
     

738.   * * * * * Logdef predicate Adjustment * * * * * *
     

739.  Final IO cst 0 . 00 , CPU cst 50 . 00
     

740.   * * * * * End Logdef Adjustment * * * * * *
     

741.     NL Join : Cost : 10 . 00  Resp : 10 . 00  Degree : 1
     

742.       Cost_io : 10 . 00  Cost_cpu : 99107
     

743.       Resp_io : 10 . 00  Resp_cpu : 99107
     

744. 
     

745.   Best NL cost : 10 . 00
     

746.           resc : 10 . 00  resc_io : 10 . 00  resc_cpu : 99107
     

747.           resp : 10 . 00  resp_io : 10 . 00  resc_cpu : 99107
     

748. Join Card : 6 . 222222 = outer ( 7 . 000000 ) * inner ( 2 . 666667 ) * sel ( . 333333 )
     

749. Join Card - Rounded : 6 Computed : 6 . 22
     

750.   Outer table : EMP  Alias : E
     

751.     resc : 3 . 00  card 7 . 00  bytes : 9  deg : 1  resp : 3 . 00
     

752.   Inner table : DEPT  Alias : D
     

753.     resc : 2 . 00  card : 2 . 67  bytes : 13  deg : 1  resp : 2 . 00
     

754.     using dmeth : 2  #groups : 1
     

755.     SORT ressource         Sort statistics
     

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

757.       Degree : 1
     

758.       Blocks to Sort : 1 Row size : 20 Total Rows : 7
     

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

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

761.       Total Temp space used : 0
     

762.     SORT ressource         Sort statistics
     

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

764.       Degree : 1
     

765.       Blocks to Sort : 1 Row size : 25 Total Rows : 3
     

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

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

768.       Total Temp space used : 0
     

769.   SM join : Resc : 7 . 00  Resp : 7 . 00 [ multiMatchCost = . 00 ]
     

770. SM Join
     

771.   SM cost : 7 . 00
     

772.      resc : 7 . 00 resc_io : 5 . 00 resc_cpu : 42099031
     

773.      resp : 7 . 00 resp_io : 5 . 00 resp_cpu : 42099031
     

774.   Outer table : EMP  Alias : E
     

775.     resc : 3 . 00  card 7 . 00  bytes : 9  deg : 1  resp : 3 . 00
     

776.   Inner table : DEPT  Alias : D
     

777.     resc : 2 . 00  card : 2 . 67  bytes : 13  deg : 1  resp : 2 . 00
     

778.     using dmeth : 2  #groups : 1
     

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

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

781.   Hash join : Resc : 5 . 50  Resp : 5 . 50 [ multiMatchCost = . 00 ]
     

782. HA Join
     

783.   HA cost : 5 . 50
     

784.      resc : 5 . 50 resc_io : 5 . 00 resc_cpu : 10567923
     

785.      resp : 5 . 50 resp_io : 5 . 00 resp_cpu : 10567923
     

786. Join order aborted : cost > best plan cost
     

787. * * * * * * * * * * * * * * * * * * * * * * *
     

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

789. 
     

790. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

791. Number of join permutations tried : 2
     

792. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

793. Consider using bloom filter between D [ DEPT ] and E [ EMP ] with ? ?
     

794. kkoBloomFilter : join ( lcdn : 3 rcdn : 7 jcdn : 6 limit : 9 )
     

795. Computing bloom ndv for creator : D [ DEPT ] ccdn : 2 . 7 and user : E [ EMP ] ucdn : 7 .
     

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

797. kkopqComputeBloomNdv : pred cnt : 2 ndv : 3 reduction : 0
     

798. kkoBloomFilter : join ndv : 3 reduction : . 380952 ( limit : . 500000 ) accepted invalidated
     

799. Enumerating distribution method ( advanced )
     

800. - - - Distribution method for
     

801. join between D [ DEPT ] ( serial ) and E [ EMP ] ( serial ) ; jm = 1 ; right side access path = TableScan
     

802. - - - - serial Hash - Join - > NONE
     

803. 
     

804. ( newjo - save ) [ 0 1 ]
     

805. Trying or - Expansion on query block SEL$AF7D52CD ( # )
     

806. Transfer Optimizer annotations for query block SEL$AF7D52CD ( # )
     

807. id = 0 frofkks [ i ] ( index start key ) predicate = D . DEPTNO > 20
     

808. id = 0 frofkksm [ i ] ( sort - merge/hash ) predicate = E . DEPTNO = D . DEPTNO
     

809. id = 0 frosand ( sort - merge/hash ) predicate = E . DEPTNO = D . DEPTNO
     

810. id = 0 frofand predicate = E . DEPTNO > 20
     

811. Final cost for query block SEL$AF7D52CD ( # ) - All Rows Plan :
     

812.   Best join order : 1
     

813.   Cost : 5 . 5027  Degree : 1  Card : 6 . 0000  Bytes : 132
     

814.   Resc : 5 . 5027  Resc_io : 5 . 0000  Resc_cpu : 10567423
     

815.   Resp : 5 . 5027  Resp_io : 5 . 0000  Resc_cpu : 10567423
     

816. kkoqbc - subheap ( delete addr = 0x2b009666faa0 , in - use = 28672 , alloc = 32840 )
     

817. kkoqbc - end :
     

818.          :
     

819.      call ( in - use = 17304 , alloc = 65656 ) , compile ( in - use = 66456 , alloc = 66920 ) , execution ( in - use = 3520 , alloc = 4032 )
     

820. 
     

821. kkoqbc : finish optimizing query block SEL$AF7D52CD ( # )
     

822. apadrv - end
     

823.            :
     

824.      call ( in - use = 17304 , alloc = 65656 ) , compile ( in - use = 67448 , alloc = 71064 ) , execution ( in - use = 3520 , alloc = 4032 )
     

825. 
     

826. 
     

827. Starting SQL statement dump
     

828. 
     

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

830. sql_id = 36844nrb44f07 plan_hash_value = - 192194834 problem_type = 3
     

831. - - - - - Current SQL Statement for this session ( sql_id = 36844nrb44f07 ) - - - - -
     

832. select / * sample 7 * / e . ename , d . dname
     

833. from emp e , dept d
     

834. where e . deptno = d . deptno ( + )
     

835. and d . deptno > 20
     

836. sql_text_length = 104
     

837. sql = select / * sample 7 * / e . ename , d . dname
     

838. from emp e , dept d
     

839. where e . deptno = d . deptno ( + )
     

840. and d . deptno > 20
     

841. - - - - - Explain Plan Dump - - - - -
     

842. - - - - - Plan Table - - - - -
     

843.  
     

844. = = = = = = = = = = = =
     

845. Plan Table
     

846. = = = = = = = = = = = =
     

847. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

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

849. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

850. |   0 | SELECT STATEMENT               |           |        |       |     6 |            | 
     

851. |    1 |  HASH JOIN                     |           |     6 |    132 |     6 | 00 : 00 : 01 |
     

852. |    2 |   TABLE ACCESS BY INDEX ROWID |  DEPT     |     3 |     39 |     2 | 00 : 00 : 01 |
     

853. |   3 |     INDEX RANGE SCAN            | PK_DEPT |     3 |         |     1 | 00 : 00 : 01 |
     

854. |    4 |    TABLE ACCESS FULL            | EMP      |     7 |     63 |     3 | 00 : 00 : 01 |
     

855. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

856. Predicate Information :
     

857. - - - - - - - - - - - - - - - - - - - - - -
     

858. 1 - access ( E . DEPTNO = D . DEPTNO )
     

859. 3 - access ( D . DEPTNO > 20 )
     

860. 4 - filter ( E . DEPTNO > 20 )
     

861.  
     

862. Content of other_xml column
     

863. = = = = = = = = = = = = = = = = = = = = = = = = = = =
     

864.   db_version : 11 . 2 . . 3
     

865.   parse_schema : SCOTT
     

866.   plan_hash : 4102772462
     

867.   plan_hash_2 : 2230442445
     

868.   Outline Data :
     

869.   / * +
     

870.     BEGIN_OUTLINE_DATA
     

871.       IGNORE_OPTIM_EMBEDDED_HINTS
     

872.       OPTIMIZER_FEATURES_ENABLE ( '11.2.0.3' )
     

873.       DB_VERSION ( '11.2.0.3' )
     

874.       ALL_ROWS
     

875.       OUTLINE_LEAF ( @ SEL$AF7D52CD )
     

876.       OUTER_JOIN_TO_INNER ( @ SEL$1   D @ SEL$1 )
     

877.       OUTLINE ( @ "SEL$1" )
     

878.       INDEX_RS_ASC ( @ "SEL$AF7D52CD   D @ SEL$1   ( DEPT . DEPTNO ) )
     

879.       FULL ( @ "SEL$AF7D52CD" "E" @ "SEL$1" )
     

880.       LEADING ( @ "SEL$AF7D52CD" "D" @ "SEL$1" "E" @ "SEL$1" )
     

881.       USE_HASH ( @ "SEL$AF7D52CD" "E" @ "SEL$1" )
     

882.     END_OUTLINE_DATA
     

883.    * /

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29990276/viewspace-1443621/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29990276/viewspace-1443621/