/u10/app/oracle/admin/db2rac1/udump/db2rac11_ora_23563_J3.trc
*** TRACE DUMP CONTINUED
FROM FILE /u10/app/oracle/admin/db2rac1/udump/db2rac11_ora_23563_J2.trc ***
Registered qb: SEL$1 0x25311424 (PARSER)
signature (): qb_name=SEL$1
nbfros=1 flg=0
fro(0): flg=4 objn=51989
hint_alias="DEPT"@"SEL$1"
Registered qb: SEL$2 0x2531102c (PARSER)
signature (): qb_name=SEL$2
nbfros=1 flg=0
fro(0): flg=4 objn=51987
hint_alias="OUTER"@"SEL$2"
Registered qb: SEL$3 0x2530a8c8 (PARSER)
signature (): qb_name=SEL$3
nbfros=1 flg=0
fro(0): flg=4 objn=51987
hint_alias="INNER"@"SEL$3"
**************************
Predicate
Move-Around (PM)
**************************
PM: Considering predicate
move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around
in SEL$1 (#0).
CBQT:
Validity checks passed for
1w7uy3mdggx1f.
apadrv-start: call(in-use=520, alloc=16360), compile(in-use=32684,
alloc=36696)
******************************************
Current SQL statement for
this session:
explain plan for
select *
from dept
where exists (
select 'X'
from emp outer
where outer.sal =
(select /* no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no
>= outer.dept_no
) and outer.dept_no=dept.dept_no
)
*******************************************
Legend
The following abbreviations
are used by optimizer trace.
CBQT - cost-based query
transformation
JPPD - join predicate
push-down
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
ST - star transformation
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)
CPUCSPEED - 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)
512: 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
128: use hash partitioning dimension
256: use range partitioning dimension
2048: use list partitioning dimension
1024: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
*******************************************
Peeked values of the binds
in SQL statement
*******************************************
***************************************
PARAMETERS USED BY THE
OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.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 = 10.2.0.2
_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 = 16
_optimizer_max_permutations = 2000
pga_aggregate_target = 16384 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 = 128 KB
_smm_max_size = 3276 KB
_smm_px_max_size = 8192 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.2.0.2
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
_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
_fix_control_key = 0
_optimizer_skip_scan_guess = false
_enable_row_shipping = false
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is
ON: tracking level = 1
***************************************
********************************
COST-BASED QUERY
TRANSFORMATIONS
********************************
FPD: Considering simple
filter push (pre rewrite) in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1 (#0) :
EXISTS (SELECT 'X' FROM
"EMP" "OUTER")
Registered qb: SEL$1
0x2530881c (COPY SEL$1)
signature(): NULL
Registered qb: SEL$2
0x25307cc8 (COPY SEL$2)
signature(): NULL
Registered qb: SEL$3
0x25304e58 (COPY SEL$3)
signature(): NULL
*****************************
Cost-Based Subquery
Unnesting
*****************************
SU: No subqueries to consider in query block SEL$3 (#3).
SU: Considering subquery unnesting in query block SEL$2
(#2)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on SEL$2 (#2).
SU: Checking
validity of unnesting subquery SEL$3 (#3)
SU: Passed validity
checks, but requires costing.
SU: Using search type: exhaustive
SU: Starting iteration 1, state space = (3) : (1)
CBQT:
Looking for costed query block SEL$3,
key = 7
CBQT:
Could not find stored query block.
SU: Unnesting
subquery SEL$3 (#3)
Registered qb: SEL$5D0CF27A
0x252fa9e8 (QUERY BLOCK TABLES CHANGED SEL$3)
signature ():
qb_name=SEL$5D0CF27A nbfros=2 flg=0
fro(0): flg=0 objn=51987
hint_alias="INNER"@"SEL$3"
fro(1):
flg=4 objn=51987 hint_alias="OUTER"@"SEL$5D0CF27A"
Registered qb: SEL$12EAFFF1
0x252fa9e8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$5D0CF27A)
signature ():
qb_name=SEL$12EAFFF1 nbfros=2 flg=0
fro(0): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
fro(1): flg=0
objn=51987 hint_alias="OUTER"@"SEL$5D0CF27A"
Registered qb: SEL$4111C18D
0x252fb7b0 (VIEW ADDED SEL$2)
signature ():
qb_name=SEL$4111C18D nbfros=2 flg=0
fro(0): flg=0
objn=51987 hint_alias="OUTER"@"SEL$2"
fro(1): flg=5
objn=0 hint_alias="VW_SQ_1"@"SEL$4111C18D"
Registered qb: SEL$EEC73E22
0x252fb7b0 (SUBQUERY UNNEST SEL$4111C18D; SEL$3)
signature (): qb_name=SEL$EEC73E22
nbfros=2 flg=0
fro(0): flg=0 objn=51987
hint_alias="OUTER"@"SEL$2"
fro(1):
flg=1 objn=0 hint_alias="VW_SQ_1"@"SEL$4111C18D"
FPD: Considering simple
filter push in SEL$EEC73E22 (#2)
FPD: Current where clause predicates in SEL$EEC73E22
(#2) :
"OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID
AND "OUTER"."DEPT_NO"=:B1
kkogcp: try to generate transitive predicate from check
constraints for SEL$EEC73E22 (#2)
predicates with check contraints:
"OUTER"."SAL"="VW_SQ_1"."VW_COL_1"
AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND
"OUTER"."DEPT_NO"=:B1
after transitive predicate generation:
"OUTER"."SAL"="VW_SQ_1"."VW_COL_1"
AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND
"OUTER"."DEPT_NO"=:B1
finally:
"OUTER"."SAL"="VW_SQ_1"."VW_COL_1"
AND "VW_SQ_1"."ROWID"="OUTER".ROWID AND
"OUTER"."DEPT_NO"=:B1
FPD: Considering simple
filter push in SEL$12EAFFF1 (#3)
FPD: Current where clause predicates in
SEL$12EAFFF1 (#3) :
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
kkogcp: try to generate transitive predicate from check
constraints for SEL$12EAFFF1 (#3)
predicates with check contraints:
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
after transitive predicate generation:
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
finally:
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
SU: Costing transformed query.
kkoqbc-start
: call(in-use=520,
alloc=16360), compile(in-use=89416, alloc=89840)
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$12EAFFF1 nbfros=2 flg=0
fro(0): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
fro(1): flg=0
objn=51987 hint_alias="OUTER"@"SEL$5D0CF27A"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: INNER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***********************
Table Stats::
Table: EMP Alias: OUTER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***************************************
SINGLE TABLE ACCESS PATH
Table: EMP Alias: OUTER
Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00
Access Path: TableScan
Cost:
57.10 Resp:
57.10 Degree: 0
Cost_io: 55.00 Cost_cpu: 4737631
Resp_io: 55.00 Resp_cpu: 4737631
Best:: AccessPath:
TableScan
Cost: 57.10 Degree: 1 Resp: 57.10
Card: 20000.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: EMP Alias: INNER
Card: Original: 20000 Rounded: 20000 Computed: 20000.00 Non Adjusted: 20000.00
Access Path: TableScan
Cost:
57.27 Resp:
57.27 Degree: 0
Cost_io: 55.00 Cost_cpu: 5137631
Resp_io: 55.00 Resp_cpu: 5137631
Best:: AccessPath:
TableScan
Cost: 57.27 Degree: 1 Resp: 57.27
Card: 20000.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[OUTER]#0 EMP[INNER]#1
***************
Now joining: EMP[INNER]#1
***************
NL Join
Outer table: Card: 20000.00 Cost: 57.10 Resp: 57.10
Degree: 1 Bytes: 27
Inner table: EMP Alias: INNER
Access Path: TableScan
NL Join:
Cost: 1113011.63
Resp: 1113011.63 Degree: 0
Cost_io: 1067556.00 Cost_cpu: 102757364831
Resp_io: 1067556.00 Resp_cpu: 102757364831
Best NL cost: 1113011.63
resc:
1113011.63 resc_io: 1067556.00 resc_cpu: 102757364831
resp:
1113011.63 resp_io: 1067556.00 resp_cpu: 102757364831
Join Card: 20000000.00 = outer (20000.00) * inner
(20000.00) * sel (0.05)
Join Card - Rounded:
20000000 Computed: 20000000.00
SM Join
Outer table:
resc: 57.10 card 20000.00
bytes: 27 deg: 1 resp: 57.10
Inner table: EMP Alias: INNER
resc: 57.27 card: 20000.00 bytes: 8
deg: 1 resp: 57.27
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 98 Row size: 40 Total Rows: 20000
Initial runs: 2 Merge passes: 1 IO Cost / pass: 54
Total IO sort cost: 152 Total CPU sort cost: 17548490
Total Temp space used: 1631000
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 47 Row size: 19 Total Rows: 20000
Initial runs: 2 Merge passes: 1 IO Cost / pass: 26
Total IO sort cost: 73 Total CPU sort cost: 16292564
Total Temp space used: 664000
SM join: Resc: 1238.17 Resp: 1238.17 [multiMatchCost=883.83]
SM cost: 1238.17
resc: 1238.17
resc_io: 335.00 resc_cpu: 2041716316
resp: 1238.17
resp_io: 335.00 resp_cpu: 2041716316
GROUP BY sort
GROUP BY cardinality: 1.00, TABLE cardinality: 20000000.00
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 119981 Row size: 49 Total Rows: 20000000
Initial runs: 1 Merge
passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21856876289
Total Temp space used: 0
Best:: JoinMethod: SortMerge
Cost: 10906.75 Degree: 1 Resp: 10906.75 Card: 20000000.00 Bytes: 35
***********************
Best so far:
Table#: 0 cost:
57.0957 card: 20000.0000 bytes: 540000
Table#: 1 cost: 10906.7544 card: 20000000.0000 bytes: 700000000
***********************
Join order[2]:
EMP[INNER]#1 EMP[OUTER]#0
***************
Now joining: EMP[OUTER]#0
***************
NL Join
Outer table: Card: 20000.00 Cost: 57.27 Resp: 57.27
Degree: 1 Bytes: 8
Inner table: EMP Alias: OUTER
Access Path: TableScan
NL Join:
Cost: 1109472.94
Resp: 1109472.94 Degree: 0
Cost_io: 1067556.00 Cost_cpu: 94757764831
Resp_io: 1067556.00 Resp_cpu: 94757764831
Best NL cost: 1109472.94
resc:
1109472.94 resc_io: 1067556.00 resc_cpu: 94757764831
resp:
1109472.94 resp_io: 1067556.00 resp_cpu: 94757764831
Join Card: 20000000.00 = outer (20000.00) * inner
(20000.00) * sel (0.05)
Join Card - Rounded:
20000000 Computed: 20000000.00
SM Join
Outer table:
resc: 57.27 card 20000.00
bytes: 8 deg: 1 resp: 57.27
Inner table: EMP Alias: OUTER
resc: 57.10 card: 20000.00 bytes: 27
deg: 1 resp: 57.10
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 47 Row size: 19 Total Rows: 20000
Initial runs: 2 Merge passes: 1 IO Cost / pass: 26
Total IO sort cost: 73 Total CPU sort cost: 16292564
Total Temp space used: 664000
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 98 Row size: 40 Total Rows: 20000
Initial runs: 2 Merge passes: 1 IO Cost / pass: 54
Total IO sort cost: 152 Total CPU sort cost: 17548490
Total Temp space used: 1631000
SM join: Resc: 1238.17 Resp: 1238.17 [multiMatchCost=883.83]
SM cost: 1238.17
resc: 1238.17
resc_io: 335.00 resc_cpu: 2041716316
resp: 1238.17
resp_io: 335.00 resp_cpu: 2041716316
GROUP BY sort
GROUP BY cardinality: 1.00, TABLE cardinality: 20000000.00
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 119981 Row size: 49 Total Rows: 20000000
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 21856876289
Total Temp space used: 0
Join order
aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations
tried: 2
*********************************
(newjo-save) [1 0 ]
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 119981 Row size: 49 Total Rows: 20000000
Initial runs: 293 Merge passes: 3 IO Cost / pass: 64992
Total IO sort cost: 314957 Total CPU sort cost: 27766180501
Total Temp space used: 1771267000
Final - All Rows
Plan: Best join order: 1
Cost: 10906.7544 Degree: 1 Card: 20000000.0000 Bytes: 700000000
Resc: 10906.7544 Resc_io: 335.0000 Resc_cpu: 23898592605
Resp: 10906.7544 Resp_io: 335.0000 Resc_cpu: 23898592605
kkoipt: Query block SEL$12EAFFF1 (#3)
******* UNPARSED QUERY IS
*******
SELECT /*+ */ MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
kkoqbc-end
: call(in-use=31984,
alloc=49112), compile(in-use=90280, alloc=93896)
kkoqbc-start
: call(in-use=31984,
alloc=49112), compile(in-use=90500, alloc=93896)
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$EEC73E22 nbfros=2 flg=0
fro(0): flg=0 objn=51987
hint_alias="OUTER"@"SEL$2"
fro(1):
flg=1 objn=0 hint_alias="VW_SQ_1"@"SEL$4111C18D"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: OUTER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***********************
Table Stats::
Table: VW_SQ_1 Alias: VW_SQ_1 NO STATISTICS
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max:
5
Table:
EMP Alias:
OUTER
Card: Original: 20000 Rounded: 3333 Computed: 3333.33 Non Adjusted: 3333.33
Access Path: TableScan
Cost:
57.57 Resp:
57.57 Degree: 0
Cost_io: 55.00 Cost_cpu: 5804291
Resp_io: 55.00 Resp_cpu: 5804291
Best:: AccessPath: TableScan
Cost: 57.57 Degree: 1 Resp: 57.57
Card: 3333.33 Bytes: 0
Multi-column join key card:
20000 #cols:
2 table: EMP
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[OUTER]#0 VW_SQ_1[VW_SQ_1]#1
***************
Now joining: VW_SQ_1[VW_SQ_1]#1
***************
NL Join
Outer table: Card: 3333.33 Cost: 57.57 Resp: 57.57
Degree: 1 Bytes: 40
Inner table: VW_SQ_1 Alias: VW_SQ_1
Access Path: TableScan
NL Join:
Cost: 36352270.03
Resp: 36352270.03 Degree:
0
Cost_io: 1116610.00 Cost_cpu: 79654014957857
Resp_io: 1116610.00 Resp_cpu: 79654014957857
Best NL cost: 36352270.03
resc:
36352270.03 resc_io: 1116610.00 resc_cpu: 79654014957857
resp:
36352270.03 resp_io: 1116610.00 resp_cpu: 79654014957857
Column (#2): ROWID(ROWID) NO STATISTICS (using defaults)
AvgLen: 10.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05
Using multi-column join key
sanity check for table EMP
Revised join sel:5.0000e-05 = 2.5000e-09 * (1/20000.00) * (1/2.5000e-09)
Join Card: 3333333.33 = outer (3333.33) * inner
(20000000.00) * sel (5.0000e-05)
Join Card - Rounded:
3333333 Computed: 3333333.33
SM Join
Outer table:
resc: 57.57 card 3333.33
bytes: 40 deg: 1 resp: 57.57
Inner table: VW_SQ_1 Alias: VW_SQ_1
resc: 10906.75 card: 20000000.00 bytes: 29
deg: 1 resp: 10906.75
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 23 Row size: 54 Total Rows: 3333
Initial runs: 2 Merge passes: 1 IO Cost / pass: 14
Total IO sort cost: 37 Total CPU sort cost: 4584350
Total Temp space used: 386000
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 102841 Row size: 42 Total Rows: 20000000
Initial runs: 251 Merge passes: 2 IO Cost / pass: 55708
Total IO sort cost: 214257 Total CPU sort cost: 25655719988
Total Temp space used: 1771267000
SM join: Resc: 236609.39 Resp: 236609.39 [multiMatchCost=0.00]
SM cost: 236609.39
resc: 236609.39
resc_io: 214684.00 resc_cpu: 49564701234
resp: 236609.39
resp_io: 214684.00 resp_cpu: 49564701234
HA Join
Outer table:
resc: 57.57 card 3333.33
bytes: 40 deg: 1 resp: 57.57
Inner table: VW_SQ_1 Alias: VW_SQ_1
resc:
10906.75 card: 20000000.00 bytes: 29
deg: 1 resp: 10906.75
using dmeth:
2 #groups: 1
Cost per ptn: 885.44 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 11849.76 Resp: 11849.76 [multiMatchCost=0.00]
HA cost: 11849.76
resc: 11849.76
resc_io: 390.00 resc_cpu: 25906027151
resp: 11849.76
resp_io: 390.00 resp_cpu: 25906027151
Best:: JoinMethod: Hash
Cost: 11849.76 Degree: 1 Resp: 11849.76 Card: 3333333.33 Bytes: 69
***********************
Best so far:
Table#: 0 cost:
57.5676 card: 3333.3333 bytes: 133320
Table#: 1 cost: 11849.7609 card: 3333333.3333 bytes: 229999977
***********************
Join order[2]:
VW_SQ_1[VW_SQ_1]#1 EMP[OUTER]#0
***************
Now joining: EMP[OUTER]#0
***************
NL Join
Outer table: Card: 20000000.00 Cost: 10906.75 Resp: 10906.75 Degree: 1
Bytes: 29
Inner table: EMP Alias: OUTER
Access Path: TableScan
NL Join:
Cost: 1121811621.19
Resp: 1121811621.19
Degree: 0
Cost_io: 1067500336.00 Cost_cpu: 122776525792605
Resp_io: 1067500336.00
Resp_cpu: 122776525792605
Inner table: EMP Alias: OUTER
Access Path: Rowid
NL Join:
Cost: 20075415.42
Resp: 20075415.42 Degree:
0
Cost_io: 20000335.00 Cost_cpu: 169727392605
Resp_io: 20000335.00 Resp_cpu: 169727392605
Best NL cost: 20075415.42
resc:
20075415.42 resc_io: 20000335.00 resc_cpu: 169727392605
resp:
20075415.42 resp_io: 20000335.00 resp_cpu: 169727392605
Join Card: 166.67 = outer (20000000.00) * inner
(3333.33) * sel (2.5000e-09)
Join cardinality for HJ/SMJ
(no post filters): 3333333.33, outer:
20000000.00, inner: 3333.33, sel: 5.0000e-05
Join Card - Rounded: 167
Computed: 166.67
Plan cardinality mismatch:
best card= 3333333.33333334001 curr
card= 166.66666666667
Best:: JoinMethod: NestedLoop
Cost: 20075415.42 Degree: 1 Resp: 20075415.42 Card: 166.67
Bytes: 69
***********************
Best so far:
Table#: 1 cost:
10906.7544 card: 20000000.0000 bytes: 580000000
Table#: 0 cost: 20075415.4176 card: 166.6667 bytes: 11523
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations
tried: 2
*********************************
(newjo-save) [1 0 ]
Final - First
Rows Plan: Best join order: 2
Cost: 20075415.4176 Degree: 1 Card: 167.0000 Bytes: 11523
Resc: 20075415.4176 Resc_io: 20000335.0000 Resc_cpu: 169727392605
Resp: 20075415.4176 Resp_io: 20000335.0000 Resc_cpu: 169727392605
kkoipt: Query block SEL$EEC73E22 (#2)
******* UNPARSED QUERY IS
*******
SELECT /*+ */ 0 FROM (SELECT /*+ */
MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" GROUP BY "OUTER".ROWID)
"VW_SQ_1","JEREMY"."EMP" "OUTER" WHERE
"OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID
AND "OUTER"."DEPT_NO"="DEPT"."DEPT_NO"
kkoqbc-end
: call(in-use=68332,
alloc=81864), compile(in-use=92344, alloc=93896)
CBQT:
Saved costed qb# 3 (SEL$12EAFFF1)
SU: Considering interleaved complex view merging
SU: Unnesting
subquery SEL$3 (#3)
CVM: Considering view merge
(candidate phase) in query block SEL$EEC73E22 (#2)
CVM: Considering view merge
(candidate phase) in query block SEL$12EAFFF1 (#3)
Query block (0x252f6da8)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT /*+ */ MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
Query block (0x252f6da8)
unchanged
CVM: CBQT Marking query
block SEL$12EAFFF1 (#3)as valid for CVM.
CVM: Merging complex view SEL$12EAFFF1 (#3) into
SEL$EEC73E22 (#2).
qbcp:******* UNPARSED QUERY IS *******
SELECT /*+ */ 0 FROM (SELECT /*+ */
MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" GROUP BY "OUTER".ROWID)
"VW_SQ_2","JEREMY"."EMP" "OUTER" WHERE
"OUTER"."SAL"="VW_SQ_2"."VW_COL_1" AND "VW_SQ_2"."ROWID"="OUTER".ROWID
AND "OUTER"."DEPT_NO"="DEPT"."DEPT_NO"
vqbcp:******* UNPARSED QUERY IS *******
SELECT /*+ */ MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
CVM: result SEL$EEC73E22
(#2).
******* UNPARSED QUERY IS
*******
SELECT /*+ */ 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="DEPT"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x252f7ce0)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT /*+ */ 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="DEPT"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x252f7ce0)
unchanged
Registered qb: SEL$B828357B
0x252f7ce0 (VIEW MERGE SEL$EEC73E22; SEL$12EAFFF1)
signature ():
qb_name=SEL$B828357B nbfros=2 flg=0
fro(0): flg=0
objn=51987 hint_alias="OUTER"@"SEL$2"
fro(1): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
FPD: Considering simple
filter push in SEL$B828357B (#2)
FPD: Current where clause predicates in SEL$B828357B
(#2) :
"OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
kkogcp: try to generate transitive predicate from check
constraints for SEL$B828357B (#2)
predicates with check contraints:
"OUTER"."DEPT_NO"=:B1 AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">=:B2
after transitive predicate generation:
"OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">=:B2
finally: "OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">=:B2
FPD: Following transitive predicates are
generated in SEL$B828357B (#2) :
"INNER"."DEPT_NO">=:B1
CVM: Costing transformed
query.
kkoqbc-start
: call(in-use=69168,
alloc=81864), compile(in-use=105196, alloc=106064)
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$B828357B nbfros=2 flg=0
fro(0): flg=0
objn=51987 hint_alias="OUTER"@"SEL$2"
fro(1): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: OUTER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***********************
Table Stats::
Table: EMP Alias: INNER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
Column (#2): SAL(NUMBER)
AvgLen: 5.00 NDV: 20000 Nulls: 0 Density:
5.0000e-05 Min: 1 Max: 20000
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density:
0.16667 Min: 0 Max: 5
Table: EMP Alias: INNER
Card: Original: 20000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost:
57.55 Resp:
57.55 Degree: 0
Cost_io: 55.00 Cost_cpu: 5757631
Resp_io: 55.00 Resp_cpu: 5757631
Best:: AccessPath:
TableScan
Cost: 57.55 Degree: 1 Resp: 57.55
Card: 1000.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max:
5
Table:
EMP Alias:
OUTER
Card: Original: 20000 Rounded: 3333 Computed: 3333.33 Non Adjusted: 3333.33
Access Path: TableScan
Cost:
57.57 Resp:
57.57 Degree: 0
Cost_io: 55.00 Cost_cpu: 5804291
Resp_io: 55.00 Resp_cpu: 5804291
Best:: AccessPath:
TableScan
Cost: 57.57 Degree: 1 Resp: 57.57
Card: 3333.33 Bytes: 0
Grouping column cardinality
[ SAL] 3333
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[INNER]#0 EMP[OUTER]#1
***************
Now joining: EMP[OUTER]#1
***************
NL Join
Outer table: Card: 1000.00 Cost: 57.55 Resp: 57.55
Degree: 1 Bytes: 8
Inner table: EMP Alias: OUTER
Access Path: TableScan
NL
Join: Cost: 56001.13 Resp: 56001.13 Degree: 0
Cost_io: 53431.00 Cost_cpu: 5810048991
Resp_io: 53431.00 Resp_cpu: 5810048991
Best NL cost: 56001.13
resc:
56001.13 resc_io: 53431.00 resc_cpu: 5810048991
resp: 56001.13
resp_io: 53431.00 resp_cpu: 5810048991
Join Card: 166666.67 = outer (1000.00) * inner (3333.33)
* sel (0.05)
Join Card - Rounded: 166667
Computed: 166666.67
Grouping column cardinality
[ SAL] 3333
SM Join
Outer table:
resc: 57.55 card 1000.00
bytes: 8 deg: 1 resp: 57.55
Inner table: EMP Alias: OUTER
resc: 57.57 card: 3333.33
bytes: 55 deg: 1 resp: 57.57
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 3 Row size: 19 Total Rows: 1000
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 2709612
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 29 Row size: 71 Total Rows: 3333
Initial runs: 2 Merge passes: 1 IO Cost / pass: 18
Total IO sort cost: 47 Total CPU sort cost: 4732106
Total Temp space used: 533000
SM join: Resc: 172.63 Resp: 172.63 [multiMatchCost=7.23]
SM cost: 172.63
resc: 172.63
resc_io: 157.00 resc_cpu: 35337040
resp: 172.63
resp_io: 157.00 resp_cpu: 35337040
GROUP BY sort
GROUP BY cardinality: 3333.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1633 Row size: 80 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Best:: JoinMethod: SortMerge
Cost: 231.25 Degree: 1 Resp: 231.25
Card: 166666.67 Bytes: 63
***********************
Best so far:
Table#: 0 cost:
57.5469 card: 1000.0000 bytes: 8000
Table#: 1 cost: 231.2523 card: 166666.6667 bytes: 10500021
***********************
Join order[2]:
EMP[OUTER]#1 EMP[INNER]#0
***************
Now joining: EMP[INNER]#0
***************
NL Join
Outer table: Card: 3333.33 Cost: 57.57 Resp: 57.57
Degree: 1 Bytes: 55
Inner table: EMP Alias: INNER
Access Path: TableScan
NL Join:
Cost: 186447.52
Resp: 186447.52 Degree: 0
Cost_io: 177956.00 Cost_cpu: 19195989614
Resp_io: 177956.00 Resp_cpu: 19195989614
Best NL cost: 186447.52
resc:
186447.52 resc_io: 177956.00 resc_cpu: 19195989614
resp:
186447.52 resp_io: 177956.00 resp_cpu: 19195989614
Join Card: 166666.67 = outer (3333.33) * inner (1000.00)
* sel (0.05)
Join Card - Rounded: 166667
Computed: 166666.67
Grouping column cardinality
[ SAL] 3333
SM Join
Outer table:
resc: 57.57 card 3333.33
bytes: 55 deg: 1 resp: 57.57
Inner table: EMP Alias: INNER
resc: 57.55 card: 1000.00
bytes: 8 deg: 1 resp: 57.55
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 29 Row size: 71 Total Rows: 3333
Initial runs: 2 Merge passes: 1 IO Cost / pass: 18
Total IO sort cost: 47 Total CPU sort cost: 4732106
Total Temp space used: 533000
SORT
resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 3 Row size: 19 Total Rows: 1000
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 2709612
Total Temp space used: 0
SM join: Resc: 172.63 Resp: 172.63 [multiMatchCost=7.23]
SM cost: 172.63
resc: 172.63
resc_io: 157.00 resc_cpu: 35337040
resp: 172.63
resp_io: 157.00 resp_cpu: 35337040
GROUP BY sort
GROUP BY cardinality: 3333.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1633 Row size: 80 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Join order
aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations
tried: 2
*********************************
(newjo-save) [1 0 ]
GROUP BY cardinality: 1.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1633 Row size: 80 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Final - All Rows
Plan: Best join order: 1
Cost: 231.2523 Degree: 1 Card: 166667.0000 Bytes: 10500021
Resc: 231.2523 Resc_io: 157.0000 Resc_cpu: 167855234
Resp: 231.2523 Resp_io: 157.0000 Resc_cpu: 167855234
kkoipt: Query block SEL$B828357B (#2)
******* UNPARSED QUERY IS
*******
SELECT /*+ */ 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="DEPT"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">="DEPT"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
kkoqbc-end
: call(in-use=111648,
alloc=130992), compile(in-use=107292, alloc=110120)
SU: Finished interleaved complex view merging
SU: Updated best state, Cost = 231.25
CBQT:
Saved costed qb# 3 (SEL$12EAFFF1)
SU: Starting iteration 2, state space = (3) : (1)
CBQT:
Looking for costed query block SEL$3,
key = 6
CBQT:
Could not find stored query block.
FPD: Considering simple
filter push in SEL$2 (#2)
FPD: Current where clause predicates in SEL$2 (#2) :
"OUTER"."SAL"=
(SELECT /*+ */ MAX("INNER"."SAL") FROM "EMP" "INNER") AND
"OUTER"."DEPT_NO"=:B1
FPD: Considering simple
filter push in SEL$3 (#3)
FPD: Current where clause predicates in SEL$3 (#3) :
"INNER"."DEPT_NO">=:B1
kkogcp: try to generate transitive predicate from check
constraints for SEL$3 (#3)
predicates with check contraints:
"INNER"."DEPT_NO">=:B1
after transitive predicate generation: "INNER"."DEPT_NO">=:B1
finally: "INNER"."DEPT_NO">=:B1
kkogcp: try to generate transitive predicate from check
constraints for SEL$2 (#2)
predicates with check contraints:
"OUTER"."SAL"= (SELECT /*+ */ MAX("INNER"."SAL") FROM "EMP" "INNER") AND
"OUTER"."DEPT_NO"=:B1
after transitive predicate generation:
"OUTER"."SAL"= (SELECT /*+ */ MAX("INNER"."SAL") FROM "EMP" "INNER") AND
"OUTER"."DEPT_NO"=:B1
finally: "OUTER"."SAL"= (SELECT /*+ */ MAX("INNER"."SAL")
FROM "EMP" "INNER") AND "OUTER"."DEPT_NO"=:B1
SU: Costing transformed query.
kkoqbc-start
: call(in-use=111648,
alloc=130992), compile(in-use=115016, alloc=118232)
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$3 nbfros=1 flg=0
fro(0): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: INNER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max:
5
Table:
EMP Alias:
INNER
Card: Original: 20000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost:
57.55 Resp:
57.55 Degree: 0
Cost_io: 55.00 Cost_cpu: 5757631
Resp_io: 55.00 Resp_cpu: 5757631
Best:: AccessPath:
TableScan
Cost: 57.55 Degree: 1 Resp: 57.55
Card: 1000.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[INNER]#0
***********************
Best so far: Table#: 0 cost: 57.5469 card: 1000.0000 bytes: 8000
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations
tried: 1
*********************************
Final - All Rows
Plan: Best join order: 1
Cost: 57.5469 Degree: 1 Card: 1000.0000 Bytes: 8000
Resc: 57.5469 Resc_io: 55.0000 Resc_cpu: 5757631
Resp: 57.5469 Resp_io: 55.0000 Resc_cpu: 5757631
kkoipt: Query block SEL$3 (#3)
******* UNPARSED QUERY IS
*******
SELECT /*+ */ MAX("INNER"."SAL")
FROM "JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
kkoqbc-end
: call(in-use=128252,
alloc=147368), compile(in-use=115652, alloc=118232)
kkoqbc-start
: call(in-use=128252,
alloc=147368), compile(in-use=115696, alloc=118232)
****************
QUERY BLOCK TEXT
****************
Not available.
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$2 nbfros=1 flg=0
fro(0): flg=0
objn=51987 hint_alias="OUTER"@"SEL$2"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: OUTER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#2): SAL(NUMBER)
AvgLen: 5.00 NDV: 20000 Nulls: 0 Density:
5.0000e-05 Min: 1 Max: 20000
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density:
0.16667 Min: 0 Max: 5
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density:
0.16667 Min: 0 Max: 5
Table: EMP Alias: OUTER
Card: Original: 20000 Rounded: 3333 Computed: 3333.33 Non Adjusted: 3333.33
Access Path: TableScan
Cost:
57.79 Resp:
57.79 Degree: 0
Cost_io: 55.00 Cost_cpu: 6304298
Resp_io: 55.00 Resp_cpu: 6304298
Best:: AccessPath:
TableScan
Cost: 57.79 Degree: 1 Resp: 57.79
Card: 3333.33 Bytes: 0
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[OUTER]#0
***********************
Best so far: Table#: 0 cost: 57.7888 card: 3333.3333 bytes: 36663
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations
tried: 1
*********************************
Final adjusted join
cardinality: 167, sq. fil. factor: 20
Final - First
Rows Plan: Best join order: 1
Cost: 403.0704 Degree: 1 Card: 167.0000 Bytes: 36663
Resc: 403.0704 Resc_io: 385.0000 Resc_cpu: 40850086
Resp: 403.0704 Resp_io: 385.0000 Resc_cpu: 40850086
kkoipt: Query block SEL$2 (#2)
******* UNPARSED QUERY IS
*******
SELECT /*+ */ 0 FROM
"JEREMY"."EMP" "OUTER" WHERE
"OUTER"."SAL"= (SELECT /*+ */ MAX("INNER"."SAL") FROM "JEREMY"."EMP"
"INNER" WHERE "INNER"."DEPT_NO">="OUTER"."DEPT_NO") AND "OUTER"."DEPT_NO"="DEPT"."DEPT_NO"
kkoqbc-end
: call(in-use=147548,
alloc=163744), compile(in-use=116664, alloc=118232)
CBQT:
Saved costed qb# 3 (SEL$3)
SU: Considering interleaved complex view merging
SU: Finished interleaved complex view merging
SU: New cost worse than best so far, Cost = 231.25
SU: Will unnest subquery SEL$3 (#3)
SU: Reconstructing original query from best state.
SU: Considering subquery unnest on SEL$2 (#2).
SU: Checking
validity of unnesting subquery SEL$3 (#3)
SU: Passed validity
checks.
SU: Unnesting
subquery SEL$3 (#3)
CVM: Checking validity of merging SEL$12EAFFF1
(#3)
Query block (0x25304e58)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT /*+ */ MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
Query block (0x25304e58)
unchanged
CVM: CBQT Marking query
block SEL$12EAFFF1 (#3)as valid for CVM.
CVM: Merging complex view SEL$12EAFFF1 (#3) into
SEL$EEC73E22 (#2).
qbcp:******* UNPARSED QUERY IS *******
SELECT /*+ */ 'X' FROM (SELECT /*+ */
MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP" "OUTER","JEREMY"."EMP"
"INNER" WHERE "INNER"."DEPT_NO">="OUTER"."DEPT_NO" GROUP BY "OUTER".ROWID)
"VW_SQ_3","JEREMY"."EMP" "OUTER" WHERE
"OUTER"."SAL"="VW_SQ_3"."VW_COL_1" AND "VW_SQ_3"."ROWID"="OUTER".ROWID
AND "OUTER"."DEPT_NO"="DEPT"."DEPT_NO"
vqbcp:******* UNPARSED QUERY IS *******
SELECT /*+ */ MAX("INNER"."SAL")
"VW_COL_1","OUTER".ROWID "ROWID" FROM
"JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
CVM: result SEL$EEC73E22
(#2).
******* UNPARSED QUERY IS
*******
SELECT /*+ */ 'X' FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="DEPT"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x25307cc8)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT /*+ */ 'X' FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="DEPT"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x25307cc8)
unchanged
SU: Considering subquery unnesting in query block SEL$1
(#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on SEL$1 (#1).
SU: Checking
validity of unnesting subquery SEL$B828357B (#2)
SU: SU bypassed:
Failed aggregate validity checks.
SU: SU bypassed:
Failed basic validity checks.
SU: Validity checks
failed.
SU: Transfer failed
validity.
*******************************
Cost-Based Complex
View Merging
*******************************
CVM: Finding query blocks
in SEL$1 (#1) that are valid to merge.
Query block (0x25307cc8)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT /*+ */ 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="DEPT"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x25307cc8)
unchanged
SU: Unnesting
subquery SEL$3 (#3)
*************************
Set-Join Conversion
(SJC)
*************************
SJC: Considering set-join
conversion in SEL$1 (#1).
SJC: Considering set-join
conversion in SEL$EEC73E22 (#2).
SJC: Considering set-join
conversion in SEL$12EAFFF1 (#3).
Query block (0x2530a8c8)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT MAX("INNER"."SAL") "VW_COL_1","OUTER".ROWID
"ROWID" FROM "JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
Query block (0x2530a8c8)
unchanged
CVM: CBQT Marking query
block SEL$12EAFFF1 (#3)as valid for CVM.
CVM: Merging complex view SEL$12EAFFF1 (#3) into
SEL$EEC73E22 (#2).
qbcp:******* UNPARSED QUERY IS *******
SELECT 0 FROM (SELECT MAX("INNER"."SAL") "VW_COL_1","OUTER".ROWID
"ROWID" FROM "JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" GROUP BY "OUTER".ROWID)
"VW_SQ_1","JEREMY"."EMP" "OUTER" WHERE
"OUTER"."SAL"="VW_SQ_1"."VW_COL_1" AND "VW_SQ_1"."ROWID"="OUTER".ROWID
AND
"OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO"
vqbcp:******* UNPARSED QUERY IS *******
SELECT MAX("INNER"."SAL") "VW_COL_1","OUTER".ROWID
"ROWID" FROM "JEREMY"."EMP"
"OUTER","JEREMY"."EMP" "INNER" WHERE
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID
CVM: result SEL$EEC73E22
(#2).
******* UNPARSED QUERY IS
*******
SELECT 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x2531102c)
before join elimination:
SQL:*******
UNPARSED QUERY IS *******
SELECT 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
Query block (0x2531102c)
unchanged
**************************
Predicate
Move-Around (PM)
**************************
PM: Considering predicate
move-around in SEL$1 (#1).
PM: Checking validity of predicate move-around
in SEL$1 (#1).
PM: PM bypassed: Outer query contains no
views.
Registered qb: SEL$B828357B
0x253025b4 (COPY SEL$B828357B)
signature(): NULL
***********************************
Cost-Based Filter
Predicate Pull-Up
***********************************
***********************************
Cost-Based Join
Predicate Push-down
***********************************
JPPD: Checking validity of
push-down in query block SEL$B828357B (#2)
JPPD: No view found to push predicate into.
JPPD: Checking validity of
push-down in query block SEL$1 (#1)
JPPD: No view found to push predicate into.
JPPD: Applying
transformation directives
JPPD: Checking validity of
push-down in query block SEL$B828357B (#2)
JPPD: No view found to push predicate into.
JPPD: Checking validity of
push-down in query block SEL$1 (#1)
JPPD: No view found to push predicate into.
FPD: Considering simple
filter push in SEL$1 (#1)
FPD: Current where clause predicates in SEL$1 (#1) :
EXISTS (SELECT 0
FROM "EMP" "INNER","EMP" "OUTER" GROUP
BY
"OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL"
HAVING "OUTER"."SAL"=MAX("INNER"."SAL"))
FPD: Considering simple
filter push in SEL$B828357B (#2)
FPD: Current where clause predicates in
SEL$B828357B (#2) :
"OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO"
kkogcp: try to generate transitive predicate from check
constraints for SEL$B828357B (#2)
predicates with check contraints:
"OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">=:B2
after transitive predicate generation:
"OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">=:B2
finally: "OUTER"."DEPT_NO"=:B1 AND
"INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">=:B2
FPD: Following transitive predicates are
generated in SEL$B828357B (#2) :
"INNER"."DEPT_NO">=:B1
kkogcp: try to generate transitive predicate from check
constraints for SEL$1 (#1)
predicates with check contraints:
EXISTS (SELECT 0 FROM "EMP" "INNER","EMP"
"OUTER" GROUP BY
"OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL"))
after transitive predicate generation: EXISTS (SELECT 0 FROM "EMP"
"INNER","EMP" "OUTER" GROUP BY
"OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL"))
finally: EXISTS (SELECT 0 FROM "EMP"
"INNER","EMP" "OUTER" GROUP BY
"OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL"))
*************************
First K Rows: Setup begin
kkoqbc-start
: call(in-use=149820,
alloc=163744), compile(in-use=114692, alloc=155152)
****************
QUERY BLOCK TEXT
****************
select 'X'
from emp outer
where outer.sal =
(select /* no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no
>= outer.dept_no
) and outer.dept_no=dept.dept_no
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$B828357B nbfros=2 flg=0
fro(0): flg=0
objn=51987 hint_alias="OUTER"@"SEL$2"
fro(1): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: OUTER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***********************
Table Stats::
Table: EMP Alias: INNER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
Column (#2): SAL(NUMBER)
AvgLen: 5.00 NDV: 20000 Nulls: 0 Density:
5.0000e-05 Min: 1 Max: 20000
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density:
0.16667 Min: 0 Max: 5
Table: EMP Alias: INNER
Card: Original: 20000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost:
57.55 Resp:
57.55 Degree: 0
Cost_io: 55.00 Cost_cpu: 5757631
Resp_io: 55.00 Resp_cpu: 5757631
Best:: AccessPath:
TableScan
Cost: 57.55 Degree: 1 Resp: 57.55
Card: 1000.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max:
5
Table:
EMP Alias:
OUTER
Card: Original: 20000 Rounded: 3333 Computed: 3333.33 Non Adjusted: 3333.33
Access Path: TableScan
Cost:
57.57 Resp:
57.57 Degree: 0
Cost_io: 55.00 Cost_cpu: 5804291
Resp_io: 55.00 Resp_cpu: 5804291
Best:: AccessPath:
TableScan
Cost: 57.57 Degree: 1 Resp: 57.57
Card: 3333.33 Bytes: 0
Grouping column cardinality
[ SAL] 3333
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[INNER]#0 EMP[OUTER]#1
***************
Now joining: EMP[OUTER]#1
***************
NL Join
Outer table: Card: 1000.00 Cost: 57.55 Resp: 57.55
Degree: 1 Bytes: 8
Inner table: EMP Alias: OUTER
Access Path: TableScan
NL Join:
Cost: 56001.13
Resp: 56001.13 Degree: 0
Cost_io: 53431.00 Cost_cpu: 5810048991
Resp_io: 53431.00 Resp_cpu: 5810048991
Best NL cost: 56001.13
resc:
56001.13 resc_io: 53431.00 resc_cpu: 5810048991
resp:
56001.13 resp_io: 53431.00 resp_cpu: 5810048991
Join Card: 166666.67 = outer (1000.00) * inner (3333.33)
* sel (0.05)
Join Card - Rounded: 166667
Computed: 166666.67
Grouping column cardinality
[ SAL] 3333
SM Join
Outer table:
resc: 57.55 card 1000.00
bytes: 8 deg: 1 resp: 57.55
Inner table: EMP Alias: OUTER
resc: 57.57 card: 3333.33
bytes: 35 deg: 1 resp: 57.57
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 3 Row size: 19 Total Rows: 1000
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 2709612
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 20 Row size: 49 Total Rows: 3333
Initial runs: 2 Merge passes: 1 IO Cost / pass: 12
Total IO sort cost: 32 Total CPU sort cost: 4510472
Total Temp space used: 369000
SM join: Resc: 157.53 Resp: 157.53 [multiMatchCost=7.23]
SM cost: 157.53
resc: 157.53
resc_io: 142.00 resc_cpu: 35115406
resp: 157.53
resp_io: 142.00 resp_cpu: 35115406
GROUP BY sort
GROUP BY cardinality: 3333.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1184 Row size: 58 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Best:: JoinMethod: SortMerge
Cost: 216.15 Degree: 1 Resp: 216.15
Card: 166666.67 Bytes: 43
***********************
Best so far:
Table#: 0 cost:
57.5469 card: 1000.0000 bytes: 8000
Table#: 1 cost: 216.1542 card: 166666.6667 bytes: 7166681
*********************************
Number of join permutations
tried: 1
*********************************
(newjo-save) [1 0 ]
GROUP BY cardinality: 1.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area
size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1184 Row size: 58 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Final - All Rows
Plan: Best join order: 1
Cost: 216.1542 Degree: 1 Card: 166667.0000 Bytes: 7166681
Resc: 216.1542 Resc_io: 142.0000 Resc_cpu: 167633600
Resp: 216.1542 Resp_io: 142.0000 Resc_cpu: 167633600
kkoipt: Query block SEL$B828357B (#2)
******* UNPARSED QUERY IS
*******
SELECT /*+
NO_STAR_TRANSFORMATION NO_EXPAND */ 0 FROM "JEREMY"."EMP"
"INNER","JEREMY"."EMP"
"OUTER" WHERE "OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">="SYS_ALIAS_1"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
kkoqbc-end
: call(in-use=187664,
alloc=196496), compile(in-use=116788, alloc=155152)
kkoqbc-start
: call(in-use=186284,
alloc=196496), compile(in-use=118756, alloc=155152)
****************
QUERY BLOCK TEXT
****************
select *
from dept
where exists (
select 'X'
from emp outer
where outer.sal =
(select /* no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no
>= outer.dept_no
) and outer.dept_no=dept.dept_no
)
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0
objn=51989 hint_alias="DEPT"@"SEL$1"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: DEPT Alias: SYS_ALIAS_1
#Rows: 6 #Blks:
5 AvgRowLen: 5.00
Index Stats::
Index: D_PK
LVLS: 0 #LB: 1
#DK: 6 LB/K: 1.00 DB/K: 1.00
CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max:
5
Table:
DEPT Alias:
SYS_ALIAS_1
Card: Original: 6 Rounded: 6 Computed: 6.00 Non Adjusted: 6.00
Access Path: TableScan
Cost:
3.02 Resp:
3.02 Degree: 0
Cost_io: 3.00 Cost_cpu: 36747
Resp_io: 3.00 Resp_cpu: 36747
Best:: AccessPath:
TableScan
Cost: 3.02 Degree: 1 Resp: 3.02
Card: 6.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
DEPT[SYS_ALIAS_1]#0
***********************
Best so far:
Table#: 0 cost:
3.0163 card: 6.0000 bytes: 30
*********************************
Number of join permutations
tried: 1
*********************************
Final adjusted join
cardinality: 1, sq. fil. factor: 6
Final - All Rows
Plan: Best join order: 1
Cost: 3.0163 Degree: 1 Card: 1.0000
Bytes: 30
Resc: 3.0163 Resc_io: 3.0000 Resc_cpu: 36747
Resp: 3.0163 Resp_io: 3.0000 Resc_cpu: 36747
kkoipt: Query block SEL$1 (#1)
******* UNPARSED QUERY IS
*******
SELECT /*+
NO_STAR_TRANSFORMATION NO_EXPAND */ "SYS_ALIAS_1"."DEPT_NO"
"DEPT_NO","SYS_ALIAS_1"."DEPT_GROUP"
"DEPT_GROUP" FROM "JEREMY"."DEPT"
"SYS_ALIAS_1" WHERE
EXISTS (SELECT /*+ */ 0 FROM "JEREMY"."EMP"
"INNER","JEREMY"."EMP"
"OUTER" WHERE "OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "INNER"."DEPT_NO">="SYS_ALIAS_1"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL"))
kkoqbc-end
: call(in-use=203012,
alloc=212872), compile(in-use=119412, alloc=155152)
First K Rows: Setup end
***********************
kkoqbc-start
: call(in-use=201644,
alloc=212872), compile(in-use=119440, alloc=155152)
****************
QUERY BLOCK TEXT
****************
select 'X'
from emp outer
where outer.sal =
(select /* no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no
>= outer.dept_no
) and outer.dept_no=dept.dept_no
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$B828357B nbfros=2 flg=0
fro(0): flg=0 objn=51987
hint_alias="OUTER"@"SEL$2"
fro(1): flg=0
objn=51987 hint_alias="INNER"@"SEL$3"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: EMP Alias: OUTER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Column (#1001): ROWID(ROWID) NO STATISTICS (using defaults)
AvgLen: 10.00 NDV: 20000 Nulls: 0 Density: 5.0000e-05
Column
(#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density:
0.16667 Min: 0 Max: 5
Column (#2): SAL(NUMBER)
AvgLen: 5.00 NDV: 20000 Nulls: 0 Density:
5.0000e-05 Min: 1 Max: 20000
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***********************
Table Stats::
Table: EMP Alias: INNER
#Rows: 20000 #Blks:
244 AvgRowLen: 72.00
Column (#2): SAL(NUMBER)
AvgLen: 5.00 NDV: 20000 Nulls: 0 Density:
5.0000e-05 Min: 1 Max: 20000
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density:
0.16667 Min: 0 Max: 5
Index Stats::
Index: E_PK
LVLS: 1 #LB: 37 #DK: 20000
LB/K: 1.00 DB/K: 1.00 CLUF: 216.00
***************************************
SINGLE TABLE ACCESS PATH
Table: EMP Alias: INNER
Card: Original: 20000 Rounded: 1000 Computed: 1000.00 Non Adjusted: 1000.00
Access Path: TableScan
Cost:
57.55 Resp:
57.55 Degree: 0
Cost_io: 55.00 Cost_cpu: 5757631
Resp_io: 55.00 Resp_cpu: 5757631
Best:: AccessPath:
TableScan
Cost: 57.55 Degree: 1 Resp: 57.55
Card: 1000.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: EMP Alias: OUTER
Card: Original: 20000 Rounded: 3333 Computed: 3333.33 Non Adjusted: 3333.33
Access Path: TableScan
Cost:
57.57 Resp:
57.57 Degree: 0
Cost_io: 55.00 Cost_cpu: 5804291
Resp_io: 55.00 Resp_cpu: 5804291
Best:: AccessPath:
TableScan
Cost: 57.57 Degree: 1 Resp: 57.57
Card: 3333.33 Bytes: 0
Grouping column cardinality
[ SAL] 3333
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
EMP[INNER]#0 EMP[OUTER]#1
***************
Now joining: EMP[OUTER]#1
***************
NL Join
Outer table: Card: 1000.00 Cost: 57.55 Resp: 57.55
Degree: 1 Bytes: 8
Inner table: EMP Alias: OUTER
Access Path: TableScan
NL Join:
Cost: 56001.13
Resp: 56001.13 Degree: 0
Cost_io: 53431.00 Cost_cpu: 5810048991
Resp_io: 53431.00 Resp_cpu: 5810048991
Best NL cost: 56001.13
resc:
56001.13 resc_io: 53431.00 resc_cpu: 5810048991
resp:
56001.13 resp_io: 53431.00 resp_cpu: 5810048991
Join Card: 166666.67 = outer (1000.00) * inner (3333.33)
* sel (0.05)
Join Card - Rounded: 166667
Computed: 166666.67
Grouping column cardinality
[ SAL] 3333
SM Join
Outer table:
resc: 57.55 card 1000.00
bytes: 8 deg: 1 resp: 57.55
Inner table: EMP Alias: OUTER
resc: 57.57 card: 3333.33
bytes: 35 deg: 1 resp: 57.57
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 3 Row size: 19 Total Rows: 1000
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 2709612
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 20 Row size: 49 Total Rows: 3333
Initial runs: 2 Merge passes: 1 IO Cost / pass: 12
Total IO sort cost: 32 Total CPU sort cost: 4510472
Total Temp space used: 369000
SM join: Resc: 157.53 Resp: 157.53 [multiMatchCost=7.23]
SM cost: 157.53
resc: 157.53
resc_io: 142.00 resc_cpu: 35115406
resp: 157.53
resp_io: 142.00 resp_cpu: 35115406
GROUP BY sort
GROUP BY cardinality: 3333.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT
resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1184 Row size: 58 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Best:: JoinMethod: SortMerge
Cost: 216.15 Degree: 1 Resp: 216.15
Card: 166666.67 Bytes: 43
***********************
Best so far:
Table#: 0 cost:
57.5469 card: 1000.0000 bytes: 8000
Table#: 1 cost: 216.1542 card: 166666.6667 bytes: 7166681
***********************
Join order[2]:
EMP[OUTER]#1 EMP[INNER]#0
***************
Now joining: EMP[INNER]#0
***************
NL Join
Outer table: Card: 3333.33 Cost: 57.57 Resp: 57.57
Degree: 1 Bytes: 35
Inner table: EMP Alias: INNER
Access Path: TableScan
NL Join:
Cost: 186447.52
Resp: 186447.52 Degree: 0
Cost_io: 177956.00 Cost_cpu: 19195989614
Resp_io: 177956.00 Resp_cpu: 19195989614
Best NL cost: 186447.52
resc:
186447.52 resc_io: 177956.00 resc_cpu: 19195989614
resp:
186447.52 resp_io: 177956.00 resp_cpu: 19195989614
Join Card: 166666.67 = outer (3333.33) * inner (1000.00)
* sel (0.05)
Join Card - Rounded: 166667
Computed: 166666.67
Grouping column cardinality
[ SAL] 3333
SM Join
Outer table:
resc: 57.57 card 3333.33
bytes: 35 deg: 1 resp: 57.57
Inner table: EMP Alias: INNER
resc: 57.55 card: 1000.00
bytes: 8 deg: 1 resp: 57.55
using dmeth:
2 #groups: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 20 Row size: 49 Total Rows: 3333
Initial runs: 2 Merge passes: 1 IO Cost / pass: 12
Total IO sort cost: 32 Total CPU sort cost: 4510472
Total Temp space used: 369000
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 3 Row size: 19 Total Rows: 1000
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 2709612
Total Temp space used: 0
SM join: Resc: 157.53 Resp: 157.53 [multiMatchCost=7.23]
SM cost: 157.53
resc: 157.53
resc_io: 142.00 resc_cpu: 35115406
resp: 157.53
resp_io: 142.00 resp_cpu: 35115406
GROUP BY sort
GROUP BY cardinality: 3333.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1184 Row size: 58 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Join order
aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations
tried: 2
*********************************
(newjo-save) [1 0 ]
GROUP BY cardinality: 1.00, TABLE cardinality: 166667.00
HAVING selectivity:
5.0000e-05 ->
GROUPS: 1
SORT resource Sort statistics
Sort width: 17 Area size: 131072 Max Area size: 3354624
Degree: 1
Blocks to Sort: 1184 Row size: 58 Total Rows: 166667
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 132518194
Total Temp space used: 0
Final - All Rows
Plan: Best join order: 1
Cost: 216.1542 Degree: 1 Card: 166667.0000 Bytes: 7166681
Resc: 216.1542 Resc_io: 142.0000 Resc_cpu: 167633600
Resp: 216.1542 Resp_io: 142.0000 Resc_cpu: 167633600
kkoipt: Query block SEL$B828357B (#2)
******* UNPARSED QUERY IS
*******
SELECT /*+ */ 0 FROM
"JEREMY"."EMP" "INNER","JEREMY"."EMP" "OUTER" WHERE "INNER"."DEPT_NO">="SYS_ALIAS_1"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL")
kkoqbc-end
: call(in-use=225356,
alloc=245624), compile(in-use=120284, alloc=155152)
kkoqbc-start
: call(in-use=225356,
alloc=245624), compile(in-use=120284, alloc=155152)
****************
QUERY BLOCK TEXT
****************
select *
from dept
where exists (
select 'X'
from emp outer
where outer.sal =
(select /* no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no
>= outer.dept_no
) and outer.dept_no=dept.dept_no
)
*********************
QUERY BLOCK
SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0
objn=51989 hint_alias="DEPT"@"SEL$1"
*****************************
SYSTEM STATISTICS
INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 188 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond
(default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL
INFORMATION
***********************
Table Stats::
Table: DEPT Alias: SYS_ALIAS_1
#Rows: 6 #Blks:
5 AvgRowLen: 5.00
Column (#1): DEPT_NO(NUMBER)
AvgLen: 3.00 NDV: 6 Nulls: 0 Density: 0.16667 Min: 0 Max:
5
Column
(#2): DEPT_GROUP(NUMBER)
AvgLen: 3.00 NDV: 2 Nulls: 0 Density: 0.5
Min: 1 Max: 2
Index Stats::
Index: D_PK
LVLS: 0 #LB: 1
#DK: 6 LB/K: 1.00 DB/K: 1.00
CLUF: 1.00
***************************************
SINGLE TABLE ACCESS PATH
Table: DEPT Alias: SYS_ALIAS_1
Card: Original: 6 Rounded: 6 Computed: 6.00 Non Adjusted: 6.00
Access Path: TableScan
Cost:
3.02 Resp:
3.02 Degree: 0
Cost_io: 3.00 Cost_cpu: 36747
Resp_io: 3.00 Resp_cpu: 36747
Best:: AccessPath:
TableScan
Cost: 3.02 Degree: 1 Resp: 3.02
Card: 6.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS
AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering
cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:
DEPT[SYS_ALIAS_1]#0
***********************
Best so far: Table#: 0 cost: 3.0163 card: 6.0000
bytes: 30
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations
tried: 1
*********************************
Final adjusted join
cardinality: 1, sq. fil. factor: 6
Final - All Rows
Plan: Best join order: 1
Cost: 651.4789 Degree: 1 Card: 1.0000
Bytes: 30
Resc: 651.4789 Resc_io: 429.0000 Resc_cpu: 502937548
Resp: 651.4789 Resp_io: 429.0000 Resc_cpu: 502937548
kkoipt: Query block SEL$1 (#1)
******* UNPARSED QUERY IS
*******
SELECT /*+ */
"SYS_ALIAS_1"."DEPT_NO"
"DEPT_NO","SYS_ALIAS_1"."DEPT_GROUP"
"DEPT_GROUP" FROM "JEREMY"."DEPT"
"SYS_ALIAS_1" WHERE
EXISTS (SELECT /*+ */ 0 FROM "JEREMY"."EMP"
"INNER","JEREMY"."EMP"
"OUTER" WHERE "INNER"."DEPT_NO">="SYS_ALIAS_1"."DEPT_NO" AND "INNER"."DEPT_NO">="OUTER"."DEPT_NO" AND "OUTER"."DEPT_NO"="SYS_ALIAS_1"."DEPT_NO"
GROUP BY "OUTER".ROWID,"OUTER".ROWID,"OUTER"."SAL" HAVING "OUTER"."SAL"=MAX("INNER"."SAL"))
kkoqbc-end
: call(in-use=238172,
alloc=245624), compile(in-use=120516, alloc=155152)
apadrv-end: call(in-use=238172, alloc=245624),
compile(in-use=121704, alloc=155152)
sql_id=1w7uy3mdggx1f.
Current SQL statement for
this session:
explain plan for
select *
from dept
where exists (
select 'X'
from emp outer
where outer.sal =
(select /* no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no
>= outer.dept_no
) and outer.dept_no=dept.dept_no
)
============
Plan Table
============
------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows
| Bytes | Cost | Time |
------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | |
| | 651 | |
| 1 | FILTER | |
| | | |
| 2 |
TABLE ACCESS FULL | DEPT |
6 | 30 | 3 | 00:00:01 |
| 3 |
FILTER | |
| | | |
| 4 |
HASH GROUP BY | |
1 | 43 | 216 | 00:00:03 |
| 5 |
MERGE JOIN | | 163K | 6999K | 158 |
00:00:02 |
| 6 |
SORT JOIN | | 1000 |
8000 | 59 | 00:00:01 |
| 7 |
TABLE ACCESS FULL | EMP | 1000 | 8000 |
58 | 00:00:01 |
| 8 |
SORT JOIN | | 3333 |
114K | 92 | 00:00:02 |
| 9 |
TABLE ACCESS FULL | EMP | 3333 | 114K |
58 | 00:00:01 |
------------------------------------------+-----------------------------------+
Predicate
Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("OUTER"."SAL"=MAX("INNER"."SAL"))
7 - filter("INNER"."DEPT_NO">=:B1)
8 - access(INTERNAL_FUNCTION("INNER"."DEPT_NO")>=INTERNAL_FUNCTION("OUTER"."DEPT_NO"))
8 - filter(INTERNAL_FUNCTION("INNER"."DEPT_NO")>=INTERNAL_FUNCTION("OUTER"."DEPT_NO"))
9 - filter("OUTER"."DEPT_NO"=:B1)
Content of other_xml column
===========================
db_version : 10.2.0.2
parse_schema : JEREMY
plan_hash : 1007617972
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B828357B")
MERGE(@"SEL$12EAFFF1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$EEC73E22")
UNNEST(@"SEL$3")
OUTLINE(@"SEL$12EAFFF1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$4111C18D")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$5D0CF27A")
OUTLINE(@"SEL$2")
FULL(@"SEL$1"
"DEPT"@"SEL$1")
FULL(@"SEL$B828357B"
"INNER"@"SEL$3")
FULL(@"SEL$B828357B"
"OUTER"@"SEL$2")
LEADING(@"SEL$B828357B"
"INNER"@"SEL$3" "OUTER"@"SEL$2")
USE_MERGE(@"SEL$B828357B"
"OUTER"@"SEL$2")
END_OUTLINE_DATA
*/
Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.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 = 10.2.0.2
_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 = 16
_optimizer_max_permutations = 2000
pga_aggregate_target = 16384 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 = 128 KB
_smm_max_size = 3276 KB
_smm_px_max_size = 8192 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.2.0.2
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
_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
_fix_control_key = 0
_optimizer_skip_scan_guess = false
_enable_row_shipping = false
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
Query Block Registry:
*********************
SEL$3 0x2530a8c8 (PARSER)
SEL$EEC73E22 0x2531102c (SUBQUERY UNNEST
SEL$4111C18D; SEL$3)
SEL$B828357B 0x2531102c (VIEW MERGE
SEL$EEC73E22; SEL$12EAFFF1) [FINAL]
SEL$5D0CF27A 0x2530a8c8 (QUERY BLOCK TABLES
CHANGED SEL$3)
SEL$12EAFFF1 0x2530a8c8 (SUBQ INTO VIEW FOR
COMPLEX UNNEST SEL$5D0CF27A)
SEL$B828357B 0x2531102c (VIEW MERGE
SEL$EEC73E22; SEL$12EAFFF1) [FINAL]
SEL$2 0x2531102c (PARSER)
SEL$4111C18D 0x2531102c (VIEW ADDED
SEL$2)
SEL$EEC73E22 0x2531102c (SUBQUERY
UNNEST SEL$4111C18D; SEL$3)
...
SEL$1 0x25311424 (PARSER) [FINAL]