*************** Case 1 *************** SQL: select /*+FULL(t4)*/* from t4; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ FULL(@"SEL$1" "opt.t4"@"SEL$1") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 1(end) ************** *************** Case 2 *************** SQL: select /*+INDEX(t4 idx_t4_c2_c3)*/* from t4; =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c3], [t4.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3], pruned_index_name[idx_t4_c3,idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 2(end) ************** *************** Case 3 *************** SQL: select /*+INDEX(t4 idx_t4_c2)*/* from t4; ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c2)|100 |614 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=true, range_key([t4.c2], [t4.c1]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:100, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,t4] Parameters ------------------------------------- *************** Case 3(end) ************** *************** Case 4 *************** SQL: select /*+INDEX(t4 idx_t4_c3)*/* from t4; ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c3], pruned_index_name[idx_t4_c2_c3,idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 4(end) ************** *************** Case 5 *************** SQL: select /*+INDEX(t_pt t_pt_idx_1)*/* from t_pt where (c2,c1) > (300,200); ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------ |0 |PX COORDINATOR | |10 |617 | |1 | EXCHANGE OUT DISTR |:EX10000 |10 |615 | |2 | PX PARTITION ITERATOR| |10 |615 | |3 | TABLE SCAN |t_pt(t_pt_idx_1)|10 |615 | ============================================================ Outputs & filters: ------------------------------------- 0 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil) 1 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil), dop=1 2 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil), force partition granule, asc. 3 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter([(t_pt.c2, t_pt.c1) > (?, ?)]), access([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), partitions(p[0-9]), is_index_back=true, filter_before_indexback[true], range_key([t_pt.c2], [t_pt.c1]), range(300,200 ; MAX,MAX) Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t_pt"@"SEL$1" "t_pt_idx_1") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t_pt"@"SEL$1" "t_pt_idx_1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t_pt:table_rows:1000, physical_range_rows:1000, logical_range_rows:1000, index_back_rows:9, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t_pt_idx_1], pruned_index_name[t_pt] Parameters ------------------------------------- {obj:{"BIGINT":300}, accuracy:{length:3, precision:3, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}}, {obj:{"BIGINT":200}, accuracy:{length:3, precision:3, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}} *************** Case 5(end) ************** *************** Case 6 *************** SQL: select /*+INDEX(t_pt primary)*/* from t_pt where (c2,c1) > (300,200); ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |10 |777 | |1 | EXCHANGE OUT DISTR |:EX10000|10 |775 | |2 | PX PARTITION ITERATOR| |10 |775 | |3 | TABLE SCAN |t_pt |10 |775 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil) 1 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil), dop=1 2 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil), force partition granule, asc. 3 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter([(t_pt.c2, t_pt.c1) > (?, ?)]), access([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), partitions(p[0-9]), is_index_back=false, filter_before_indexback[false], range_key([t_pt.c1], [t_pt.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ FULL(@"SEL$1" "opt.t_pt"@"SEL$1") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t_pt"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t_pt:table_rows:1000, physical_range_rows:1000, logical_range_rows:1000, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t_pt], pruned_index_name[t_pt_idx_1] Parameters ------------------------------------- {obj:{"BIGINT":300}, accuracy:{length:3, precision:3, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}}, {obj:{"BIGINT":200}, accuracy:{length:3, precision:3, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}} *************** Case 6(end) ************** *************** Case 7 *************** SQL: select /*+INDEX(t_pt primary)*/* from t_pt where (c1,c2) > (200,300); ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |10 |777 | |1 | EXCHANGE OUT DISTR |:EX10000|10 |775 | |2 | PX PARTITION ITERATOR| |10 |775 | |3 | TABLE SCAN |t_pt |10 |775 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil) 1 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil), dop=1 2 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter(nil), force partition granule, asc. 3 - output([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), filter([(t_pt.c1, t_pt.c2) > (?, ?)]), access([t_pt.c1], [t_pt.c2], [t_pt.c3], [t_pt.c4]), partitions(p[0-9]), is_index_back=false, filter_before_indexback[false], range_key([t_pt.c1], [t_pt.c2]), range(200,300 ; MAX,MAX) Used Hint: ------------------------------------- /*+ FULL(@"SEL$1" "opt.t_pt"@"SEL$1") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t_pt"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t_pt:table_rows:1000, physical_range_rows:1000, logical_range_rows:1000, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t_pt], pruned_index_name[t_pt_idx_1] Parameters ------------------------------------- {obj:{"BIGINT":200}, accuracy:{length:3, precision:3, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}}, {obj:{"BIGINT":300}, accuracy:{length:3, precision:3, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}} *************** Case 7(end) ************** *************** Case 8 *************** SQL: select /*+INDEX(t4 idx_t4_invalid)*/* from t4; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 8(end) ************** *************** Case 9 *************** SQL: select /*+INDEX(t4 idx_t4_c3) INDEX(t4 idx_t4_c2) INDEX(t4 idx_t4_c2_c3) */* from t4; ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3], pruned_index_name[idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 9(end) ************** *************** Case 10 *************** SQL: select * from t4 use index(idx_t4_c3, idx_t4_c2, idx_t4_c2_c3); ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3], pruned_index_name[idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 10(end) ************** *************** Case 11 *************** SQL: select * from t4 force index(idx_t4_c3, idx_t4_c2, idx_t4_c2_c3); ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3], pruned_index_name[idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 11(end) ************** *************** Case 12 *************** SQL: select /*+INDEX(t4 idx_t4_c3) FULL(t4) */* from t4; ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c3,t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 12(end) ************** *************** Case 13 *************** SQL: select /*+INDEX(t4 idx_t4_c3) INDEX(t5 idx_t5_c2)*/* from t4,t5; ============================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |23888| |1 | PX COORDINATOR | |300 |1885 | |2 | EXCHANGE OUT DISTR |:EX10000 |300 |1842 | |3 | PX PARTITION ITERATOR | |300 |1842 | |4 | TABLE SCAN |t5(idx_t5_c2)|300 |1842 | |5 | MATERIAL | |100 |148 | |6 | TABLE SCAN |t4(idx_t4_c3)|100 |92 | ============================================================= Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=true, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil) 6 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t5"@"SEL$1" "idx_t5_c2") INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t4"@"SEL$1" )) INDEX(@"SEL$1" "opt.t5"@"SEL$1" "idx_t5_c2") INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:300, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t5_c2], pruned_index_name[idx_t5_c3,t5] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c3], pruned_index_name[idx_t4_c2_c3,idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 13(end) ************** *************** Case 14 *************** SQL: select /*+INDEX(t4 idx_t4_c3)*/* from t4 as tt; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|tt |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c1], [tt.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.tt"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 14(end) ************** *************** Case 15 *************** SQL: select /*+INDEX(tt idx_t4_c3)*/* from t4 as tt; ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|tt(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c3], [tt.c1], [tt.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c3], pruned_index_name[idx_t4_c2_c3,idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 15(end) ************** *************** Case 16 *************** SQL: select * from t4 use index(); =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 16(end) ************** *************** Case 17 *************** SQL: select /*+INDEX(t4 idx_t4_c3) */* from t4 ignore index(idx_t4_c3); ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c3)|100 |92 | ============================================ Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c3], [t4.c1], [t4.c2]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c3], pruned_index_name[idx_t4_c2_c3,idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 17(end) ************** *************** Case 18 *************** SQL: select * from t4 ignore index(idx_t4_c3); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c3], [t4.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,t4], pruned_index_name[idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 18(end) ************** *************** Case 19 *************** SQL: select * from t4 ignore index(idx_t4_c2); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c3], [t4.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 19(end) ************** *************** Case 20 *************** SQL: select * from t4 ignore index(idx_t4_c3, idx_t4_c2); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c3], [t4.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") NO_INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,t4], pruned_index_name[idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 20(end) ************** *************** Case 21 *************** SQL: select * from t4 ignore index(idx_t4_c3) ignore index(idx_t4_c2); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|t4(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c3], [t4.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.t4"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,t4], pruned_index_name[idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 21(end) ************** *************** Case 22 *************** SQL: select * from t4 tt ignore index(idx_t4_c3, idx_t4_c2); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|tt(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c2], [tt.c3], [tt.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c3") NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,t4], pruned_index_name[idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 22(end) ************** *************** Case 23 *************** SQL: select * from t4 tt ignore index(idx_t4_c3) ignore index(idx_t4_c2); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|tt(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c2], [tt.c3], [tt.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c3") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,t4], pruned_index_name[idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 23(end) ************** *************** Case 24 *************** SQL: select * from t4 tt ignore index(primary, idx_t4_c2); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|tt(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c2], [tt.c3], [tt.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "primary") NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3], pruned_index_name[idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 24(end) ************** *************** Case 25 *************** SQL: select * from t4 tt ignore index(primary) ignore index(idx_t4_c3); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|tt(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c2], [tt.c3], [tt.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "primary") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3], pruned_index_name[idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 25(end) ************** *************** Case 26 *************** SQL: select * from t4 tt ignore index(primary) ignore index(idx_t4_c2_); =============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------- |0 |TABLE SCAN|tt(idx_t4_c2_c3)|100 |92 | =============================================== Outputs & filters: ------------------------------------- 0 - output([tt.c1], [tt.c2], [tt.c3]), filter(nil), access([tt.c1], [tt.c2], [tt.c3]), partitions(p0), is_index_back=false, range_key([tt.c2], [tt.c3], [tt.c1]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true Used Hint: ------------------------------------- /*+ NO_INDEX(@"SEL$1" "opt.tt"@"SEL$1" "primary") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "opt.tt"@"SEL$1" "idx_t4_c2_c3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- tt:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3], pruned_index_name[idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 26(end) ************** *************** Case 27 *************** SQL: select * from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22250| |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |100 |148 | |6 | TABLE SCAN |t4 |100 |92 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil) 6 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 27(end) ************** *************** Case 28 *************** SQL: select /*+leading(t4 t5) use_nl(t5)*/* from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22360| |1 | TABLE SCAN |t4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 28(end) ************** *************** Case 29 *************** SQL: select /*+leading(tt4 tt5) use_nl(tt5)*/* from t4 tt4, t5 tt5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22360| |1 | TABLE SCAN |tt4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |tt5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([tt4.c1], [tt4.c2], [tt4.c3], [tt5.c1], [tt5.c2], [tt5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([tt4.c1], [tt4.c2], [tt4.c3]), filter(nil), access([tt4.c1], [tt4.c2], [tt4.c3]), partitions(p0), is_index_back=false, range_key([tt4.c1], [tt4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([tt5.c1], [tt5.c2], [tt5.c3]), filter(nil) 3 - output([tt5.c2], [tt5.c3], [tt5.c1]), filter(nil) 4 - output([tt5.c2], [tt5.c3], [tt5.c1]), filter(nil), dop=1 5 - output([tt5.c2], [tt5.c3], [tt5.c1]), filter(nil), force partition granule, asc. 6 - output([tt5.c2], [tt5.c3], [tt5.c1]), filter(nil), access([tt5.c2], [tt5.c3], [tt5.c1]), partitions(p[0-2]), is_index_back=false, range_key([tt5.c2], [tt5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.tt4"@"SEL$1" "opt.tt5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.tt5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.tt4"@"SEL$1" "opt.tt5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.tt5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.tt5"@"SEL$1" )) FULL(@"SEL$1" "opt.tt4"@"SEL$1") FULL(@"SEL$1" "opt.tt5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- tt4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] tt5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 29(end) ************** *************** Case 30 *************** SQL: select /*+leading (t4 t5) use_nl(t5) use_nl(t5) use_nl(t5)*/* from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22360| |1 | TABLE SCAN |t4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 30(end) ************** *************** Case 31 *************** SQL: select /*+use_nl(t4) use_nl(t5)*/* from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22250| |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |100 |148 | |6 | TABLE SCAN |t4 |100 |92 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil) 6 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 31(end) ************** *************** Case 32 *************** SQL: select /*+leading (t4 t5) use_nl(t5)*/* from t4 left join t5 on t4.c1 = t5.c1; ====================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------ |0 |NESTED-LOOP OUTER JOIN | |290 |9739| |1 | TABLE SCAN |t4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR| |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ====================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds([t4.c1 = t5.c1]), nl_params_(nil), batch_join=false 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 32(end) ************** *************** Case 33 *************** SQL: select /*+leading (t5 t4) use_nl(t5)*/* from t4 left join t5 on t4.c1 = t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH RIGHT OUTER JOIN | |290 |983 | |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR| |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | TABLE SCAN |t4 |100 |92 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 33(end) ************** *************** Case 34 *************** SQL: select /*+leading (t4 t5) use_nl(t5)*/* from t4 right join t5 on t4.c1 = t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH RIGHT OUTER JOIN | |300 |756 | |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR | |300 |247 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |4 | PX PARTITION ITERATOR| |300 |205 | |5 | TABLE SCAN |t5 |300 |205 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 34(end) ************** *************** Case 35 *************** SQL: select /*+leading (t5 t4) use_nl(t5)*/* from t4 right join t5 on t4.c1 = t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH OUTER JOIN | |300 |983 | |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR| |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | TABLE SCAN |t4 |100 |92 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 35(end) ************** *************** Case 36 *************** SQL: select /*+leading (t4 t5) use_nl(t5)*/* from t4 full join t5 on t4.c1 = t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH FULL OUTER JOIN | |300 |756 | |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR | |300 |247 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |4 | PX PARTITION ITERATOR| |300 |205 | |5 | TABLE SCAN |t5 |300 |205 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 36(end) ************** *************** Case 37 *************** SQL: select /*+leading (t5 t4) use_nl(t5)*/* from t4 full join t5 on t4.c1 = t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH FULL OUTER JOIN | |300 |983 | |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR| |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | TABLE SCAN |t4 |100 |92 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 37(end) ************** *************** Case 38 *************** SQL: select /*+leading (t4 t5) use_merge(t5)*/* from t4 left join t5 on t4.c1 = t5.c1; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------- |0 |MERGE OUTER JOIN | |290 |1046| |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR MERGE SORT | |300 |727 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |684 | |4 | SORT | |300 |684 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil), merge_directions([ASC]) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), sort_keys([t5.c1, ASC]) 3 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), dop=1 4 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), sort_keys([t5.c1, ASC]) 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ USE_MERGE(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_MERGE(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 38(end) ************** *************** Case 39 *************** SQL: select /*+leading (t4 t5) use_merge(t5)*/* from t4 right join t5 on t4.c1 = t5.c1; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------- |0 |MERGE RIGHT OUTER JOIN | |300 |1046| |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR MERGE SORT | |300 |727 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |684 | |4 | SORT | |300 |684 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil), merge_directions([ASC]) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), sort_keys([t5.c1, ASC]) 3 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), dop=1 4 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), sort_keys([t5.c1, ASC]) 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ USE_MERGE(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_MERGE(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 39(end) ************** *************** Case 40 *************** SQL: select /*+leading (t4 t5) use_merge(t5)*/* from t4 full join t5 on t4.c1 = t5.c1; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------- |0 |MERGE FULL OUTER JOIN | |300 |1046| |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR MERGE SORT | |300 |727 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |684 | |4 | SORT | |300 |684 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil), merge_directions([ASC]) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), sort_keys([t5.c1, ASC]) 3 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), dop=1 4 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil), sort_keys([t5.c1, ASC]) 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ USE_MERGE(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_MERGE(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 40(end) ************** *************** Case 41 *************** SQL: select /*+leading(tt t5) use_nl(t5)*/* from (select max(c1) from t1) tt, t5; =============================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN | |300 |479 | |1 | SUBPLAN SCAN |tt |1 |38 | |2 | SCALAR GROUP BY | |1 |38 | |3 | SUBPLAN SCAN |VIEW1 |1 |37 | |4 | LIMIT | |1 |37 | |5 | PX COORDINATOR MERGE SORT | |1 |37 | |6 | EXCHANGE OUT DISTR |:EX10000 |1 |37 | |7 | LIMIT | |1 |37 | |8 | TOP-N SORT | |1 |37 | |9 | PX PARTITION ITERATOR | |1 |36 | |10| TABLE SCAN |t1(Reverse)|1 |36 | |11| PX COORDINATOR | |300 |247 | |12| EXCHANGE OUT DISTR |:EX20000 |300 |205 | |13| PX PARTITION ITERATOR | |300 |205 | |14| TABLE SCAN |t5 |300 |205 | =============================================================== Outputs & filters: ------------------------------------- 0 - output([tt.max(c1)], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([tt.max(c1)]), filter(nil), access([tt.max(c1)]) 2 - output([T_FUN_MAX(VIEW1.c1)]), filter(nil), group(nil), agg_func([T_FUN_MAX(VIEW1.c1)]) 3 - output([VIEW1.c1]), filter(nil), access([VIEW1.c1]) 4 - output([t1.c1]), filter(nil), limit(1), offset(nil) 5 - output([t1.c1]), filter(nil), sort_keys([t1.c1, DESC]) 6 - output([t1.c1]), filter(nil), dop=1 7 - output([t1.c1]), filter(nil), limit(1), offset(nil) 8 - output([t1.c1]), filter(nil), sort_keys([t1.c1, DESC]), topn(1), local merge sort 9 - output([t1.c1]), filter(nil), force partition granule, asc. 10 - output([t1.c1]), filter(nil), access([t1.c1]), partitions(p[0-4]), limit(1), offset(nil), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true 11 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 12 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 13 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 14 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("tt"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("tt"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$3" "opt.t1"@"SEL$3") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t1], pruned_index_name[idx_t1_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 41(end) ************** *************** Case 42 *************** SQL: select /*+leading(t5 tt) use_nl(tt)*/* from (select max(c1) from t1) tt, t5; ================================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN | |300 |497 | |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000 |300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |1 |38 | |6 | SUBPLAN SCAN |tt |1 |38 | |7 | SCALAR GROUP BY | |1 |38 | |8 | SUBPLAN SCAN |VIEW1 |1 |37 | |9 | LIMIT | |1 |37 | |10| PX COORDINATOR MERGE SORT | |1 |37 | |11| EXCHANGE OUT DISTR |:EX20000 |1 |37 | |12| LIMIT | |1 |37 | |13| TOP-N SORT | |1 |37 | |14| PX PARTITION ITERATOR | |1 |36 | |15| TABLE SCAN |t1(Reverse)|1 |36 | ================================================================ Outputs & filters: ------------------------------------- 0 - output([tt.max(c1)], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([tt.max(c1)]), filter(nil) 6 - output([tt.max(c1)]), filter(nil), access([tt.max(c1)]) 7 - output([T_FUN_MAX(VIEW1.c1)]), filter(nil), group(nil), agg_func([T_FUN_MAX(VIEW1.c1)]) 8 - output([VIEW1.c1]), filter(nil), access([VIEW1.c1]) 9 - output([t1.c1]), filter(nil), limit(1), offset(nil) 10 - output([t1.c1]), filter(nil), sort_keys([t1.c1, DESC]) 11 - output([t1.c1]), filter(nil), dop=1 12 - output([t1.c1]), filter(nil), limit(1), offset(nil) 13 - output([t1.c1]), filter(nil), sort_keys([t1.c1, DESC]), topn(1), local merge sort 14 - output([t1.c1]), filter(nil), force partition granule, asc. 15 - output([t1.c1]), filter(nil), access([t1.c1]), partitions(p[0-4]), limit(1), offset(nil), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "tt"@"SEL$1" )) USE_NL(@"SEL$1" ("tt"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "tt"@"SEL$1" )) USE_NL(@"SEL$1" ("tt"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("tt"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$3" "opt.t1"@"SEL$3") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t1], pruned_index_name[idx_t1_c2] Parameters ------------------------------------- *************** Case 42(end) ************** *************** Case 43 *************** SQL: select /*+leading(t4 t5) use_bnl(t5)*/* from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22250| |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |100 |148 | |6 | TABLE SCAN |t4 |100 |92 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil) 6 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 43(end) ************** *************** Case 44 *************** SQL: select /*+leading(t4 t5) use_bnl(t5)*/* from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22250| |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |100 |148 | |6 | TABLE SCAN |t4 |100 |92 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil) 6 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 44(end) ************** *************** Case 45 *************** SQL: select /*+leading(tt t5) use_nl(t5)*/* from (select /*+leading(t4 t7) use_nl(t7)*/max(t4.c1) from t4,t7) tt, t5; =============================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN | |300 |9588| |1 | SUBPLAN SCAN |tt |1 |9147| |2 | SCALAR GROUP BY | |1 |9147| |3 | NESTED-LOOP JOIN CARTESIAN| |10000 |7237| |4 | TABLE SCAN |t4(idx_t4_c2)|100 |54 | |5 | MATERIAL | |100 |106 | |6 | TABLE SCAN |t7 |100 |88 | |7 | PX COORDINATOR | |300 |247 | |8 | EXCHANGE OUT DISTR |:EX10000 |300 |205 | |9 | PX PARTITION ITERATOR | |300 |205 | |10| TABLE SCAN |t5 |300 |205 | =============================================================== Outputs & filters: ------------------------------------- 0 - output([tt.max(t4.c1)], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([tt.max(t4.c1)]), filter(nil), access([tt.max(t4.c1)]) 2 - output([T_FUN_MAX(t4.c1)]), filter(nil), group(nil), agg_func([T_FUN_MAX(t4.c1)]) 3 - output([t4.c1]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 4 - output([t4.c1]), filter(nil), access([t4.c1]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c1]), range(MIN,MIN ; MAX,MAX)always true 5 - output([1]), filter(nil) 6 - output([1]), filter(nil), access([t7.c1]), partitions(p0), is_index_back=false, range_key([t7.c1]), range(MIN ; MAX)always true 7 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 8 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 9 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 10 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("tt"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) LEADING(@"SEL$2" ("opt.t4"@"SEL$2" "opt.t7"@"SEL$2" )) USE_NL(@"SEL$2" ("opt.t7"@"SEL$2" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("tt"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) LEADING(@"SEL$2" ("opt.t4"@"SEL$2" "opt.t7"@"SEL$2" )) USE_NL(@"SEL$2" ("opt.t7"@"SEL$2" )) USE_NL_MATERIALIZATION(@"SEL$2" ("opt.t7"@"SEL$2" )) INDEX(@"SEL$2" "opt.t4"@"SEL$2" "idx_t4_c2") FULL(@"SEL$2" "opt.t7"@"SEL$2") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2,t4] t7:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t7] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 45(end) ************** *************** Case 46 *************** SQL: select /*+leading(t5 tt) use_nl(tt)*/* from (select /*+leading(t7 t4) use_nl(t4)*/max(t4.c1) from t4,t7) tt, t5; ================================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN | |300 |9606| |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000 |300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |1 |9147| |6 | SUBPLAN SCAN |tt |1 |9147| |7 | SCALAR GROUP BY | |1 |9147| |8 | NESTED-LOOP JOIN CARTESIAN| |10000 |7237| |9 | TABLE SCAN |t7 |100 |88 | |10| MATERIAL | |100 |73 | |11| TABLE SCAN |t4(idx_t4_c2)|100 |54 | ================================================================ Outputs & filters: ------------------------------------- 0 - output([tt.max(t4.c1)], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([tt.max(t4.c1)]), filter(nil) 6 - output([tt.max(t4.c1)]), filter(nil), access([tt.max(t4.c1)]) 7 - output([T_FUN_MAX(t4.c1)]), filter(nil), group(nil), agg_func([T_FUN_MAX(t4.c1)]) 8 - output([t4.c1]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 9 - output([1]), filter(nil), access([t7.c1]), partitions(p0), is_index_back=false, range_key([t7.c1]), range(MIN ; MAX)always true 10 - output([t4.c1]), filter(nil) 11 - output([t4.c1]), filter(nil), access([t4.c1]), partitions(p0), is_index_back=false, range_key([t4.c2], [t4.c1]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "tt"@"SEL$1" )) USE_NL(@"SEL$1" ("tt"@"SEL$1" )) LEADING(@"SEL$2" ("opt.t7"@"SEL$2" "opt.t4"@"SEL$2" )) USE_NL(@"SEL$2" ("opt.t4"@"SEL$2" )) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "tt"@"SEL$1" )) USE_NL(@"SEL$1" ("tt"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("tt"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") LEADING(@"SEL$2" ("opt.t7"@"SEL$2" "opt.t4"@"SEL$2" )) USE_NL(@"SEL$2" ("opt.t4"@"SEL$2" )) USE_NL_MATERIALIZATION(@"SEL$2" ("opt.t4"@"SEL$2" )) FULL(@"SEL$2" "opt.t7"@"SEL$2") INDEX(@"SEL$2" "opt.t4"@"SEL$2" "idx_t4_c2") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t7:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t7] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2,t4] Parameters ------------------------------------- *************** Case 46(end) ************** *************** Case 47 *************** SQL: select /*+ordered*/* from t4,t5; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22360| |1 | TABLE SCAN |t4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR | |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ ORDERED */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 47(end) ************** *************** Case 48 *************** SQL: select /*+ordered*/* from t5,t4; ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |30000 |22250| |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR | |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | MATERIAL | |100 |148 | |6 | TABLE SCAN |t4 |100 |92 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([t5.c1], [t5.c2], [t5.c3], [t4.c1], [t4.c2], [t4.c3]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil) 6 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ ORDERED */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t4"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 48(end) ************** *************** Case 49 *************** SQL: select /*+ordered*/* from t5; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |300 |247 | |1 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |2 | PX PARTITION ITERATOR| |300 |205 | |3 | TABLE SCAN |t5 |300 |205 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t5.c1], [t5.c2], [t5.c3]), filter(nil) 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 49(end) ************** *************** Case 50 *************** SQL: select /*+ordered*/* from t4 left join t5 on t4.c1=t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH OUTER JOIN | |290 |756 | |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR | |300 |247 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |4 | PX PARTITION ITERATOR| |300 |205 | |5 | TABLE SCAN |t5 |300 |205 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 50(end) ************** *************** Case 51 *************** SQL: select /*+ordered*/* from t4 right join t5 on t4.c1=t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH OUTER JOIN | |300 |983 | |1 | PX COORDINATOR | |300 |247 | |2 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |3 | PX PARTITION ITERATOR| |300 |205 | |4 | TABLE SCAN |t5 |300 |205 | |5 | TABLE SCAN |t4 |100 |92 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true 5 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t5"@"SEL$1" "opt.t4"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t4"@"SEL$1" )) FULL(@"SEL$1" "opt.t5"@"SEL$1") FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] Parameters ------------------------------------- *************** Case 51(end) ************** *************** Case 52 *************** SQL: select /*+ordered*/* from t4 full join t5 on t4.c1=t5.c1; ===================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------- |0 |HASH FULL OUTER JOIN | |300 |756 | |1 | TABLE SCAN |t4 |100 |92 | |2 | PX COORDINATOR | |300 |247 | |3 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |4 | PX PARTITION ITERATOR| |300 |205 | |5 | TABLE SCAN |t5 |300 |205 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3], [t5.c1], [t5.c2], [t5.c3]), filter(nil), equal_conds([t4.c1 = t5.c1]), other_conds(nil) 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil) 3 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), dop=1 4 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), force partition granule, asc. 5 - output([t5.c2], [t5.c3], [t5.c1]), filter(nil), access([t5.c2], [t5.c3], [t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_HASH(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t4], pruned_index_name[idx_t4_c2_c3,idx_t4_c3,idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 52(end) ************** *************** Case 53 *************** SQL: select /*+index(t_normal_idx idx)*/* from t_normal_idx order by c3 limit 1; ======================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |124 | |1 | LIMIT | |1 |87 | |2 | TOP-N SORT | |1 |87 | |3 | TABLE SCAN |t_normal_idx(idx) |100 |54 | |4 | TABLE GET |t_normal_idx_alias|1 |37 | ======================================================= Outputs & filters: ------------------------------------- 0 - output([t_normal_idx.c1], [t_normal_idx_alias.c2], [t_normal_idx.c3], [t_normal_idx_alias.c4], [t_normal_idx_alias.c5], [t_normal_idx_alias.c6], [t_normal_idx_alias.c7], [t_normal_idx_alias.c8], [t_normal_idx_alias.c9], [t_normal_idx_alias.c10]), filter(nil), conds(nil), nl_params_([t_normal_idx.c1]), batch_join=true 1 - output([t_normal_idx.c1], [t_normal_idx.c3]), filter(nil), limit(1), offset(nil) 2 - output([t_normal_idx.c1], [t_normal_idx.c3]), filter(nil), sort_keys([t_normal_idx.c3, ASC]), topn(1) 3 - output([t_normal_idx.c1], [t_normal_idx.c3]), filter(nil), access([t_normal_idx.c1], [t_normal_idx.c3]), partitions(p0), is_index_back=false, range_key([t_normal_idx.c2], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c1]), range(MIN,MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX,MAX)always true 4 - output([t_normal_idx_alias.c2], [t_normal_idx_alias.c4], [t_normal_idx_alias.c5], [t_normal_idx_alias.c6], [t_normal_idx_alias.c7], [t_normal_idx_alias.c8], [t_normal_idx_alias.c9], [t_normal_idx_alias.c10]), filter(nil), access([t_normal_idx_alias.c2], [t_normal_idx_alias.c4], [t_normal_idx_alias.c5], [t_normal_idx_alias.c6], [t_normal_idx_alias.c7], [t_normal_idx_alias.c8], [t_normal_idx_alias.c9], [t_normal_idx_alias.c10]), partitions(p0), is_index_back=false, range_key([t_normal_idx_alias.c1]), range(MIN ; MAX), range_cond([t_normal_idx_alias.c1 = ?]) Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "opt.t_normal_idx"@"SEL$1" "idx") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA USE_LATE_MATERIALIZATION LEADING(@"SEL$1" ("opt.t_normal_idx"@"SEL$1" "opt.t_normal_idx_alias"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t_normal_idx_alias"@"SEL$1" )) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t_normal_idx_alias"@"SEL$1" )) INDEX(@"SEL$1" "opt.t_normal_idx"@"SEL$1" "idx") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t_normal_idx:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx], pruned_index_name[t_normal_idx] Parameters ------------------------------------- *************** Case 53(end) ************** *************** Case 54 *************** SQL: select /*+use_late_materialization index(t_normal_idx idx)*/* from t_normal_idx order by c3 limit 1; ======================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |124 | |1 | LIMIT | |1 |87 | |2 | TOP-N SORT | |1 |87 | |3 | TABLE SCAN |t_normal_idx(idx) |100 |54 | |4 | TABLE GET |t_normal_idx_alias|1 |37 | ======================================================= Outputs & filters: ------------------------------------- 0 - output([t_normal_idx.c1], [t_normal_idx_alias.c2], [t_normal_idx.c3], [t_normal_idx_alias.c4], [t_normal_idx_alias.c5], [t_normal_idx_alias.c6], [t_normal_idx_alias.c7], [t_normal_idx_alias.c8], [t_normal_idx_alias.c9], [t_normal_idx_alias.c10]), filter(nil), conds(nil), nl_params_([t_normal_idx.c1]), batch_join=true 1 - output([t_normal_idx.c1], [t_normal_idx.c3]), filter(nil), limit(1), offset(nil) 2 - output([t_normal_idx.c1], [t_normal_idx.c3]), filter(nil), sort_keys([t_normal_idx.c3, ASC]), topn(1) 3 - output([t_normal_idx.c1], [t_normal_idx.c3]), filter(nil), access([t_normal_idx.c1], [t_normal_idx.c3]), partitions(p0), is_index_back=false, range_key([t_normal_idx.c2], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c1]), range(MIN,MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX,MAX)always true 4 - output([t_normal_idx_alias.c2], [t_normal_idx_alias.c4], [t_normal_idx_alias.c5], [t_normal_idx_alias.c6], [t_normal_idx_alias.c7], [t_normal_idx_alias.c8], [t_normal_idx_alias.c9], [t_normal_idx_alias.c10]), filter(nil), access([t_normal_idx_alias.c2], [t_normal_idx_alias.c4], [t_normal_idx_alias.c5], [t_normal_idx_alias.c6], [t_normal_idx_alias.c7], [t_normal_idx_alias.c8], [t_normal_idx_alias.c9], [t_normal_idx_alias.c10]), partitions(p0), is_index_back=false, range_key([t_normal_idx_alias.c1]), range(MIN ; MAX), range_cond([t_normal_idx_alias.c1 = ?]) Used Hint: ------------------------------------- /*+ USE_LATE_MATERIALIZATION INDEX(@"SEL$1" "opt.t_normal_idx"@"SEL$1" "idx") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA USE_LATE_MATERIALIZATION LEADING(@"SEL$1" ("opt.t_normal_idx"@"SEL$1" "opt.t_normal_idx_alias"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t_normal_idx_alias"@"SEL$1" )) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t_normal_idx_alias"@"SEL$1" )) INDEX(@"SEL$1" "opt.t_normal_idx"@"SEL$1" "idx") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t_normal_idx:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx], pruned_index_name[t_normal_idx] Parameters ------------------------------------- *************** Case 54(end) ************** *************** Case 55 *************** SQL: select /*+no_use_late_materialization index(t_normal_idx idx)*/* from t_normal_idx where c2=1 order by c4 limit 1; ================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------- |0 |LIMIT | |1 |783 | |1 | TOP-N SORT | |1 |783 | |2 | TABLE SCAN|t_normal_idx(idx)|100 |676 | ================================================== Outputs & filters: ------------------------------------- 0 - output([t_normal_idx.c1], [t_normal_idx.c2], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c7], [t_normal_idx.c8], [t_normal_idx.c9], [t_normal_idx.c10]), filter(nil), limit(1), offset(nil) 1 - output([t_normal_idx.c1], [t_normal_idx.c2], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c7], [t_normal_idx.c8], [t_normal_idx.c9], [t_normal_idx.c10]), filter(nil), sort_keys([t_normal_idx.c4, ASC]), topn(1) 2 - output([t_normal_idx.c2], [t_normal_idx.c1], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c7], [t_normal_idx.c8], [t_normal_idx.c9], [t_normal_idx.c10]), filter(nil), access([t_normal_idx.c2], [t_normal_idx.c1], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c7], [t_normal_idx.c8], [t_normal_idx.c9], [t_normal_idx.c10]), partitions(p0), is_index_back=true, range_key([t_normal_idx.c2], [t_normal_idx.c3], [t_normal_idx.c4], [t_normal_idx.c5], [t_normal_idx.c6], [t_normal_idx.c1]), range(1,MIN,MIN,MIN,MIN,MIN ; 1,MAX,MAX,MAX,MAX,MAX), range_cond([t_normal_idx.c2 = ?]) Used Hint: ------------------------------------- /*+ NO_USE_LATE_MATERIALIZATION INDEX(@"SEL$1" "opt.t_normal_idx"@"SEL$1" "idx") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA NO_USE_LATE_MATERIALIZATION INDEX(@"SEL$1" "opt.t_normal_idx"@"SEL$1" "idx") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t_normal_idx:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:100, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx], pruned_index_name[t_normal_idx] Parameters ------------------------------------- {obj:{"BIGINT":1}, accuracy:{length:1, precision:1, scale:0}, flag:1, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"BIGINT", collation:"binary", coercibility:"NUMERIC"}} *************** Case 55(end) ************** *************** Case 56 *************** SQL: select * from t4; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 56(end) ************** *************** Case 57 *************** SQL: select /*+qb_name(select_1)*/* from t4; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ QB_NAME(select_1) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 57(end) ************** *************** Case 58 *************** SQL: select /*+qb_name(select_1) qb_name(select_2)*/* from t4; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ QB_NAME(select_2) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 58(end) ************** *************** Case 59 *************** SQL: select /*+qb_name(select_1) */* from t4 where t4.c1 > any(select /*+qb_name(select_2)*/c1 from t5); ====================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------ |0 |NESTED-LOOP SEMI JOIN | |34 |9608| |1 | TABLE SCAN |t4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR| |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ====================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), conds([t4.c1 > t5.c1]), nl_params_(nil), batch_join=false 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1]), filter(nil) 3 - output([t5.c1]), filter(nil) 4 - output([t5.c1]), filter(nil), dop=1 5 - output([t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c1]), filter(nil), access([t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ QB_NAME(select_2) QB_NAME(select_1) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 59(end) ************** *************** Case 60 *************** SQL: select /*+qb_name(select_1) */* from t4 where t4.c1 > any(select /*+qb_name(select_1)*/c1 from t5); ====================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------ |0 |NESTED-LOOP SEMI JOIN | |34 |9608| |1 | TABLE SCAN |t4 |100 |92 | |2 | MATERIAL | |300 |413 | |3 | PX COORDINATOR | |300 |247 | |4 | EXCHANGE OUT DISTR |:EX10000|300 |205 | |5 | PX PARTITION ITERATOR| |300 |205 | |6 | TABLE SCAN |t5 |300 |205 | ====================================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), conds([t4.c1 > t5.c1]), nl_params_(nil), batch_join=false 1 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true 2 - output([t5.c1]), filter(nil) 3 - output([t5.c1]), filter(nil) 4 - output([t5.c1]), filter(nil), dop=1 5 - output([t5.c1]), filter(nil), force partition granule, asc. 6 - output([t5.c1]), filter(nil), access([t5.c1]), partitions(p[0-2]), is_index_back=false, range_key([t5.c2], [t5.c3]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ QB_NAME(select_1) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("opt.t4"@"SEL$1" "opt.t5"@"SEL$1" )) USE_NL(@"SEL$1" ("opt.t5"@"SEL$1" )) USE_NL_MATERIALIZATION(@"SEL$1" ("opt.t5"@"SEL$1" )) FULL(@"SEL$1" "opt.t4"@"SEL$1") FULL(@"SEL$1" "opt.t5"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] t5:table_rows:300, physical_range_rows:300, logical_range_rows:300, index_back_rows:0, output_rows:300, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[t5], pruned_index_name[idx_t5_c2,idx_t5_c3] Parameters ------------------------------------- *************** Case 60(end) ************** *************** Case 61 *************** SQL: select /*+topk(1 100) hotspot max_concurrent(1)*/* from t4; =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t4 |100 |92 | =================================== Outputs & filters: ------------------------------------- 0 - output([t4.c1], [t4.c2], [t4.c3]), filter(nil), access([t4.c1], [t4.c2], [t4.c3]), partitions(p0), is_index_back=false, range_key([t4.c1], [t4.c2]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t4"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t4:table_rows:100, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t4_c2_c3,idx_t4_c3,t4], pruned_index_name[idx_t4_c2] Parameters ------------------------------------- *************** Case 61(end) ************** *************** Case 62 *************** SQL: select /*+read_consistency("weak")*/* from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, hint(read_consistency:2), range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ READ_CONSISTENCY("WEAK") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") READ_CONSISTENCY("WEAK") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 62(end) ************** *************** Case 63 *************** SQL: select /*+query_timeout(100)*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ QUERY_TIMEOUT(100) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") QUERY_TIMEOUT(100) END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 63(end) ************** *************** Case 64 *************** SQL: select /*+frozen_version(1)*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, hint(frozen_version:"1-0-0", read_consistency:1), range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ FROZEN_VERSION(1) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") FROZEN_VERSION(1) END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 64(end) ************** *************** Case 65 *************** SQL: select /*+use_plan_cache(none)*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ USE_PLAN_CACHE("NONE") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") USE_PLAN_CACHE("NONE") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 65(end) ************** *************** Case 66 *************** SQL: select /*+use_plan_cache(default)*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ USE_PLAN_CACHE("DEFAULT") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") USE_PLAN_CACHE("DEFAULT") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 66(end) ************** *************** Case 67 *************** SQL: select /*+use_plan_cache(nothing)*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 67(end) ************** *************** Case 68 *************** SQL: select /*+no_rewrite*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ NO_REWRITE */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") NO_REWRITE END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 68(end) ************** *************** Case 69 *************** SQL: select /*+trace_log*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ TRACE_LOG */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") TRACE_LOG END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 69(end) ************** *************** Case 70 *************** SQL: select /*+log_level('INFO')*/ * from t1; ==================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------- |0 |PX COORDINATOR | |500 |389 | |1 | EXCHANGE OUT DISTR |:EX10000|500 |342 | |2 | PX PARTITION ITERATOR| |500 |342 | |3 | TABLE SCAN |t1 |500 |342 | ==================================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2]), filter(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), dop=1 2 - output([t1.c1], [t1.c2]), filter(nil), force partition granule, asc. 3 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p[0-4]), is_index_back=false, range_key([t1.c1]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ LOG_LEVEL('info') */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "opt.t1"@"SEL$1") LOG_LEVEL('info') END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- t1:table_rows:500, physical_range_rows:500, logical_range_rows:500, index_back_rows:0, output_rows:500, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_t1_c2,t1] Parameters ------------------------------------- *************** Case 70(end) **************