Transformation之simple view merge 【三】

能從view中拿到rowid 我們就稱之為簡單的view

view 
    被儲存的view
複雜的view
    union/union-all
    group-by
    distinct
複雜view
    not complex
    in-line view（寫到from後的子查詢）
     from子句帶的子查詢

普通的view merge：

點選( 此處 )摺疊或開啟

1. create or replace view emp_mgr_view as
   

2. select e . ename employee , m . ename manager , e . deptno from emp e , emp m
   

3. where e . mgr = m . empno ;
   

4. 
   

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

6. select / * sample - 3 * /
   

7. employee , manager
   

8. from emp_mgr_view
   

9. where deptno = 20 ;
   

10. 
    

11. select * from table ( dbms_xplan . display_cursor ( null , null , 'last' ) ) ;
    

12. @10053d . sql

oracle 把這個檢視消除了，變成了一個簡單了的關聯查詢


將這個查詢轉化成 如下：

把view和外邊的查詢合併在一起，成為了一個merge（ （使）混合;相融;融入;漸漸消失在某物中 ）這是merge英文的含義，oracle直接訪問你的表、不會說先生成檢視，然後再訪問檢視。
可以指定引數關閉掉 這個功能
simple view merge 根本就沒有打在10053裡面，下面我們看看生成的10053.進入到10053之前sql已經轉好

點選( 此處 )摺疊或開啟

1. * * * 2015 - 02 - 26 04 : 02 : 49 . 961

2. * * * SESSION ID : ( 56 . 153 ) 2015 - 02 - 26 04 : 02 : 49 . 961
   

3. * * * CLIENT ID : ( ) 2015 - 02 - 26 04 : 02 : 49 . 961
   

4. * * * SERVICE NAME : ( SYS$USERS ) 2015 - 02 - 26 04 : 02 : 49 . 961
   

5. * * * MODULE NAME : ( SQL * Plus ) 2015 - 02 - 26 04 : 02 : 49 . 961
   

6. * * * ACTION NAME : ( ) 2015 - 02 - 26 04 : 02 : 49 . 961
   

7.  
   

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

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

10. QUERY BLOCK SIGNATURE
    

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

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

13.     fro ( ) : flg = 5 objn = 78822 hint_alias = "EMP_MGR_VIEW" @ "SEL$1"
    

14. 
    

15. Registered qb : SEL$2 0x3e8d1020 ( PARSER )
    

16. - - - - - - - - - - - - - - - - - - - - -
    

17. QUERY BLOCK SIGNATURE
    

18. - - - - - - - - - - - - - - - - - - - - -
    

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

20.     fro ( ) : flg = 4 objn = 75335 hint_alias = E @ SEL$2
    

21.     fro ( 1 ) : flg = 4 objn = 75335 hint_alias = M @ SEL$2
    

22. 
    

23. SPM : statement not found in SMB
    

24. 
    

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

26. Automatic degree of parallelism ( ADOP )
    

27. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

28. Automatic degree of parallelism is disabled : Parameter .
    

29. 
    

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

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

32. Predicate Move - Around ( PM )
    

33. * * * * * * * * * * * * * * * * * * * * * * * * * *
    

34. OPTIMIZER INFORMATION
    

35. 
    

36. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

37. - - - - - Current SQL Statement for this session ( sql_id = 1mfmysf37dut4 ) - - - - -
    

38. select / * sample - 3 * /
    

39. employee , manager
    

40. from emp_mgr_view
    

41. where deptno = 20
    

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

43. Legend
    

44. The following abbreviations are used by optimizer trace .
    

45. CBQT - cost - based query transformation
    

46. JPPD - join predicate push - down
    

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

48. FPD - filter push - down
    

49. PM - predicate move - around
    

50. CVM - complex view merging
    

51. SPJ - select - project - join
    

52. SJC - set join conversion
    

53. SU - subquery unnesting
    

54. OBYE - order by elimination
    

55. OST - old style star transformation
    

56. ST - new ( cbqt ) star transformation
    

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

58. JE - Join Elimination
    

59. JF - join factorization
    

60. SLP - select list pruning
    

61. DP - distinct placement
    

62. qb - query block
    

63. LB - leaf blocks
    

64. DK - distinct keys
    

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

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

67. CLUF - clustering factor
    

68. NDV - number of distinct values
    

69. Resp - response cost
    

70. Card - cardinality
    

71. Resc - resource cost
    

72. NL - nested loops ( join )
    

73. SM - sort merge ( join )
    

74. HA - hash ( join )
    

75. CPUSPEED - CPU Speed
    

76. IOTFRSPEED - I/O transfer speed
    

77. IOSEEKTIM - I/O seek time
    

78. SREADTIM - average single block read time
    

79. MREADTIM - average multiblock read time
    

80. MBRC - average multiblock read count
    

81. MAXTHR - maximum I/O system throughput
    

82. SLAVETHR - average slave I/O throughput
    

83. dmeth - distribution method
    

84.   1 : no partitioning required
    

85.   2 : value partitioned
    

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

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

88.   8 : broadcast right and partition left
    

89.   16 : broadcast left and partition right
    

90.   32 : partition left using partitioning of right
    

91.   64 : partition right using partitioning of left
    

92.   256 : run the join in serial
    

93.   0 : invalid distribution method
    

94. sel - selectivity
    

95. ptn - partition
    

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

97. PARAMETERS USED BY THE OPTIMIZER
    

98. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    

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

100.   PARAMETERS WITH ALTERED VALUES
     

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

102. Compilation Environment Dump
     

103. Bug Fix Control Environment
     

104. 
     

105. 
     

106.    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

107.   PARAMETERS WITH DEFAULT VALUES
     

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

109. Compilation Environment Dump
     

110. optimizer_mode_hinted = false
     

111. optimizer_features_hinted = 0 . .
     

112. parallel_execution_enabled = true
     

113. parallel_query_forced_dop = 0
     

114. parallel_dml_forced_dop = 0
     

115. parallel_ddl_forced_degree = 0
     

116. parallel_ddl_forced_instances = 0
     

117. _query_rewrite_fudge = 90
     

118. optimizer_features_enable = 11 . 2 . . 3
     

119. _optimizer_search_limit = 5
     

120. cpu_count = 1
     

121. active_instance_count = 1
     

122. parallel_threads_per_cpu = 2
     

123. hash_area_size = 131072
     

124. bitmap_merge_area_size = 1048576
     

125. sort_area_size = 65536
     

126. sort_area_retained_size = 0
     

127. _sort_elimination_cost_ratio = 0
     

128. _optimizer_block_size = 8192
     

129. _sort_multiblock_read_count = 2
     

130. _hash_multiblock_io_count = 0
     

131. _db_file_optimizer_read_count = 8
     

132. _optimizer_max_permutations = 2000
     

133. pga_aggregate_target = 286720 KB
     

134. _pga_max_size = 204800 KB
     

135. _query_rewrite_maxdisjunct = 257
     

136. _smm_auto_min_io_size = 56 KB
     

137. _smm_auto_max_io_size = 248 KB
     

138. _smm_min_size = 286 KB
     

139. _smm_max_size = 57344 KB
     

140. _smm_px_max_size = 143360 KB
     

141. _cpu_to_io = 0
     

142. _optimizer_undo_cost_change = 11 . 2 . . 3
     

143. parallel_query_mode = enabled
     

144. parallel_dml_mode = disabled
     

145. parallel_ddl_mode = enabled
     

146. optimizer_mode = all_rows
     

147. sqlstat_enabled = false
     

148. _optimizer_percent_parallel = 101
     

149. _always_anti_join = choose
     

150. _always_semi_join = choose
     

151. _optimizer_mode_force = true
     

152. _partition_view_enabled = true
     

153. _always_star_transformation = false
     

154. _query_rewrite_or_error = false
     

155. _hash_join_enabled = true
     

156. cursor_sharing = exact
     

157. _b_tree_bitmap_plans = true
     

158. star_transformation_enabled = false
     

159. _optimizer_cost_model = choose
     

160. _new_sort_cost_estimate = true
     

161. _complex_view_merging = true
     

162. _unnest_subquery = true
     

163. _eliminate_common_subexpr = true
     

164. _pred_move_around = true
     

165. _convert_set_to_join = false
     

166. _push_join_predicate = true
     

167. _push_join_union_view = true
     

168. _fast_full_scan_enabled = true
     

169. _optim_enhance_nnull_detection = true
     

170. _parallel_broadcast_enabled = true
     

171. _px_broadcast_fudge_factor = 100
     

172. _ordered_nested_loop = true
     

173. _no_or_expansion = false
     

174. optimizer_index_cost_adj = 100
     

175. optimizer_index_caching = 0
     

176. _system_index_caching = 0
     

177. _disable_datalayer_sampling = false
     

178. query_rewrite_enabled = true
     

179. query_rewrite_integrity = enforced
     

180. _query_cost_rewrite = true
     

181. _query_rewrite_2 = true
     

182. _query_rewrite_1 = true
     

183. _query_rewrite_expression = true
     

184. _query_rewrite_jgmigrate = true
     

185. _query_rewrite_fpc = true
     

186. _query_rewrite_drj = true
     

187. _full_pwise_join_enabled = true
     

188. _partial_pwise_join_enabled = true
     

189. _left_nested_loops_random = true
     

190. _improved_row_length_enabled = true
     

191. _index_join_enabled = true
     

192. _enable_type_dep_selectivity = true
     

193. _improved_outerjoin_card = true
     

194. _optimizer_adjust_for_nulls = true
     

195. _optimizer_degree = 0
     

196. _use_column_stats_for_function = true
     

197. _subquery_pruning_enabled = true
     

198. _subquery_pruning_mv_enabled = false
     

199. _or_expand_nvl_predicate = true
     

200. _like_with_bind_as_equality = false
     

201. _table_scan_cost_plus_one = true
     

202. _cost_equality_semi_join = true
     

203. _default_non_equality_sel_check = true
     

204. _new_initial_join_orders = true
     

205. _oneside_colstat_for_equijoins = true
     

206. _optim_peek_user_binds = true
     

207. _minimal_stats_aggregation = true
     

208. _force_temptables_for_gsets = false
     

209. workarea_size_policy = auto
     

210. _smm_auto_cost_enabled = true
     

211. _gs_anti_semi_join_allowed = true
     

212. _optim_new_default_join_sel = true
     

213. optimizer_dynamic_sampling = 2
     

214. _pre_rewrite_push_pred = true
     

215. _optimizer_new_join_card_computation = true
     

216. _union_rewrite_for_gs = yes_gset_mvs
     

217. _generalized_pruning_enabled = true
     

218. _optim_adjust_for_part_skews = true
     

219. _force_datefold_trunc = false
     

220. statistics_level = typical
     

221. _optimizer_system_stats_usage = true
     

222. skip_unusable_indexes = true
     

223. _remove_aggr_subquery = true
     

224. _optimizer_push_down_distinct = 0
     

225. _dml_monitoring_enabled = true
     

226. _optimizer_undo_changes = false
     

227. _predicate_elimination_enabled = true
     

228. _nested_loop_fudge = 100
     

229. _project_view_columns = true
     

230. _local_communication_costing_enabled = true
     

231. _local_communication_ratio = 50
     

232. _query_rewrite_vop_cleanup = true
     

233. _slave_mapping_enabled = true
     

234. _optimizer_cost_based_transformation = linear
     

235. _optimizer_mjc_enabled = true
     

236. _right_outer_hash_enable = true
     

237. _spr_push_pred_refspr = true
     

238. _optimizer_cache_stats = false
     

239. _optimizer_cbqt_factor = 50
     

240. _optimizer_squ_bottomup = true
     

241. _fic_area_size = 131072
     

242. _optimizer_skip_scan_enabled = true
     

243. _optimizer_cost_filter_pred = false
     

244. _optimizer_sortmerge_join_enabled = true
     

245. _optimizer_join_sel_sanity_check = true
     

246. _mmv_query_rewrite_enabled = true
     

247. _bt_mmv_query_rewrite_enabled = true
     

248. _add_stale_mv_to_dependency_list = true
     

249. _distinct_view_unnesting = false
     

250. _optimizer_dim_subq_join_sel = true
     

251. _optimizer_disable_strans_sanity_checks = 0
     

252. _optimizer_compute_index_stats = true
     

253. _push_join_union_view2 = true
     

254. _optimizer_ignore_hints = false
     

255. _optimizer_random_plan = 0
     

256. _query_rewrite_setopgrw_enable = true
     

257. _optimizer_correct_sq_selectivity = true
     

258. _disable_function_based_index = false
     

259. _optimizer_join_order_control = 3
     

260. _optimizer_cartesian_enabled = true
     

261. _optimizer_starplan_enabled = true
     

262. _extended_pruning_enabled = true
     

263. _optimizer_push_pred_cost_based = true
     

264. _optimizer_null_aware_antijoin = true
     

265. _optimizer_extend_jppd_view_types = true
     

266. _sql_model_unfold_forloops = run_time
     

267. _enable_dml_lock_escalation = false
     

268. _bloom_filter_enabled = true
     

269. _update_bji_ipdml_enabled = 0
     

270. _optimizer_extended_cursor_sharing = udo
     

271. _dm_max_shared_pool_pct = 1
     

272. _optimizer_cost_hjsmj_multimatch = true
     

273. _optimizer_transitivity_retain = true
     

274. _px_pwg_enabled = true
     

275. optimizer_secure_view_merging = true
     

276. _optimizer_join_elimination_enabled = true
     

277. flashback_table_rpi = non_fbt
     

278. _optimizer_cbqt_no_size_restriction = true
     

279. _optimizer_enhanced_filter_push = true
     

280. _optimizer_filter_pred_pullup = true
     

281. _rowsrc_trace_level = 0
     

282. _simple_view_merging = true
     

283. _optimizer_rownum_pred_based_fkr = true
     

284. _optimizer_better_inlist_costing = all
     

285. _optimizer_self_induced_cache_cost = false
     

286. _optimizer_min_cache_blocks = 10
     

287. _optimizer_or_expansion = depth
     

288. _optimizer_order_by_elimination_enabled = true
     

289. _optimizer_outer_to_anti_enabled = true
     

290. _selfjoin_mv_duplicates = true
     

291. _dimension_skip_null = true
     

292. _force_rewrite_enable = false
     

293. _optimizer_star_tran_in_with_clause = true
     

294. _optimizer_complex_pred_selectivity = true
     

295. _optimizer_connect_by_cost_based = true
     

296. _gby_hash_aggregation_enabled = true
     

297. _globalindex_pnum_filter_enabled = true
     

298. _px_minus_intersect = true
     

299. _fix_control_key = 0
     

300. _force_slave_mapping_intra_part_loads = false
     

301. _force_tmp_segment_loads = false
     

302. _query_mmvrewrite_maxpreds = 10
     

303. _query_mmvrewrite_maxintervals = 5
     

304. _query_mmvrewrite_maxinlists = 5
     

305. _query_mmvrewrite_maxdmaps = 10
     

306. _query_mmvrewrite_maxcmaps = 20
     

307. _query_mmvrewrite_maxregperm = 512
     

308. _query_mmvrewrite_maxqryinlistvals = 500
     

309. _disable_parallel_conventional_load = false
     

310. _trace_virtual_columns = false
     

311. _replace_virtual_columns = true
     

312. _virtual_column_overload_allowed = true
     

313. _kdt_buffering = true
     

314. _first_k_rows_dynamic_proration = true
     

315. _optimizer_sortmerge_join_inequality = true
     

316. _optimizer_aw_stats_enabled = true
     

317. _bloom_pruning_enabled = true
     

318. result_cache_mode = MANUAL
     

319. _px_ual_serial_input = true
     

320. _optimizer_skip_scan_guess = false
     

321. _enable_row_shipping = true
     

322. _row_shipping_threshold = 80
     

323. _row_shipping_explain = false
     

324. transaction_isolation_level = read_commited
     

325. _optimizer_distinct_elimination = true
     

326. _optimizer_multi_level_push_pred = true
     

327. _optimizer_group_by_placement = true
     

328. _optimizer_rownum_bind_default = 10
     

329. _enable_query_rewrite_on_remote_objs = true
     

330. _optimizer_extended_cursor_sharing_rel = simple
     

331. _optimizer_adaptive_cursor_sharing = true
     

332. _direct_path_insert_features = 0
     

333. _optimizer_improve_selectivity = true
     

334. optimizer_use_pending_statistics = false
     

335. _optimizer_enable_density_improvements = true
     

336. _optimizer_aw_join_push_enabled = true
     

337. _optimizer_connect_by_combine_sw = true
     

338. _enable_pmo_ctas = 0
     

339. _optimizer_native_full_outer_join = force
     

340. _bloom_predicate_enabled = true
     

341. _optimizer_enable_extended_stats = true
     

342. _is_lock_table_for_ddl_wait_lock = 0
     

343. _pivot_implementation_method = choose
     

344. optimizer_capture_sql_plan_baselines = false
     

345. optimizer_use_sql_plan_baselines = true
     

346. _optimizer_star_trans_min_cost = 0
     

347. _optimizer_star_trans_min_ratio = 0
     

348. _with_subquery = OPTIMIZER
     

349. _optimizer_fkr_index_cost_bias = 10
     

350. _optimizer_use_subheap = true
     

351. parallel_degree_policy = manual
     

352. parallel_degree = 0
     

353. parallel_min_time_threshold = 10
     

354. _parallel_time_unit = 10
     

355. _optimizer_or_expansion_subheap = true
     

356. _optimizer_free_transformation_heap = true
     

357. _optimizer_reuse_cost_annotations = true
     

358. _result_cache_auto_size_threshold = 100
     

359. _result_cache_auto_time_threshold = 1000
     

360. _optimizer_nested_rollup_for_gset = 100
     

361. _nlj_batching_enabled = 1
     

362. parallel_query_default_dop = 0
     

363. is_recur_flags = 0
     

364. optimizer_use_invisible_indexes = false
     

365. flashback_data_archive_internal_cursor = 0
     

366. _optimizer_extended_stats_usage_control = 192
     

367. _parallel_syspls_obey_force = true
     

368. cell_offload_processing = true
     

369. _rdbms_internal_fplib_enabled = false
     

370. db_file_multiblock_read_count = 97
     

371. _bloom_folding_enabled = true
     

372. _mv_generalized_oj_refresh_opt = true
     

373. cell_offload_compaction = ADAPTIVE
     

374. cell_offload_plan_display = AUTO
     

375. _bloom_predicate_pushdown_to_storage = true
     

376. _bloom_vector_elements = 0
     

377. _bloom_pushing_max = 512
     

378. parallel_degree_limit = 65535
     

379. parallel_force_local = false
     

380. parallel_max_degree = 2
     

381. total_cpu_count = 1
     

382. _optimizer_coalesce_subqueries = true
     

383. _optimizer_fast_pred_transitivity = true
     

384. _optimizer_fast_access_pred_analysis = true
     

385. _optimizer_unnest_disjunctive_subq = true
     

386. _optimizer_unnest_corr_set_subq = true
     

387. _optimizer_distinct_agg_transform = true
     

388. _aggregation_optimization_settings = 0
     

389. _optimizer_connect_by_elim_dups = true
     

390. _optimizer_eliminate_filtering_join = true
     

391. _connect_by_use_union_all = true
     

392. dst_upgrade_insert_conv = true
     

393. advanced_queuing_internal_cursor = 0
     

394. _optimizer_unnest_all_subqueries = true
     

395. parallel_autodop = 0
     

396. parallel_ddldml = 0
     

397. _parallel_cluster_cache_policy = adaptive
     

398. _parallel_scalability = 50
     

399. iot_internal_cursor = 0
     

400. _optimizer_instance_count = 0
     

401. _optimizer_connect_by_cb_whr_only = false
     

402. _suppress_scn_chk_for_cqn = nosuppress_1466
     

403. _optimizer_join_factorization = true
     

404. _optimizer_use_cbqt_star_transformation = true
     

405. _optimizer_table_expansion = true
     

406. _and_pruning_enabled = true
     

407. _deferred_constant_folding_mode = DEFAULT
     

408. _optimizer_distinct_placement = true
     

409. partition_pruning_internal_cursor = 0
     

410. parallel_hinted = none
     

411. _sql_compatibility = 0
     

412. _optimizer_use_feedback = true
     

413. _optimizer_try_st_before_jppd = true
     

414. _dml_frequency_tracking = false
     

415. _optimizer_interleave_jppd = true
     

416. kkb_drop_empty_segments = 0
     

417. _px_partition_scan_enabled = true
     

418. _px_partition_scan_threshold = 64
     

419. _optimizer_false_filter_pred_pullup = true
     

420. _bloom_minmax_enabled = true
     

421. only_move_row = 0
     

422. _optimizer_enable_table_lookup_by_nl = true
     

423. deferred_segment_creation = true
     

424. _optimizer_filter_pushdown = true
     

425. _allow_level_without_connect_by = false
     

426. _max_rwgs_groupings = 8192
     

427. _optimizer_outer_join_to_inner = true
     

428. _optimizer_full_outer_join_to_outer = true
     

429. _px_numa_support_enabled = true
     

430. total_processor_group_count = 1
     

431. Bug Fix Control Environment
     

432.     fix  3834770 = 1
     

433.     fix  3746511 = enabled
     

434.     fix  4519016 = enabled
     

435.     fix  3118776 = enabled
     

436.     fix  4488689 = enabled
     

437.     

438.      ... ...

439.    

440. 
     

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

442.   PARAMETERS IN OPT_PARAM HINT
     

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

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

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

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

447. 
     

448. Considering Query Transformations on query block SEL$1 ( # )                                ----開始transformation
     

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

450. Query transformations ( QT )
     

451. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

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

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

457. Check Basic Validity for Non - Union View for query block SEL$2 ( # )
     

458. JPPD : JPPD bypassed : View has unsupported constructs .
     

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

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

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

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

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

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

465. CBQT : Validity checks failed for 1mfmysf37dut4 .
     

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

467. * * * * * * * * * * * * * * * * * * * * * * * * *
     

468. Common Subexpression elimination ( CSE )
     

469. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

471. * * * * * * * * * * * * * * * * * * * * * * * * *
     

472. Common Subexpression elimination ( CSE )
     

473. * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

477. * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

478. Order - by elimination ( OBYE )
     

479. * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

481. JE : Considering Join Elimination on query block SEL$2 ( # )
     

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

483. Join Elimination ( JE )                                                              
     

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

485. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

486. SELECT E . ENAME   EMPLOYEE , M . ENAME   MANAGER , E . DEPTNO   DEPTNO  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . MGR = M . EMPNO    這裡已經給你做好了轉換
     

487. JE : cfro : EMP objn : 75335 col# : 4 dfro : EMP dcol# : 1
     

488. JE : cfro : EMP objn : 75335 col# : 1 dfro : EMP dcol# : 4
     

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

490. SELECT E . ENAME   EMPLOYEE , M . ENAME   MANAGER , E . DEPTNO   DEPTNO  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . MGR = M . EMPNO
     

491. Query block SEL$2 ( # ) unchanged
     

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

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

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

495. CVM : Checking validity of merging in query block SEL$2 ( # )
     

496. CVM : Considering view merge in query block SEL$2 ( # )
     

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

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

499. CVM : Merging SPJ view SEL$2 ( # ) into SEL$1 ( # )              ---從$2merge到$1裡面，說明merger了，但是沒有打出來，simple和complex view都用CVM標示
     

500. Registered qb : SEL$F5BB74E1 0x3e8d6678 ( VIEW MERGE SEL$1 ; SEL$2 )
     

501. - - - - - - - - - - - - - - - - - - - - -
     

502. QUERY BLOCK SIGNATURE
     

503. - - - - - - - - - - - - - - - - - - - - -
     

504.   signature ( ) : qb_name = SEL$F5BB74E1 nbfros = 2 flg =
     

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

506.     fro ( 1 ) : flg = 0 objn = 75335 hint_alias = "M" @ "SEL$2"
     

507. 
     

508. OJE : Begin : find best directive for query block SEL$F5BB74E1 ( # )
     

509. OJE : End : finding best directive for query block SEL$F5BB74E1 ( # )
     

510. JE : Considering Join Elimination on query block SEL$F5BB74E1 ( # )
     

511. * * * * * * * * * * * * * * * * * * * * * * * * *
     

512. Join Elimination ( JE )
     

513. * * * * * * * * * * * * * * * * * * * * * * * * *
     

514. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

515. SELECT E . ENAME   EMPLOYEE , M . ENAME   MANAGER  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

516. JE : cfro : EMP objn : 75335 col# : 4 dfro : EMP dcol# : 1
     

517. JE : cfro : EMP objn : 75335 col# : 1 dfro : EMP dcol# : 4
     

518. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

519. SELECT E . ENAME   EMPLOYEE , M . ENAME   MANAGER  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

520. Query block SEL$F5BB74E1 ( # ) unchanged
     

521. query block SEL$1 transformed to SEL$F5BB74E1 ( # )
     

522. Considering Query Transformations on query block SEL$F5BB74E1 ( # )
     

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

524. Query transformations ( QT )
     

525. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

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

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

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

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

532. CBQT : Validity checks failed for 1mfmysf37dut4 .
     

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

534. * * * * * * * * * * * * * * * * * * * * * * * * *
     

535. Common Subexpression elimination ( CSE )
     

536. * * * * * * * * * * * * * * * * * * * * * * * * *
     

537. CSE : CSE not performed on query block SEL$F5BB74E1 ( # ) .
     

538. SU : Considering subquery unnesting in query block SEL$F5BB74E1 ( # )
     

539. * * * * * * * * * * * * * * * * * * * *
     

540. Subquery Unnest ( SU )
     

541. * * * * * * * * * * * * * * * * * * * *
     

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

543. * * * * * * * * * * * * * * * * * * * * * * * * *
     

544. Set - Join Conversion ( SJC )
     

545. * * * * * * * * * * * * * * * * * * * * * * * * *
     

546. SJC : not performed
     

547. JE : Considering Join Elimination on query block SEL$F5BB74E1 ( # )
     

548. * * * * * * * * * * * * * * * * * * * * * * * * *
     

549. Join Elimination ( JE )
     

550. * * * * * * * * * * * * * * * * * * * * * * * * *
     

551. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

552. SELECT E . ENAME   EMPLOYEE , M . ENAME   MANAGER  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

553. JE : cfro : EMP objn : 75335 col# : 4 dfro : EMP dcol# : 1
     

554. JE : cfro : EMP objn : 75335 col# : 1 dfro : EMP dcol# : 4
     

555. SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
     

556. SELECT E . ENAME   EMPLOYEE , M . ENAME   MANAGER  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

557. Query block SEL$F5BB74E1 ( # ) unchanged
     

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

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

560. Predicate Move - Around ( PM )
     

561. * * * * * * * * * * * * * * * * * * * * * * * * * *
     

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

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

564. query block SEL$F5BB74E1 ( # ) unchanged
     

565. FPD : Considering simple filter push in query block SEL$F5BB74E1 ( # )
     

566. E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

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

568. finally : E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

569. 
     

570. apadrv - start sqlid = 1853933574856043300
     

571.    :
     

572.      call ( in - use = 4048 , alloc = 16344 ) , compile ( in - use = 78440 , alloc = 79352 ) , execution ( in - use = 4072 , alloc = 8088 )
     

573. 
     

574. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

575. Peeked values of the binds in SQL statement
     

576. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

577. 
     

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

579. SELECT "E . ENAME   EMPLOYEE , M . ENAME   MANAGER  FROM SCOTT . EMP   E , SCOTT . EMP   M  WHERE E . DEPTNO = 20 AND E . MGR = M . EMPNO
     

580. kkoqbc : optimizing query block SEL$F5BB74E1 ( # )
     

581.         
     

582.          :
     

583.      call ( in - use = 4152 , alloc = 16344 ) , compile ( in - use = 79712 , alloc = 83496 ) , execution ( in - use = 4072 , alloc = 8088 )
     

584. 
     

585. kkoqbc - subheap ( create addr = 0x2b043e8dfaa0 )
     

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

587. QUERY BLOCK TEXT
     

588. * * * * * * * * * * * * * * * *
     

589. select / * sample - 3 * /
     

590. employee , manager
     

591. from emp_mgr_view
     

592. where deptno = 20
     

593. - - - - - - - - - - - - - - - - - - - - -
     

594. QUERY BLOCK SIGNATURE
     

595. - - - - - - - - - - - - - - - - - - - - -
     

596. signature ( optimizer ) : qb_name = SEL$F5BB74E1 nbfros = 2 flg =
     

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

598.   fro ( 1 ) : flg = 0 objn = 75335 hint_alias = "M" @ "SEL$2"
     

599. 
     

600. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     

601. SYSTEM STATISTICS INFORMATION
     

602. - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     

603.   Using NOWORKLOAD Stats
     

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

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

606.   IOSEEKTIM : 10 milliseconds ( default is 10 )
     

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

608. 
     

609. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

610. BASE STATISTICAL INFORMATION
     

611. * * * * * * * * * * * * * * * * * * * * * * *
     

612. Table Stats : :                                   ---這裡是表的統計資訊
     

613.   Table : EMP  Alias : M
     

614.     #Rows : 14 #Blks : 5 AvgRowLen : 38 . 00 ChainCnt : 0 . 00  ---一共過少chain
     

615.    Column ( # 1 ) : EMPNO (
     

616.       AvgLen : 4 NDV : 14 Nulls : 0 Density : 0 . 071429 Min : 7369 Max : 7934    --列平均長度
     

617. Index Stats : :                                               ----這裡是index的統計資訊
     

618.   Index : PK_EMP  Col# : 1
     

619.      LVLS : 0 #LB : 1  #DK : 14 LB/K : 1 . 00  DB/K : 1 . 00  CLUF : 1 . 00   ---LVLS 是blevel， CLUF：cluster factor
     

620. * * * * * * * * * * * * * * * * * * * * * * *
     

621. Table Stats : :                                                    ----優化器認為涉及到2兩張表
     

622.   Table : EMP  Alias : E
     

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

624.   Column ( # 4 ) : MGR (
     

625.     AvgLen : 4 NDV : 6 Nulls : 1 Density : 0 . 166667 Min : 7566 Max : 7902
     

626. Index Stats : :
     

627.   Index : PK_EMP  Col# : 1
     

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

629. Access path analysis for EMP
     

630. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

631. SINGLE TABLE ACCESS PATH               ---研究單表訪問成本
     

632.   Single Table Cardinality Estimation for EMP [ E ]
     

633.   Column ( # 8 ) : DEPTNO (
     

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

635. 
     

636.   Table : EMP  Alias : E
     

637.     Card : Original : 14 . 000000  Rounded : 4  Computed : 4 . 33  Non Adjusted : 4 . 33
     

638.   Access Path : TableScan
     

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

640.       Cost_io : 3 . 00  Cost_cpu : 40461
     

641.       Resp_io : 3 . 00  Resp_cpu : 40461
     

642.   Best : : AccessPath : TableScan
     

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

644. 
     

645. Access path analysis for EMP
     

646. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

647. SINGLE TABLE ACCESS PATH
     

648.   Single Table Cardinality Estimation for EMP [ M ]
     

649.   Table : EMP  Alias : M
     

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

651.   Access Path : TableScan
     

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

653.       Cost_io : 3 . 00  Cost_cpu : 37987
     

654.       Resp_io : 3 . 00  Resp_cpu : 37987
     

655.   Best : : AccessPath : TableScan
     

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

657. 
     

658. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

659. 
     

660. 
     

661. OPTIMIZER STATISTICS AND COMPUTATIONS
     

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

663. GENERAL PLANS
     

664. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

665. Considering cardinality - based initial join order .
     

666. Permutations for Starting Table :
     

667. Join order [ 1 ] : EMP [ E ] # 0  EMP [ M ] # 1       ---這是第一個驅動和被驅動 E做驅動表 M做被驅動表
     

668. 
     

669. * * * * * * * * * * * * * * *
     

670. Now joining : EMP [ M ] # 1
     

671. * * * * * * * * * * * * * * *
     

672. NL Join
     

673.   Outer table : Card : 4 . 33  Cost : 3 . 00  Resp : 3 . 00  Degree : 1  Bytes : 13
     

674. Access path analysis for EMP
     

675.   Inner table : EMP  Alias : M
     

676.   Access Path : TableScan
     

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

678.       Cost_io : 10 . 00  Cost_cpu : 192409
     

679.       Resp_io : 10 . 00  Resp_cpu : 192409
     

680.   Access Path : index ( UniqueScan )
     

681.     Index : PK_EMP
     

682.     resc_io : 1 . 00  resc_cpu : 8341
     

683.     ix_sel : 0 . 071429  ix_sel_with_filters : 0 . 071429
     

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

685.       Cost_io : 7 . 00  Cost_cpu : 73826
     

686.       Resp_io : 7 . 00  Resp_cpu : 73826
     

687.   Access Path : index ( AllEqUnique )
     

688.     Index : PK_EMP
     

689.     resc_io : 1 . 00  resc_cpu : 8341
     

690.     ix_sel : 0 . 071429  ix_sel_with_filters : 0 . 071429
     

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

692.       Cost_io : 7 . 00  Cost_cpu : 73826
     

693.       Resp_io : 7 . 00  Resp_cpu : 73826
     

694. 
     

695.    Best NL cost : 7 . 00
     

696.           resc : 7 . 00  resc_io : 7 . 00  resc_cpu : 73826
     

697.           resp : 7 . 00  resp_io : 7 . 00  resc_cpu : 73826
     

698. Join Card : 4 . 333333 = outer ( 4 . 333333 ) * inner ( 14 . 000000 ) * sel ( . 071429 )
     

699. Join Card - Rounded : 4 Computed : 4 . 33
     

700.   Outer table : EMP  Alias : E
     

701.     resc : 3 . 00  card 4 . 33  bytes : 13  deg : 1  resp : 3 . 00
     

702.   Inner table : EMP  Alias : M
     

703.     resc : 3 . 00  card : 14 . 00  bytes : 10  deg : 1  resp : 3 . 00
     

704.     using dmeth : 2  #groups : 1
     

705.     SORT ressource         Sort statistics
     

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

707.       Degree : 1
     

708.       Blocks to Sort : 1 Row size : 25 Total Rows : 4
     

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

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

711.       Total Temp space used : 0
     

712.     SORT ressource         Sort statistics
     

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

714.       Degree : 1
     

715.       Blocks to Sort : 1 Row size : 21 Total Rows : 14
     

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

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

718.       Total Temp space used : 0
     

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

720. SM Join
     

721.    SM cost : 8 . 00
     

722.      resc : 8 . 00 resc_io : 6 . 00 resc_cpu : 42123421
     

723.      resp : 8 . 00 resp_io : 6 . 00 resp_cpu : 42123421
     

724.   Outer table : EMP  Alias : E
     

725.     resc : 3 . 00  card 4 . 33  bytes : 13  deg : 1  resp : 3 . 00
     

726.   Inner table : EMP  Alias : M
     

727.     resc : 3 . 00  card : 14 . 00  bytes : 10  deg : 1  resp : 3 . 00
     

728.     using dmeth : 2  #groups : 1
     

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

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

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

732. HA Join
     

733.    HA cost : 6 . 50
     

734.      resc : 6 . 50 resc_io : 6 . 00 resc_cpu : 10591000
     

735.      resp : 6 . 50 resp_io : 6 . 00 resp_cpu : 10591000
     

736. Best : : JoinMethod : Hash
     

737.        Cost : 6 . 50  Degree : 1  Resp : 6 . 50  Card : 4 . 33 Bytes : 23
     

738. * * * * * * * * * * * * * * * * * * * * * * *
     

739. Best so far : Table# : 0  cost : 3 . 0019  card : 4 . 3333  bytes : 52
     

740.               Table# : 1  cost : 6 . 5038  card : 4 . 3333  bytes : 92
     

741. * * * * * * * * * * * * * * * * * * * * * * *
     

742. Join order [ 2 ] : EMP [ M ] # 1  EMP [ E ] # 0   先順序[1]/[2]，然後裡面包含nl sm hj
     

743. 
     

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

745. Now joining : EMP [ E ] #
     

746. * * * * * * * * * * * * * * *
     

747. NL Join
     

748.   Outer table : Card : 14 . 00  Cost : 3 . 00  Resp : 3 . 00  Degree : 1  Bytes : 10
     

749. Access path analysis for EMP
     

750.   Inner table : EMP  Alias : E
     

751.   Access Path : TableScan
     

752.     NL Join : Cost : 24 . 03  Resp : 24 . 03  Degree : 1
     

753.       Cost_io : 24 . 00  Cost_cpu : 604435
     

754.       Resp_io : 24 . 00  Resp_cpu : 604435
     

755. 
     

756.    Best NL cost : 24 . 03
     

757.           resc : 24 . 03  resc_io : 24 . 00  resc_cpu : 604435
     

758.           resp : 24 . 03  resp_io : 24 . 00  resc_cpu : 604435
     

759. Join Card : 4 . 333333 = outer ( 14 . 000000 ) * inner ( 4 . 333333 ) * sel ( . 071429 )             cardinality
     

760. Join Card - Rounded : 4 Computed : 4 . 33
     

761.   Outer table : EMP  Alias : M
     

762.     resc : 3 . 00  card 14 . 00  bytes : 10  deg : 1  resp : 3 . 00
     

763.   Inner table : EMP  Alias : E
     

764.     resc : 3 . 00  card : 4 . 33  bytes : 13  deg : 1  resp : 3 . 00
     

765.     using dmeth : 2  #groups : 1
     

766.     SORT ressource         Sort statistics
     

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

768.       Degree : 1
     

769.       Blocks to Sort : 1 Row size : 21 Total Rows : 14
     

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

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

772.       Total Temp space used : 0
     

773.     SORT ressource         Sort statistics
     

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

775.       Degree : 1
     

776.       Blocks to Sort : 1 Row size : 25 Total Rows : 4
     

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

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

779.       Total Temp space used : 0
     

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

781. SM Join
     

782.    SM cost : 8 . 00
     

783.      resc : 8 . 00 resc_io : 6 . 00 resc_cpu : 42123421
     

784.      resp : 8 . 00 resp_io : 6 . 00 resp_cpu : 42123421
     

785. SM Join ( with index on outer )
     

786.   Access Path : index ( FullScan )
     

787.     Index : PK_EMP
     

788.     resc_io : 2 . 00  resc_cpu : 19423
     

789.     ix_sel : 1 . 000000  ix_sel_with_filters : 1 . 000000
     

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

791.   Outer table : EMP  Alias : M
     

792.     resc : 2 . 00  card 14 . 00  bytes : 10  deg : 1  resp : 2 . 00
     

793.   Inner table : EMP  Alias : E
     

794.     resc : 3 . 00  card : 4 . 33  bytes : 13  deg : 1  resp : 3 . 00
     

795.     using dmeth : 2  #groups : 1
     

796.     SORT ressource         Sort statistics
     

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

798.       Degree : 1
     

799.       Blocks to Sort : 1 Row size : 25 Total Rows : 4
     

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

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

802.       Total Temp space used : 0
     

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

804.   Outer table : EMP  Alias : M
     

805.     resc : 3 . 00  card 14 . 00  bytes : 10  deg : 1  resp : 3 . 00
     

806.   Inner table : EMP  Alias : E
     

807.     resc : 3 . 00  card : 4 . 33  bytes : 13  deg : 1  resp : 3 . 00
     

808.     using dmeth : 2  #groups : 1
     

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

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

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

812. HA Join
     

813.    HA cost : 6 . 50
     

814.      resc : 6 . 50 resc_io : 6 . 00 resc_cpu : 10591500
     

815.      resp : 6 . 50 resp_io : 6 . 00 resp_cpu : 10591500
     

816. Best : : JoinMethod : SortMerge
     

817.        Cost : 6 . 00  Degree : 1  Resp : 6 . 00  Card : 4 . 33 Bytes : 23
     

818. * * * * * * * * * * * * * * * * * * * * * * *
     

819. Best so far : Table# : 1  cost : 2 . 0009  card : 14 . 0000  bytes : 140
     

820.               Table# : 0  cost : 6 . 0029  card : 4 . 3333  bytes : 92
     

821. * * * * * * * * * * * * * * * * * * * * * * *
     

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

823. 
     

824. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

825. Number of join permutations tried : 2
     

826. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     

827. Consider using bloom filter between M [ EMP ] and E [ EMP ] with ? ?
     

828. kkoBloomFilter : join ( lcdn : 14 rcdn : 4 jcdn : 4 limit : 30 )
     

829. Computing bloom ndv for creator : M [ EMP ] ccdn : 14 . 0 and user : E [ EMP ] ucdn : 4 . 3
     

830. kkopqComputeBloomNdv : predicate ( bndv : 6 ndv : 6 ) and ( bndv : 14 ndv : 14 )
     

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

832. kkoBloomFilter : join ndv : 0 reduction : 3 . 230769 ( limit : . 500000 ) rejected because distinct value ratio
     

833. Enumerating distribution method ( advanced )
     

834. - - - Distribution method for
     

835. join between M [ EMP ] ( serial ) and E [ EMP ] ( serial ) ; jm = 7 ; right side access path = TableScan
     

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

837. 
     

838. ( newjo - save ) [ 0 1 ]
     

839. Trying or - Expansion on query block SEL$F5BB74E1 ( # )
     

840. Transfer Optimizer annotations for query block SEL$F5BB74E1 ( # )
     

841. id = 0 frofkksm [ i ] ( sort - merge/hash ) predicate = "E" . "MGR" = "M" . "EMPNO"
     

842. id = 0 frosand ( sort - merge/hash ) predicate = "E" . "MGR" = "M" . "EMPNO"
     

843. id = 0 frofand predicate = "E" . "DEPTNO" = 20 AND "E" . "MGR" IS NOT NULL
     

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

845.   Best join order : 2
     

846.   Cost : 6 . 0029  Degree : 1  Card : 4 . 0000  Bytes : 92
     

847.   Resc : 6 . 0029  Resc_io : 5 . 0000  Resc_cpu : 21081349
     

848.   Resp : 6 . 0029  Resp_io : 5 . 0000  Resc_cpu : 21081349
     

849. kkoqbc - subheap ( delete addr = 0x2b043e8dfaa0 , in - use = 27216 , alloc = 32840 )
     

850. kkoqbc - end :
     

851.          :
     

852.      call ( in - use = 19488 , alloc = 82024 ) , compile ( in - use = 82560 , alloc = 83496 ) , execution ( in - use = 4072 , alloc = 8088 )
     

853. 
     

854. kkoqbc : finish optimizing query block SEL$F5BB74E1 ( # )
     

855. apadrv - end
     

856.            :
     

857.      call ( in - use = 19488 , alloc = 82024 ) , compile ( in - use = 83544 , alloc = 87640 ) , execution ( in - use = 4072 , alloc = 8088 )
     

858. 
     

859. 
     

860. Starting SQL statement dump
     

861. 
     

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

863. sql_id = 1mfmysf37dut4 plan_hash_value = 992080948 problem_type = 3
     

864. - - - - - Current SQL Statement for this session ( sql_id = 1mfmysf37dut4 ) - - - - -
     

865. select / * sample - 3 * /
     

866. employee , manager
     

867. from emp_mgr_view
     

868. where deptno = 20
     

869. sql_text_length = 75
     

870. sql = select / * sample - 3 * /
     

871. employee , manager
     

872. from emp_mgr_view
     

873. where deptno = 20
     

874. - - - - - Explain Plan Dump - - - - -
     

875. - - - - - Plan Table - - - - -
     

876.  
     

877. = = = = = = = = = = = =
     

878. Plan Table
     

879. = = = = = = = = = = = =
     

880. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

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

882. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

883. |   0 | SELECT STATEMENT               |          |         |         |      6 |            |
     

884. |   1 |   MERGE JOIN                    |          |      4 |     92 |      6 | 00 : 00 : 01 |
     

885. |   2 |    TABLE ACCESS BY INDEX ROWID | EMP     |     14 |    140 |      2 | 00 : 00 : 01 |
     

886. |   3 |     INDEX FULL SCAN             | PK_EMP |     14 |         |      1 | 00 : 00 : 01 |
     

887. |   4 |    SORT JOIN                   |          |      4 |    52 |      4 | 00 : 00 : 01 |
     

888. |   5 |     TABLE ACCESS FULL           | EMP     |      4 |     52 |      3 | 00 : 00 : 01 |
     

889. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
     

890. Predicate Information :
     

891. - - - - - - - - - - - - - - - - - - - - - -
     

892. 4 - access ( "E" . "MGR" = "M" . "EMPNO" )
     

893. 4 - filter ( "E" . "MGR" = "M" . "EMPNO" )
     

894. 5 - filter ( ( "E" . "DEPTNO" = 20 AND "E" . "MGR" IS NOT NULL ) )
     

895.  
     

896. Content of other_xml column
     

897. = = = = = = = = = = = = = = = = = = = = = = = = = = =
     

898.   db_version : 11 . 2 . . 3
     

899.   parse_schema : SCOTT
     

900.   plan_hash : 992080948
     

901.   plan_hash_2 : 1563151197
     

902.   Outline Data :
     

903.   / * +
     

904.     BEGIN_OUTLINE_DATA
     

905.       IGNORE_OPTIM_EMBEDDED_HINTS
     

906.       OPTIMIZER_FEATURES_ENABLE ( '11.2.0.3' )
     

907.       DB_VERSION ( '11.2.0.3' )
     

908.       ALL_ROWS
     

909.       OUTLINE_LEAF ( @ "SEL$F5BB74E1" )
     

910.       MERGE ( @ "SEL$2" )
     

911.       OUTLINE ( @ "SEL$1" )
     

912.       OUTLINE ( @ "SEL$2" )
     

913.       INDEX ( @ "SEL$F5BB74E1" "M" @ "SEL$2" ( "EMP" . "EMPNO" ) )
     

914.       FULL ( @ "SEL$F5BB74E1" "E" @ "SEL$2" )
     

915.       LEADING ( @ "SEL$F5BB74E1" "M" @ "SEL$2" "E" @ "SEL$2" )
     

916.       USE_MERGE ( @ "SEL$F5BB74E1" "E" @ "SEL$2" )
     

917.     END_OUTLINE_DATA
     

918.    * /
     

919.  
     

920. Optimizer state dump :
     

921. Compilation Environment Dump
     

922. optimizer_mode_hinted = false
     

923. ... ...

可能路徑上的join order成本錯誤可能影響後邊整個的執行計劃。
還是那句話做sql調優沒必要看10053，我們關注的是merge錯的時候，或者沒做、不應該做的做了。
特別是報表系統view多的系統、常常出現transformation錯的情況
為什麼hj cost最小/*+opt_param("_bloom_fillter_enabled","false") */
不是所有的引數都可以這麼指定

與簡單檢視合併相關的引數
Parameter： _simple_view_merging把這個引數關掉就不merge了
Hints： /*+opt_param('_bloom_simple_view_merging',"false") */
no_query_transformation   少動
merge,no_merge   少動

點選( 此處 )摺疊或開啟

1. scott@TESTDB12 > alter session set "_simple_view_merging" = false ;
   

2. 
   

3. Session altered .
   

4. 
   

5. select / * sample - 3 * /
   

6. employee , manager
   

7. from emp_mgr_view
   

8.   4  where deptno = 20 ;
   

9. 
   

10. EMPLOYEE   MANAGER
    

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

12. SCOTT       JONES
    

13. FORD       JONES
    

14. ADAMS       SCOTT
    

15. JONES       KING
    

16. SMITH       FORD

17.                                           

18. 
    

19. scott@TESTDB12 > select * from table ( dbms_xplan . display_cursor ( null , null , 'last' ) ) ;
    

20. 
    

21. PLAN_TABLE_OUTPUT
    

22. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

23. SQL_ID    1mfmysf37dut4 , child number 1
    

24. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

25. select / * sample - 3 * / employee , manager from emp_mgr_view where deptno =
    

26. 20
    

27. 
    

28. Plan hash value : 1461531747
    

29. 
    

30. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

31. | Id | Operation                       | Name           |    Rows |    Bytes | Cost ( % CPU ) |      Time |
    

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

33. |   0 | SELECT STATEMENT                |                |          |           |        6 ( 100 ) |            |
    

34. |   1 |   VIEW                           | EMP_MGR_VIEW |       4 |       64 |        6   ( 17 ) | 00 : 00 : 01 |
    

35. |  2 |    MERGE JOIN                    |                |       4 |       92 |        6   ( 17 ) | 00 : 00 : 01 |
    

36. |   3 |     TABLE ACCESS BY INDEX ROWID  | EMP           |       14 |      140 |        2    ( ) | 00 : 00 : 01 |
    

37. |   4 |      INDEX FULL SCAN             | PK_EMP        |       14 |           |        1    ( ) | 00 : 00 : 01 |
    

38. | * 5 |     SORT JOIN                    |                |       4 |       52 |        4   ( 25 ) | 00 : 00 : 01 |
    

39. | * 6 |      TABLE ACCESS FULL           | EMP           |       4 |       52 |        3    ( ) | 00 : 00 : 01 |
    

40. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

41. 
    

42. Predicate Information ( identified by operation id ) :
    

43. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

44. 
    

45.    5 - access ( "E" . "MGR" = "M" . "EMPNO" )
    

46.        filter ( "E" . MGR = M . EMPNO" )
    

47.    6 - filter ( ( E . DEPTNO = 20 AND E . MGR  IS NOT NULL ) )
    

48. 
    

這是關閉引數後的結果
有一些固定的原因是不做simple view merge的
1、view不是一個簡單的檢視
2、view出現在semi-j，anti-j的右邊（也不是一定的，需要關注一下）
3、view的select列表上包含子查詢。（也不是一定的，需要關注一下）

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