把subquery merge到它的外面的query(启发)
网上说exist要比in快之列的优化法则,但是至少oracle 10g后,这些事情oracle会去做,但是有时候不去做,所以要是能写成exist,那么就尽量写成exist。
点击( 此处 )折叠或打开
-
alter session set events '10053 trace name context forever,level 1';
-
select * from dept where deptno in ( select deptno from emp where sal > 3000 ) ;
-
alter session set events '10053 trace name context off';
点击( 此处 )折叠或打开
-
Trace file /u01/app/oracle/diag/rdbms/testdb12/TestDB12/trace/TestDB12_ora_27778 . trc
-
Oracle Database 11g Enterprise Edition Release 11 . 2 . . 3 . 0 - 64bit Production
-
With the Partitioning , Oracle Label Security , OLAP , Data Mining ,
-
Oracle Database Vault and Real Application Testing options
-
ORACLE_HOME = /u01/app/oracle/product/11 . 2 . 0/dbhome_1
-
System name : Linux
-
Node name : test11g
-
Release : 2 . 6 . 18 - 194 . el5
-
Version : #1 SMP Tue Mar 16 21 : 52 : 39 EDT 2010
-
Machine : x86_64
-
Instance name : TestDB12
-
Redo thread mounted by this instance : 1
-
Oracle process number : 32
-
Unix process pid : 27778 , image : oracle@test11g ( TNS V1 - V3 )
-
-
-
* * * 2015 - 02 - 27 03 : 28 : 42 . 034
-
* * * SESSION ID : ( 53 . 2993 ) 2015 - 02 - 27 03 : 28 : 42 . 034
-
* * * CLIENT ID : ( ) 2015 - 02 - 27 03 : 28 : 42 . 034
-
* * * SERVICE NAME : ( SYS$USERS ) 2015 - 02 - 27 03 : 28 : 42 . 034
-
* * * MODULE NAME : ( SQL * Plus ) 2015 - 02 - 27 03 : 28 : 42 . 034
-
* * * ACTION NAME : ( ) 2015 - 02 - 27 03 : 28 : 42 . 034
-
-
Registered qb : SEL$1 0x57631638 ( PARSER )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : qb_name = SEL$1 nbfros = 1 flg =
-
fro ( ) : flg = 4 objn = 75333 hint_alias = \ "DEPT\" @ \ "SEL$1\"
-
-
Registered qb : SEL$2 0x5763c120 ( PARSER )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : qb_name = SEL$2 nbfros = 1 flg =
-
fro ( ) : flg = 4 objn = 75335 hint_alias = \ "EMP\" @ \ "SEL$2\"
-
-
SPM : statement not found in SMB
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Automatic degree of parallelism ( ADOP )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Automatic degree of parallelism is disabled : Parameter .
-
-
PM : Considering predicate move - around in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Predicate Move - Around ( PM )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
OPTIMIZER INFORMATION
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
- - - - - Current SQL Statement for this session ( sql_id = dnkzmnvvdxgtm ) - - - - -
-
select * from dept where deptno in ( select deptno from emp where sal > 3000 )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Legend
-
The following abbreviations are used by optimizer trace .
-
CBQT - cost - based query transformation
-
JPPD - join predicate push - down
-
OJPPD - old - style ( non - cost - based ) JPPD
-
FPD - filter push - down
-
PM - predicate move - around
-
CVM - complex view merging
-
SPJ - select - project - join
-
SJC - set join conversion
-
SU - subquery unnesting
-
OBYE - order by elimination
-
OST - old style star transformation
-
ST - new ( cbqt ) star transformation
-
CNT - count ( col ) to count ( * ) transformation
-
JE - Join Elimination
-
JF - join factorization
-
SLP - select list pruning
-
DP - distinct placement
-
qb - query block
-
LB - leaf blocks
-
DK - distinct keys
-
LB/K - average number of leaf blocks per key
-
DB/K - average number of data blocks per key
-
CLUF - clustering factor
-
NDV - number of distinct values
-
Resp - response cost
-
Card - cardinality
-
Resc - resource cost
-
NL - nested loops ( join )
-
SM - sort merge ( join )
-
HA - hash ( join )
-
CPUSPEED - CPU Speed
-
IOTFRSPEED - I/O transfer speed
-
IOSEEKTIM - I/O seek time
-
SREADTIM - average single block read time
-
MREADTIM - average multiblock read time
-
MBRC - average multiblock read count
-
MAXTHR - maximum I/O system throughput
-
SLAVETHR - average slave I/O throughput
-
dmeth - distribution method
-
1 : no partitioning required
-
2 : value partitioned
-
4 : right is random ( round - robin )
-
128 : left is random ( round - robin )
-
8 : broadcast right and partition left
-
16 : broadcast left and partition right
-
32 : partition left using partitioning of right
-
64 : partition right using partitioning of left
-
256 : run the join in serial
-
0 : invalid distribution method
-
sel - selectivity
-
ptn - partition
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS USED BY THE OPTIMIZER
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS WITH ALTERED VALUES
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Compilation Environment Dump
-
Bug Fix Control Environment
-
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS WITH DEFAULT VALUES
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Compilation Environment Dump
-
optimizer_mode_hinted = false
-
optimizer_features_hinted = 0 . .
-
parallel_execution_enabled = true
-
parallel_query_forced_dop = 0
-
parallel_dml_forced_dop = 0
-
parallel_ddl_forced_degree = 0
-
parallel_ddl_forced_instances = 0
-
_query_rewrite_fudge = 90
-
optimizer_features_enable = 11 . 2 . . 3
-
_optimizer_search_limit = 5
-
cpu_count = 1
-
active_instance_count = 1
-
parallel_threads_per_cpu = 2
-
hash_area_size = 131072
-
bitmap_merge_area_size = 1048576
-
sort_area_size = 65536
-
sort_area_retained_size = 0
-
_sort_elimination_cost_ratio = 0
-
_optimizer_block_size = 8192
-
_sort_multiblock_read_count = 2
-
_hash_multiblock_io_count = 0
-
_db_file_optimizer_read_count = 8
-
_optimizer_max_permutations = 2000
-
pga_aggregate_target = 286720 KB
-
_pga_max_size = 204800 KB
-
_query_rewrite_maxdisjunct = 257
-
_smm_auto_min_io_size = 56 KB
-
_smm_auto_max_io_size = 248 KB
-
_smm_min_size = 286 KB
-
_smm_max_size = 57344 KB
-
_smm_px_max_size = 143360 KB
-
_cpu_to_io = 0
-
_optimizer_undo_cost_change = 11 . 2 . . 3
-
parallel_query_mode = enabled
-
parallel_dml_mode = disabled
-
parallel_ddl_mode = enabled
-
optimizer_mode = all_rows
-
sqlstat_enabled = false
-
_optimizer_percent_parallel = 101
-
_always_anti_join = choose
-
_always_semi_join = choose
-
_optimizer_mode_force = true
-
_partition_view_enabled = true
-
_always_star_transformation = false
-
_query_rewrite_or_error = false
-
_hash_join_enabled = true
-
cursor_sharing = exact
-
_b_tree_bitmap_plans = true
-
star_transformation_enabled = false
-
_optimizer_cost_model = choose
-
_new_sort_cost_estimate = true
-
_complex_view_merging = true
-
_unnest_subquery = true
-
_eliminate_common_subexpr = true
-
_pred_move_around = true
-
_convert_set_to_join = false
-
_push_join_predicate = true
-
_push_join_union_view = true
-
_fast_full_scan_enabled = true
-
_optim_enhance_nnull_detection = true
-
_parallel_broadcast_enabled = true
-
_px_broadcast_fudge_factor = 100
-
_ordered_nested_loop = true
-
_no_or_expansion = false
-
optimizer_index_cost_adj = 100
-
optimizer_index_caching = 0
-
_system_index_caching = 0
-
_disable_datalayer_sampling = false
-
query_rewrite_enabled = true
-
query_rewrite_integrity = enforced
-
_query_cost_rewrite = true
-
_query_rewrite_2 = true
-
_query_rewrite_1 = true
-
_query_rewrite_expression = true
-
_query_rewrite_jgmigrate = true
-
_query_rewrite_fpc = true
-
_query_rewrite_drj = true
-
_full_pwise_join_enabled = true
-
_partial_pwise_join_enabled = true
-
_left_nested_loops_random = true
-
_improved_row_length_enabled = true
-
_index_join_enabled = true
-
_enable_type_dep_selectivity = true
-
_improved_outerjoin_card = true
-
_optimizer_adjust_for_nulls = true
-
_optimizer_degree = 0
-
_use_column_stats_for_function = true
-
_subquery_pruning_enabled = true
-
_subquery_pruning_mv_enabled = false
-
_or_expand_nvl_predicate = true
-
_like_with_bind_as_equality = false
-
_table_scan_cost_plus_one = true
-
_cost_equality_semi_join = true
-
_default_non_equality_sel_check = true
-
_new_initial_join_orders = true
-
_oneside_colstat_for_equijoins = true
-
_optim_peek_user_binds = true
-
_minimal_stats_aggregation = true
-
_force_temptables_for_gsets = false
-
workarea_size_policy = auto
-
_smm_auto_cost_enabled = true
-
_gs_anti_semi_join_allowed = true
-
_optim_new_default_join_sel = true
-
optimizer_dynamic_sampling = 2
-
_pre_rewrite_push_pred = true
-
_optimizer_new_join_card_computation = true
-
_union_rewrite_for_gs = yes_gset_mvs
-
_generalized_pruning_enabled = true
-
_optim_adjust_for_part_skews = true
-
_force_datefold_trunc = false
-
statistics_level = typical
-
_optimizer_system_stats_usage = true
-
skip_unusable_indexes = true
-
_remove_aggr_subquery = true
-
_optimizer_push_down_distinct = 0
-
_dml_monitoring_enabled = true
-
_optimizer_undo_changes = false
-
_predicate_elimination_enabled = true
-
_nested_loop_fudge = 100
-
_project_view_columns = true
-
_local_communication_costing_enabled = true
-
_local_communication_ratio = 50
-
_query_rewrite_vop_cleanup = true
-
_slave_mapping_enabled = true
-
_optimizer_cost_based_transformation = linear
-
_optimizer_mjc_enabled = true
-
_right_outer_hash_enable = true
-
_spr_push_pred_refspr = true
-
_optimizer_cache_stats = false
-
_optimizer_cbqt_factor = 50
-
_optimizer_squ_bottomup = true
-
_fic_area_size = 131072
-
_optimizer_skip_scan_enabled = true
-
_optimizer_cost_filter_pred = false
-
_optimizer_sortmerge_join_enabled = true
-
_optimizer_join_sel_sanity_check = true
-
_mmv_query_rewrite_enabled = true
-
_bt_mmv_query_rewrite_enabled = true
-
_add_stale_mv_to_dependency_list = true
-
_distinct_view_unnesting = false
-
_optimizer_dim_subq_join_sel = true
-
_optimizer_disable_strans_sanity_checks = 0
-
_optimizer_compute_index_stats = true
-
_push_join_union_view2 = true
-
_optimizer_ignore_hints = false
-
_optimizer_random_plan = 0
-
_query_rewrite_setopgrw_enable = true
-
_optimizer_correct_sq_selectivity = true
-
_disable_function_based_index = false
-
_optimizer_join_order_control = 3
-
_optimizer_cartesian_enabled = true
-
_optimizer_starplan_enabled = true
-
_extended_pruning_enabled = true
-
_optimizer_push_pred_cost_based = true
-
_optimizer_null_aware_antijoin = true
-
_optimizer_extend_jppd_view_types = true
-
_sql_model_unfold_forloops = run_time
-
_enable_dml_lock_escalation = false
-
_bloom_filter_enabled = true
-
_update_bji_ipdml_enabled = 0
-
_optimizer_extended_cursor_sharing = udo
-
_dm_max_shared_pool_pct = 1
-
_optimizer_cost_hjsmj_multimatch = true
-
_optimizer_transitivity_retain = true
-
_px_pwg_enabled = true
-
optimizer_secure_view_merging = true
-
_optimizer_join_elimination_enabled = true
-
flashback_table_rpi = non_fbt
-
_optimizer_cbqt_no_size_restriction = true
-
_optimizer_enhanced_filter_push = true
-
_optimizer_filter_pred_pullup = true
-
_rowsrc_trace_level = 0
-
_simple_view_merging = true
-
_optimizer_rownum_pred_based_fkr = true
-
_optimizer_better_inlist_costing = all
-
_optimizer_self_induced_cache_cost = false
-
_optimizer_min_cache_blocks = 10
-
_optimizer_or_expansion = depth
-
_optimizer_order_by_elimination_enabled = true
-
_optimizer_outer_to_anti_enabled = true
-
_selfjoin_mv_duplicates = true
-
_dimension_skip_null = true
-
_force_rewrite_enable = false
-
_optimizer_star_tran_in_with_clause = true
-
_optimizer_complex_pred_selectivity = true
-
_optimizer_connect_by_cost_based = true
-
_gby_hash_aggregation_enabled = true
-
_globalindex_pnum_filter_enabled = true
-
_px_minus_intersect = true
-
_fix_control_key = 0
-
_force_slave_mapping_intra_part_loads = false
-
_force_tmp_segment_loads = false
-
_query_mmvrewrite_maxpreds = 10
-
_query_mmvrewrite_maxintervals = 5
-
_query_mmvrewrite_maxinlists = 5
-
_query_mmvrewrite_maxdmaps = 10
-
_query_mmvrewrite_maxcmaps = 20
-
_query_mmvrewrite_maxregperm = 512
-
_query_mmvrewrite_maxqryinlistvals = 500
-
_disable_parallel_conventional_load = false
-
_trace_virtual_columns = false
-
_replace_virtual_columns = true
-
_virtual_column_overload_allowed = true
-
_kdt_buffering = true
-
_first_k_rows_dynamic_proration = true
-
_optimizer_sortmerge_join_inequality = true
-
_optimizer_aw_stats_enabled = true
-
_bloom_pruning_enabled = true
-
result_cache_mode = MANUAL
-
_px_ual_serial_input = true
-
_optimizer_skip_scan_guess = false
-
_enable_row_shipping = true
-
_row_shipping_threshold = 80
-
_row_shipping_explain = false
-
transaction_isolation_level = read_commited
-
_optimizer_distinct_elimination = true
-
_optimizer_multi_level_push_pred = true
-
_optimizer_group_by_placement = true
-
_optimizer_rownum_bind_default = 10
-
_enable_query_rewrite_on_remote_objs = true
-
_optimizer_extended_cursor_sharing_rel = simple
-
_optimizer_adaptive_cursor_sharing = true
-
_direct_path_insert_features = 0
-
_optimizer_improve_selectivity = true
-
optimizer_use_pending_statistics = false
-
_optimizer_enable_density_improvements = true
-
_optimizer_aw_join_push_enabled = true
-
_optimizer_connect_by_combine_sw = true
-
_enable_pmo_ctas = 0
-
_optimizer_native_full_outer_join = force
-
_bloom_predicate_enabled = true
-
_optimizer_enable_extended_stats = true
-
_is_lock_table_for_ddl_wait_lock = 0
-
_pivot_implementation_method = choose
-
optimizer_capture_sql_plan_baselines = false
-
optimizer_use_sql_plan_baselines = true
-
_optimizer_star_trans_min_cost = 0
-
_optimizer_star_trans_min_ratio = 0
-
_with_subquery = OPTIMIZER
-
_optimizer_fkr_index_cost_bias = 10
-
_optimizer_use_subheap = true
-
parallel_degree_policy = manual
-
parallel_degree = 0
-
parallel_min_time_threshold = 10
-
_parallel_time_unit = 10
-
_optimizer_or_expansion_subheap = true
-
_optimizer_free_transformation_heap = true
-
_optimizer_reuse_cost_annotations = true
-
_result_cache_auto_size_threshold = 100
-
_result_cache_auto_time_threshold = 1000
-
_optimizer_nested_rollup_for_gset = 100
-
_nlj_batching_enabled = 1
-
parallel_query_default_dop = 0
-
is_recur_flags = 0
-
optimizer_use_invisible_indexes = false
-
flashback_data_archive_internal_cursor = 0
-
_optimizer_extended_stats_usage_control = 192
-
_parallel_syspls_obey_force = true
-
cell_offload_processing = true
-
_rdbms_internal_fplib_enabled = false
-
db_file_multiblock_read_count = 97
-
_bloom_folding_enabled = true
-
_mv_generalized_oj_refresh_opt = true
-
cell_offload_compaction = ADAPTIVE
-
cell_offload_plan_display = AUTO
-
_bloom_predicate_pushdown_to_storage = true
-
_bloom_vector_elements = 0
-
_bloom_pushing_max = 512
-
parallel_degree_limit = 65535
-
parallel_force_local = false
-
parallel_max_degree = 2
-
total_cpu_count = 1
-
_optimizer_coalesce_subqueries = true
-
_optimizer_fast_pred_transitivity = true
-
_optimizer_fast_access_pred_analysis = true
-
_optimizer_unnest_disjunctive_subq = true
-
_optimizer_unnest_corr_set_subq = true
-
_optimizer_distinct_agg_transform = true
-
_aggregation_optimization_settings = 0
-
_optimizer_connect_by_elim_dups = true
-
_optimizer_eliminate_filtering_join = true
-
_connect_by_use_union_all = true
-
dst_upgrade_insert_conv = true
-
advanced_queuing_internal_cursor = 0
-
_optimizer_unnest_all_subqueries = true
-
parallel_autodop = 0
-
parallel_ddldml = 0
-
_parallel_cluster_cache_policy = adaptive
-
_parallel_scalability = 50
-
iot_internal_cursor = 0
-
_optimizer_instance_count = 0
-
_optimizer_connect_by_cb_whr_only = false
-
_suppress_scn_chk_for_cqn = nosuppress_1466
-
_optimizer_join_factorization = true
-
_optimizer_use_cbqt_star_transformation = true
-
_optimizer_table_expansion = true
-
_and_pruning_enabled = true
-
_deferred_constant_folding_mode = DEFAULT
-
_optimizer_distinct_placement = true
-
partition_pruning_internal_cursor = 0
-
parallel_hinted = none
-
_sql_compatibility = 0
-
_optimizer_use_feedback = true
-
_optimizer_try_st_before_jppd = true
-
_dml_frequency_tracking = false
-
_optimizer_interleave_jppd = true
-
kkb_drop_empty_segments = 0
-
_px_partition_scan_enabled = true
-
_px_partition_scan_threshold = 64
-
_optimizer_false_filter_pred_pullup = true
-
_bloom_minmax_enabled = true
-
only_move_row = 0
-
_optimizer_enable_table_lookup_by_nl = true
-
deferred_segment_creation = true
-
_optimizer_filter_pushdown = true
-
_allow_level_without_connect_by = false
-
_max_rwgs_groupings = 8192
-
_optimizer_outer_join_to_inner = true
-
_optimizer_full_outer_join_to_outer = true
-
_px_numa_support_enabled = true
-
total_processor_group_count = 1
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS IN OPT_PARAM HINT
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Column Usage Monitoring is ON : tracking level = 1
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
Considering Query Transformations on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Query transformations ( QT )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
JF : Checking validity of join factorization for query block SEL$2 ( # )
-
JF : Bypassed : not a UNION or UNION - ALL query block .
-
ST : not valid since star transformation parameter is FALSE
-
TE : Checking validity of table expansion for query block SEL$2 ( # )
-
TE : Bypassed : No partitioned table in query block .
-
CBQT : Validity checks passed for dnkzmnvvdxgtm .
-
CSE : Considering common sub - expression elimination in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$2 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : CSE not performed on query block SEL$2 ( # ) .
-
CSE : CSE not performed on query block SEL$1 ( # ) .
-
OBYE : Considering Order - by Elimination from view SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Order - by elimination ( OBYE )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
OBYE : OBYE bypassed : no order by to eliminate .
-
OJE : Begin : find best directive for query block SEL$1 ( # )
-
OJE : End : finding best directive for query block SEL$1 ( # )
-
OJE : Begin : find best directive for query block SEL$2 ( # )
-
OJE : End : finding best directive for query block SEL$2 ( # )
-
query block SEL$1 ( # ) unchanged
-
Considering Query Transformations on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Query transformations ( QT )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$2 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : CSE not performed on query block SEL$2 ( # ) .
-
CSE : CSE not performed on query block SEL$1 ( # ) .
-
query block SEL$1 ( # ) unchanged
-
apadrv - start sqlid = 15729934021953503027
-
:
-
call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 62144 , alloc = 62632 ) , execution ( in - use = 3456 , alloc = 4032 )
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Peeked values of the binds in SQL statement
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
CBQT : Considering cost - based transformation on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
COST - BASED QUERY TRANSFORMATIONS
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
FPD : Considering simple filter push ( pre rewrite ) in query block SEL$2 ( # )
-
FPD : Current where clause predicates \ "EMP\" . \ "SAL\" > 3000
-
-
FPD : Considering simple filter push ( pre rewrite ) in query block SEL$1 ( # )
-
FPD : Current where clause predicates \ "DEPT\" . \ "DEPTNO\" = ANY ( SELECT \ "EMP\" . \ "DEPTNO\" FROM \ "EMP\" \ "EMP\" )
-
-
OBYE : Considering Order - by Elimination from view SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Order - by elimination ( OBYE )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
OBYE : OBYE bypassed : no order by to eliminate .
-
Considering Query Transformations on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Query transformations ( QT )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$2 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : CSE not performed on query block SEL$2 ( # ) .
-
CSE : CSE not performed on query block SEL$1 ( # ) .
-
kkqctdrvTD - start on query block SEL$1 ( # )
-
kkqctdrvTD - start : :
-
call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 104584 , alloc = 108008 ) , execution ( in - use = 3456 , alloc = 4032 )
-
-
Registered qb : SEL$1 0x576c9f98 ( COPY SEL$1 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : NULL
-
Registered qb : SEL$2 0x576ca878 ( COPY SEL$2 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : NULL
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Cost - Based Subquery Unnesting
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
SU : Unnesting query blocks in query block SEL$1 ( # 1 ) that are valid to unnest .
-
Subquery removal for query block SEL$2 ( # 2 )
-
RSW : Not valid for subquery removal SEL$2 ( # 2 )
-
Subquery unchanged .
-
Subquery Unnesting on query block SEL$1 ( # 1 ) SU : Performing unnesting that does not require costing .
-
SU : Considering subquery unnest on query block SEL$1 ( # 1 ) .
-
SU : Checking validity of unnesting subquery SEL$2 ( # 2 )
-
SU : Passed validity checks .
-
SU : Transforming ANY subquery to a join .
-
Registered qb : SEL$5DA710D3 0x576c9f98 ( SUBQUERY UNNEST SEL$1 ; SEL$2 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : qb_name = SEL$5DA710D3 nbfros = 2 flg =
-
fro ( ) : flg = 0 objn = 75333 hint_alias = \ "DEPT\" @ \ "SEL$1\"
-
fro ( 1 ) : flg = 0 objn = 75335 hint_alias = \ "EMP\" @ \ "SEL$2\"
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Cost - Based Complex View Merging
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
CVM : Finding query blocks in query block SEL$5DA710D3 ( # 1 ) that are valid to merge .
-
OJE : Begin : find best directive for query block SEL$5DA710D3 ( # 1 )
-
OJE : End : finding best directive for query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - cleanup : transform ( in - use = 4792 , alloc = 8392 ) :
-
call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 120856 , alloc = 131696 ) , execution ( in - use = 3496 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 112680 , alloc = 131696 ) , execution ( in - use = 3496 , alloc = 4032 )
-
-
Subquery removal for query block SEL$2 ( # 2 )
-
RSW : Not valid for subquery removal SEL$2 ( # 2 )
-
Subquery unchanged .
-
SU : Transforming ANY subquery to a join .
-
SJC : Considering set - join conversion in query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Set - Join Conversion ( SJC )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
SJC : not performed
-
OJE : Begin : find best directive for query block SEL$5DA710D3 ( # 1 )
-
OJE : End : finding best directive for query block SEL$5DA710D3 ( # 1 )
-
JE : Considering Join Elimination on query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Join Elimination ( JE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT DEPT . DEPTNO DEPTNO , DEPT . DNAME DNAME , DEPT . LOC LOC FROM SCOTT . EMP EMP , SCOTT . DEPT DEPT WHERE DEPT . DEPTNO = EMP . DEPTNO AND EMP . SAL > 3000
-
JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
-
JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
-
JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
-
JE : cfro : EMP objn : 75333 col# : 8 dfro : DEPT dcol# : 1
-
JE : cfro : DEPT objn : 75335 col# : 1 dfro : EMP dcol# : 8
-
SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT DEPT . DEPTNO DEPTNO , DEPT . DNAME DNAME , DEPT . LOC LOC FROM SCOTT . EMP EMP , SCOTT . DEPT DEPT WHERE DEPT . DEPTNO = EMP . DEPTNO AND EMP . SAL > 3000
-
Query block SEL$5DA710D3 ( # 1 ) unchanged
-
PM : Considering predicate move - around in query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Predicate Move - Around ( PM )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
PM : PM bypassed : Outer query contains no views .
-
PM : PM bypassed : Outer query contains no views .
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114000 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114632 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114952 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 114952 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 115560 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 115880 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 115880 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
TE : Checking validity of table expansion for query block SEL$5DA710D3 ( # 1 )
-
TE : Bypassed : No partitioned table in query block .
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 116488 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 116808 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
TE : Checking validity of table expansion for query block SEL$5DA710D3 ( # 1 )
-
TE : Bypassed : No partitioned table in query block .
-
ST : Query in kkqstardrv : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT DEPT . DEPTNO DEPTNO , DEPT . DNAME DNAME , DEPT . LOC LOC FROM SCOTT . EMP EMP , SCOTT . DEPT DEPT WHERE DEPT . DEPTNO = EMP . DEPTNO AND EMP . SAL > 3000
-
ST : not valid since star transformation parameter is FALSE
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 116808 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
JF : Checking validity of join factorization for query block SEL$5DA710D3 ( # 1 )
-
JF : Bypassed : not a UNION or UNION - ALL query block .
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 117416 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 117736 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
JPPD : Considering Cost - based predicate pushdown from query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Cost - based predicate pushdown ( JPPD )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 117736 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 118344 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 118664 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
JPPD : Applying transformation directives
-
query block SEL$1 transformed to SEL$5DA710D3 ( # 1 )
-
FPD : Considering simple filter push in query block SEL$5DA710D3 ( # 1 )
-
DEPT . DEPTNO = EMP . DEPTNO AND EMP . SAL > 3000
-
try to generate transitive predicate from check constraints for query block SEL$5DA710D3 ( # 1 )
-
finally : DEPT . DEPTNO = EMP . DEPTNO AND EMP . SAL > 3000
-
-
Final query after transformations : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT DEPT . DEPTNO DEPTNO, DEPT . DNAME DNAME , DEPT . LOC LOC FROM SCOTT . EMP EMP , SCOTT . DEPT DEPT WHERE DEPT . DEPTNO = EMP . DEPTNO AND EMP . SAL > 3000
-
kkoqbc : optimizing query block SEL$5DA710D3 ( # 1 )
-
-
:
-
call ( in - use = 3952 , alloc = 16344 ) , compile ( in - use = 119968 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkoqbc - subheap ( create addr = 0x2b265763a658 )
-
* * * * * * * * * * * * * * * *
-
QUERY BLOCK TEXT
-
* * * * * * * * * * * * * * * *
-
select * from dept where deptno in ( select deptno from emp where sal > 3000 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( optimizer ) : qb_name = SEL$5DA710D3 nbfros = 2 flg =
-
fro ( ) : flg = 0 objn = 75333 hint_alias = \ "DEPT\" @ \ "SEL$1\"
-
fro ( 1 ) : flg = 0 objn = 75335 hint_alias = \ "EMP\" @ \ "SEL$2\"
-
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
SYSTEM STATISTICS INFORMATION
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
Using NOWORKLOAD Stats
-
CPUSPEEDNW : 1752 millions instructions/sec ( default is 100 )
-
IOTFRSPEED : 4096 bytes per millisecond ( default is 4096 )
-
IOSEEKTIM : 10 milliseconds ( default is 10 )
-
MBRC : NO VALUE blocks ( default is 8 )
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
BASE STATISTICAL INFORMATION
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Table Stats : :
-
Table : DEPT Alias : DEPT
-
#Rows : 4 #Blks : 5 AvgRowLen : 20 . 00 ChainCnt : 0 . 00
-
Column ( # 1 ) : DEPTNO (
-
AvgLen : 3 NDV : 4 Nulls : 0 Density : 0 . 250000 Min : 10 Max : 40
-
Index Stats : :
-
Index : PK_DEPT Col# : 1
-
LVLS : 0 #LB : 1 #DK : 4 LB/K : 1 . 00 DB/K : 1 . 00 CLUF : 1 . 00
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Table Stats : :
-
Table : EMP Alias : EMP
-
#Rows : 14 #Blks : 5 AvgRowLen : 38 . 00 ChainCnt : 0 . 00
-
Column ( # 8 ) : DEPTNO (
-
AvgLen : 3 NDV : 3 Nulls : 0 Density : 0 . 333333 Min : 10 Max : 30
-
Index Stats : :
-
Index : PK_EMP Col# : 1
-
LVLS : 0 #LB : 1 #DK : 14 LB/K : 1 . 00 DB/K : 1 . 00 CLUF : 1 . 00
-
Access path analysis for EMP
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP [ EMP ]
-
Column ( # 6 ) : SAL (
-
AvgLen : 4 NDV : 12 Nulls : 0 Density : 0 . 083333 Min : 800 Max : 5000
-
Table : EMP Alias : EMP
-
Card : Original : 14 . 000000 Rounded : 7 Computed : 6 . 67 Non Adjusted : 6 . 67
-
Access Path : TableScan
-
Cost : 3 . 00 Resp : 3 . 00 Degree : 0
-
Cost_io : 3 . 00 Cost_cpu : 40087
-
Resp_io : 3 . 00 Resp_cpu : 40087
-
Best : : AccessPath : TableScan
-
Cost : 3 . 00 Degree : 1 Resp : 3 . 00 Card : 6 . 67 Bytes : 0
-
-
Access path analysis for DEPT
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for DEPT [ DEPT ]
-
Table : DEPT Alias : DEPT
-
Card : Original : 4 . 000000 Rounded : 4 Computed : 4 . 00 Non Adjusted : 4 . 00
-
Access Path : TableScan
-
Cost : 3 . 00 Resp : 3 . 00 Degree : 0
-
Cost_io : 3 . 00 Cost_cpu : 36367
-
Resp_io : 3 . 00 Resp_cpu : 36367
-
Best : : AccessPath : TableScan
-
Cost : 3 . 00 Degree : 1 Resp : 3 . 00 Card : 4 . 00 Bytes : 0
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
-
OPTIMIZER STATISTICS AND COMPUTATIONS
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
GENERAL PLANS
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Considering cardinality - based initial join order .
-
Permutations for Starting Table :
-
Join order [ 1 ] : DEPT [ DEPT ] # 0 EMP [ EMP ] # 1
-
-
* * * * * * * * * * * * * * *
-
Now joining : EMP [ EMP ] # 1
-
* * * * * * * * * * * * * * *
-
NL Join
-
Outer table : Card : 4 . 00 Cost : 3 . 00 Resp : 3 . 00 Degree : 1 Bytes : 20
-
Access path analysis for EMP
-
Inner table : EMP Alias : EMP
-
Access Path : TableScan
-
NL Join : Cost : 10 . 01 Resp : 10 . 01 Degree : 1
-
Cost_io : 10 . 00 Cost_cpu : 197836
-
Resp_io : 10 . 00 Resp_cpu : 197836
-
-
Best NL cost : 10 . 01
-
resc : 10 . 01 resc_io : 10 . 00 resc_cpu : 197836
-
resp : 10 . 01 resp_io : 10 . 00 resc_cpu : 197836
-
> > > adjusting AJ/SJ sel based on min/max ranges : jsel = min ( . 750000 , 0 . 666667 )
-
Semi Join Card : 2 . 666667 = outer ( 4 . 000000 ) * sel ( . 666667 )
-
Join Card - Rounded : 3 Computed : 2 . 67
-
Outer table : DEPT Alias : DEPT
-
resc : 3 . 00 card 4 . 00 bytes : 20 deg : 1 resp : 3 . 00
-
Inner table : EMP Alias : EMP
-
resc : 3 . 00 card : 6 . 67 bytes : 7 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 32 Total Rows : 4
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021466
-
Total Temp space used : 0
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 18 Total Rows : 7
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021991
-
Total Temp space used : 0
-
SM join : Resc : 8 . 00 Resp : 8 . 00 [ multiMatchCost = . 00 ]
-
SM Join
-
SM cost : 8 . 00
-
resc : 8 . 00 resc_io : 6 . 00 resc_cpu : 42119911
-
resp : 8 . 00 resp_io : 6 . 00 resp_cpu : 42119911
-
SM Join ( with index on outer )
-
Access Path : index ( FullScan )
-
Index : PK_DEPT
-
resc_io : 2 . 00 resc_cpu : 15803
-
ix_sel : 1 . 000000 ix_sel_with_filters : 1 . 000000
-
Cost : 2 . 00 Resp : 2 . 00 Degree : 1
-
Outer table : DEPT Alias : DEPT
-
resc : 2 . 00 card 4 . 00 bytes : 20 deg : 1 resp : 2 . 00
-
Inner table : EMP Alias : EMP
-
resc : 3 . 00 card : 6 . 67 bytes : 7 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 18 Total Rows : 7
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021991
-
Total Temp space used : 0
-
SM join : Resc : 6 . 00 Resp : 6 . 00 [ multiMatchCost = . 00 ]
-
Outer table : DEPT Alias : DEPT
-
resc : 3 . 00 card 4 . 00 bytes : 20 deg : 1 resp : 3 . 00
-
Inner table : EMP Alias : EMP
-
resc : 3 . 00 card : 6 . 67 bytes : 7 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
Cost per ptn : 0 . 50 #ptns : 1
-
hash_area : 124 ( max = 14336 ) buildfrag : 1 probefrag : 1 ppasses : 1
-
Hash join : Resc : 6 . 50 Resp : 6 . 50 [ multiMatchCost = . 00 ]
-
HA Join
-
HA cost : 6 . 50
-
resc : 6 . 50 resc_io : 6 . 00 resc_cpu : 10588307
-
resp : 6 . 50 resp_io : 6 . 00 resp_cpu : 10588307
-
Best : : JoinMethod : SortMergeSemi
-
Cost : 6 . 00 Degree : 1 Resp : 6 . 00 Card : 2 . 67 Bytes : 27
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Best so far : Table# : 0 cost : 2 . 0008 card : 4 . 0000 bytes : 80
-
Table# : 1 cost : 6 . 0027 card : 2 . 6667 bytes : 81
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Join order [ 2 ] : EMP [ EMP ] # 1 DEPT [ DEPT ] #
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 18 Total Rows : 7
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021991
-
Total Temp space used : 0
-
-
* * * * * * * * * * * * * * *
-
Now joining : DEPT [ DEPT ] #
-
* * * * * * * * * * * * * * *
-
NL Join
-
Outer table : Card : 3 . 00 Cost : 4 . 00 Resp : 4 . 00 Degree : 1 Bytes : 7
-
Access path analysis for DEPT
-
Inner table : DEPT Alias : DEPT
-
Access Path : TableScan
-
NL Join : Cost : 10 . 01 Resp : 10 . 01 Degree : 1
-
Cost_io : 9 . 00 Cost_cpu : 21171180
-
Resp_io : 9 . 00 Resp_cpu : 21171180
-
Access Path : index ( UniqueScan )
-
Index : PK_DEPT
-
resc_io : 1 . 00 resc_cpu : 8361
-
ix_sel : 0 . 250000 ix_sel_with_filters : 0 . 250000
-
NL Join : Cost : 7 . 00 Resp : 7 . 00 Degree : 1
-
Cost_io : 6 . 00 Cost_cpu : 21087162
-
Resp_io : 6 . 00 Resp_cpu : 21087162
-
Access Path : index ( AllEqUnique )
-
Index : PK_DEPT
-
resc_io : 1 . 00 resc_cpu : 8361
-
ix_sel : 0 . 250000 ix_sel_with_filters : 0 . 250000
-
NL Join : Cost : 7 . 00 Resp : 7 . 00 Degree : 1
-
Cost_io : 6 . 00 Cost_cpu : 21087162
-
Resp_io : 6 . 00 Resp_cpu : 21087162
-
-
Best NL cost : 7 . 00
-
resc : 7 . 00 resc_io : 6 . 00 resc_cpu : 21087162
-
resp : 7 . 00 resp_io : 6 . 00 resc_cpu : 21087162
-
Join Card : 3 . 000000 = outer ( 3 . 000000 ) * inner ( 4 . 000000 ) * sel ( . 250000 )
-
Join Card - Rounded : 3 Computed : 3 . 00
-
Outer table : EMP Alias : EMP
-
resc : 4 . 00 card 3 . 00 bytes : 7 deg : 1 resp : 4 . 00
-
Inner table : DEPT Alias : DEPT
-
resc : 3 . 00 card : 4 . 00 bytes : 20 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 18 Total Rows : 3
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021320
-
Total Temp space used : 0
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 32 Total Rows : 4
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021466
-
Total Temp space used : 0
-
SM join : Resc : 9 . 00 Resp : 9 . 00 [ multiMatchCost = . 00 ]
-
SM Join
-
SM cost : 9 . 00
-
resc : 9 . 00 resc_io : 6 . 00 resc_cpu : 63141231
-
resp : 9 . 00 resp_io : 6 . 00 resp_cpu : 63141231
-
Outer table : EMP Alias : EMP
-
resc : 4 . 00 card 3 . 00 bytes : 7 deg : 1 resp : 4 . 00
-
Inner table : DEPT Alias : DEPT
-
resc : 3 . 00 card : 4 . 00 bytes : 20 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
Cost per ptn : 0 . 50 #ptns : 1
-
hash_area : 124 ( max = 14336 ) buildfrag : 1 probefrag : 1 ppasses : 1
-
Hash join : Resc : 7 . 50 Resp : 7 . 50 [ multiMatchCost = . 00 ]
-
HA Join
-
HA cost : 7 . 50
-
resc : 7 . 50 resc_io : 6 . 00 resc_cpu : 31609848
-
resp : 7 . 50 resp_io : 6 . 00 resp_cpu : 31609848
-
Plan cardinality mismatch : best card = 2 . 66666666667 curr card = 3 . 00000000000
-
Join order aborted : cost > best plan cost
-
* * * * * * * * * * * * * * * * * * * * * * *
-
( newjo - stop - 1 ) k : , spcnt : , perm : 2 , maxperm : 2000
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Number of join permutations tried : 2
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Consider using bloom filter between DEPT [ DEPT ] and EMP [ EMP ] with ? ?
-
kkoBloomFilter : join ( lcdn : 4 rcdn : 7 jcdn : 3 limit : 13 )
-
Computing bloom ndv for creator : DEPT [ DEPT ] ccdn : 4 . 0 and user : EMP [ EMP ] ucdn : 6 . 7
-
kkopqComputeBloomNdv : predicate ( bndv : 4 ndv : 4 ) and ( bndv : 3 ndv : 3 )
-
kkopqComputeBloomNdv : pred cnt : 2 ndv : 4 reduction : 1
-
kkoBloomFilter : join ndv : 0 reduction : . 600000 ( limit : . 500000 ) rejected because distinct value ratio
-
Enumerating distribution method ( advanced )
-
- - - Distribution method for
-
join between DEPT [ DEPT ] ( serial ) and EMP [ EMP ] ( serial ) ; jm = 8 ; right side access path = TableScan
-
- - - - serial Sort - Merge - Join - > NONE
-
-
( newjo - save ) [ 0 1 ]
-
Trying or - Expansion on query block SEL$5DA710D3 ( # 1 )
-
Transfer Optimizer annotations for query block SEL$5DA710D3 ( # 1 )
-
id = 0 frofkksm [ i ] ( sort - merge/hash ) predicate = DEPT . DEPTNO = EMP . DEPTNO
-
id = 0 frosand ( sort - merge/hash ) predicate = DEPT . DEPTNO = EMP . DEPTNO
-
id = 0 frofand predicate = EMP . SAL > 3000
-
Final cost for query block SEL$5DA710D3 ( # 1 ) - All Rows Plan :
-
Best join order : 1
-
Cost : 6 . 0027 Degree : 1 Card : 3 . 0000 Bytes : 81
-
Resc : 6 . 0027 Resc_io : 5 . 0000 Resc_cpu : 21077881
-
Resp : 6 . 0027 Resp_io : 5 . 0000 Resc_cpu : 21077881
-
kkoqbc - subheap ( delete addr = 0x2b265763a658 , in - use = 26368 , alloc = 32840 )
-
kkoqbc - end :
-
:
-
call ( in - use = 19256 , alloc = 65656 ) , compile ( in - use = 123008 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
kkoqbc : finish optimizing query block SEL$5DA710D3 ( # 1 )
-
apadrv - end
-
:
-
call ( in - use = 19256 , alloc = 65656 ) , compile ( in - use = 124008 , alloc = 131696 ) , execution ( in - use = 3704 , alloc = 4032 )
-
-
-
Starting SQL statement dump
-
-
user_id = 83 user_name = SCOTT module = SQL * Plus action =
-
sql_id = dnkzmnvvdxgtm plan_hash_value = 1090737117 problem_type = 3
-
- - - - - Current SQL Statement for this session ( sql_id = dnkzmnvvdxgtm ) - - - - -
-
select * from dept where deptno in ( select deptno from emp where sal > 3000 )
-
sql_text_length = 77
-
sql = select * from dept where deptno in ( select deptno from emp where sal > 3000 )
-
- - - - - Explain Plan Dump - - - - -
-
- - - - - Plan Table - - - - -
-
-
= = = = = = = = = = = =
-
Plan Table
-
= = = = = = = = = = = =
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
-
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
-
| 0 | SELECT STATEMENT | | | | 6 | |
-
| 1 | MERGE JOIN SEMI | | 3 | 81 | 6 | 00 : 00 : 01 |
-
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 | 00 : 00 : 01 |
-
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 | 00 : 00 : 01 |
-
| 4 | SORT UNIQUE | | 7 | 49 | 4 | 00 : 00 : 01 |
-
| 5 | TABLE ACCESS FULL | EMP | 7 | 49 | 3 | 00 : 00 : 01 |
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
-
Predicate Information :
-
- - - - - - - - - - - - - - - - - - - - - -
-
4 - access ( "DEPTNO" = "DEPTNO" ) ---变成了join 条件就是它
-
4 - filter ( "DEPTNO" = "DEPTNO" )
-
5 - filter ( "SAL" > 3000 )
-
-
Content of other_xml column
-
= = = = = = = = = = = = = = = = = = = = = = = = = = =
-
db_version : 11 . 2 . . 3
-
parse_schema : SCOTT
-
plan_hash : 1090737117
-
plan_hash_2 : 1551854233
-
Outline Data :
-
/ * +
-
BEGIN_OUTLINE_DATA
-
IGNORE_OPTIM_EMBEDDED_HINTS
-
OPTIMIZER_FEATURES_ENABLE ( \ '11.2.0.3\' )
-
DB_VERSION ( \ '11.2.0.3\' )
-
ALL_ROWS
-
OUTLINE_LEAF ( @ \ "SEL$5DA710D3\" )
-
UNNEST ( @ \ "SEL$2\" )
-
OUTLINE ( @ \ "SEL$1\" )
-
OUTLINE ( @ \ "SEL$2\" )
-
INDEX ( @ \ "SEL$5DA710D3\" \ "DEPT\" @ \ "SEL$1\" ( \ "DEPT\" . \ "DEPTNO\" ) )
-
FULL ( @ \ "SEL$5DA710D3\" \ "EMP\" @ \ "SEL$2\" )
-
LEADING ( @ \ "SEL$5DA710D3\" \ "DEPT\" @ \ "SEL$1\" \ "EMP\" @ \ "SEL$2\" )
-
USE_MERGE ( @ \ "SEL$5DA710D3\" \ "EMP\" @ \ "SEL$2\" )
-
END_OUTLINE_DATA
-
* /
-
-
JF - join factorization:join 共同的项提取出来,没必要的项删掉。
-
merge join 就是将两个结果集排序,主要成本排序。
-
semi join 半链接,跟exist算法一样。当做merge但一个结果集存在另一个结果集,我们就把这个值提出来。如果有就提取,没有就不要。
这就是semi和inner join的区别,如果是inner join 那么挑选出来的是所有的(3,3,4,5,5,6), 逻辑上是不一样的,semi是个逻辑概念。这也是为什么semi join 半链接比inner join、outer join快的原因。
anti join正好是反过来的
使用in和exists的等价查询
相关参数
_unnest_subquery 关这些参数一般都是测试中
_optimizer_unnest_all_subqueries
_optimizer_squ_bottomup
相关hints
NO_QUERY_TRANSFORMATION hints的作用就是不解套
unnest
no_unnest
push_subq no_push_subq
qb_name
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1443168/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-1443168/