You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2017/10/20 17:04:23 UTC
[3/5] incubator-trafodion git commit: Updating failing regress tests
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/ea3d3d61/core/sql/regress/seabase/EXPECTED010
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED010 b/core/sql/regress/seabase/EXPECTED010
index 4760b4c..d0662c3 100644
--- a/core/sql/regress/seabase/EXPECTED010
+++ b/core/sql/regress/seabase/EXPECTED010
@@ -41,7 +41,7 @@
--- SQL operation complete.
>>
->>obey TEST010(tests);
+>>obey TEST010(testsWithForces);
>>--------------------------------------------------------------------------
>>
>>create table if not exists t010t1 (a int not null, b char(10), primary key(a));
@@ -58,7 +58,7 @@
>>invoke t010t1;
-- Definition of Trafodion table TRAFODION.SCH.T010T1
--- Definition current Mon Sep 18 16:05:40 2017
+-- Definition current Thu Oct 19 22:12:21 2017
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -71,7 +71,7 @@
>>invoke hbase."_CELL_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_CELL_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:05:40 2017
+-- Definition current Thu Oct 19 22:12:22 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -90,7 +90,7 @@
>>invoke hbase."_ROW_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_ROW_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:05:41 2017
+-- Definition current Thu Oct 19 22:12:23 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -221,7 +221,7 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510743136532
+PLAN_ID .................. 212375211145371059
ROWS_OUT ................ 11
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 or b='1';
@@ -263,7 +263,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208217
+ ObjectUIDs ............. 5140439103333366849
select_list ............ TRAFODION.SCH.T010T1.A, TRAFODION.SCH.T010T1.B
@@ -304,7 +304,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510743216468
+PLAN_ID .................. 212375211145466061
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 and b='1';
@@ -347,7 +347,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208217
+ ObjectUIDs ............. 5140439103333366849
select_list ............ %(1), %('1')
input_variables ........ %(1), %('1')
@@ -387,7 +387,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510743326023
+PLAN_ID .................. 212375211145557099
ROWS_OUT ................ 10
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where b='1';
@@ -429,7 +429,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208217
+ ObjectUIDs ............. 5140439103333366849
select_list ............ TRAFODION.SCH.T010T1.A, %('1')
input_variables ........ %('1')
@@ -470,7 +470,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510742386836
+PLAN_ID .................. 212375211144533319
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a = 2;
@@ -513,7 +513,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208217
+ ObjectUIDs ............. 5140439103333366849
select_list ............ %(2), TRAFODION.SCH.T010T1.B
input_variables ........ %(2)
@@ -561,6 +561,10 @@ DESCRIPTION
--- 10 row(s) inserted.
>>
+>>control query shape scan(table 'T010T2', mdam forced);
+
+--- SQL operation complete.
+>>
>>prepare x1 from
+>select * from t010t2 where a in (1,4) and b='a' and c = 1;
@@ -612,6 +616,16 @@ A B C D
--- 4 row(s) selected.
>>-- expect 1a1, 1a3, 2a1, 4a1
>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
+>>control query shape nested_join(scan(path 'TRAFODION.SCH.T010T2', forward
++>, blocks_per_access 1 , mdam forced, mdam_columns all(sparse, sparse
++>, sparse)),anything);
+
+--- SQL operation complete.
+>>
>>prepare x4 from
+>delete from t010t2 where a in (2,4,6) and b='a' and c in (1,3);
@@ -620,6 +634,11 @@ A B C D
--- 2 row(s) deleted.
>>-- expect 2 rows deleted, 2a1 and 4a1
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
>>execute x3;
A B C D
@@ -667,6 +686,10 @@ A B C D
--- SQL operation complete.
>>
+>>control query shape scan(table 'T010T2', mdam forced);
+
+--- SQL operation complete.
+>>
>>prepare y1 from
+>select * from t010t2 where a in (1,4) and b='a' and c = 1;
@@ -717,6 +740,16 @@ A B C D
--- 4 row(s) selected.
>>-- expect 1a1, 1a3, 2a1, 4a1
>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
+>>control query shape nested_join(scan(path 'TRAFODION.SCH.T010T2', forward
++>, blocks_per_access 1 , mdam forced, mdam_columns all(sparse, sparse
++>, sparse)),anything);
+
+--- SQL operation complete.
+>>
>>prepare y4 from
+>delete from t010t2 where a in (2,4,6) and b='a' and c in (1,3);
@@ -725,6 +758,11 @@ A B C D
--- 2 row(s) deleted.
>>-- expect 2 rows deleted, 2a1 and 4a1
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
>>execute y3;
A B C D
@@ -777,12 +815,13 @@ A B C D
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X1
-PLAN_ID .................. 212372510744251128
+PLAN_ID .................. 212375211146540988
ROWS_OUT ................. 2
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
STATEMENT ................ select *
from t010t2
where a in (1,4) and b='a' and c = 1;
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -790,7 +829,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -821,7 +860,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
select_list ............ TRAFODION.SCH.T010T2.A, %('a'), %(1),
TRAFODION.SCH.T010T2.D
input_variables ........ %('a'), %(1)
@@ -831,8 +870,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.08
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -844,8 +883,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 3
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... ((A = 1) or (A = 4)) and (B = %('a')) and (C = %(1))
@@ -856,13 +894,14 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X2
-PLAN_ID .................. 212372510744321090
+PLAN_ID .................. 212375211146661132
ROWS_OUT ................. 5
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and (c in (1,
3) or c>=5);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -870,7 +909,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -901,7 +940,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -911,8 +950,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -924,8 +963,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and (B = %('a'))
@@ -937,12 +975,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X3
-PLAN_ID .................. 212372510744403400
+PLAN_ID .................. 212375211146790052
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and c in (1,3);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -950,7 +989,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -981,7 +1020,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -991,8 +1030,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -1004,8 +1043,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and ((C = 1) or (C
@@ -1017,11 +1055,12 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X4
-PLAN_ID .................. 212372510744506903
+PLAN_ID .................. 212375211146953887
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ delete from t010t2
where a in (2,4,6) and b='a' and c in (1,3);
+MUST_MATCH ............... forced nested join(forced scan, Cut (0))
------------------------------------------------------------------ NODE LISTING
@@ -1029,7 +1068,7 @@ ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est ........... 4
@@ -1061,7 +1100,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
input_variables ........ %('a')
@@ -1069,7 +1108,7 @@ TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0.01
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est ........... 4
fragment_id ............ 0
@@ -1099,8 +1138,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -1112,11 +1151,9 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ A, B, C
- executor_predicates .... (B = %('a'))
mdam_disjunct .......... (((A = 2) or (A = 4)) or (A = 6)) and ((C = 1) or (C
= 3)) and (B = %('a'))
@@ -1126,7 +1163,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X5
-PLAN_ID .................. 212372510744679860
+PLAN_ID .................. 212375211147208609
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -1172,7 +1209,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
input_variables ........ %('upd'), %(4)
@@ -1199,12 +1236,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y1
-PLAN_ID .................. 212372510744762969
+PLAN_ID .................. 212375211147325266
ROWS_OUT ................. 2
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
STATEMENT ................ select *
from t010t2
where a in (1,4) and b='a' and c = 1;
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -1212,7 +1250,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -1244,7 +1282,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
select_list ............ TRAFODION.SCH.T010T2.A, %('a'), %(1),
TRAFODION.SCH.T010T2.D
input_variables ........ %('a'), %(1)
@@ -1254,8 +1292,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.08
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -1267,8 +1305,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 3
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... ((A = 1) or (A = 4)) and (B = %('a')) and (C = %(1))
@@ -1279,13 +1316,14 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y2
-PLAN_ID .................. 212372510744826272
+PLAN_ID .................. 212375211147405399
ROWS_OUT ................. 5
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and (c in (1,
3) or c>=5);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -1293,7 +1331,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -1325,7 +1363,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -1335,8 +1373,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -1348,8 +1386,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and (B = %('a'))
@@ -1361,12 +1398,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y3
-PLAN_ID .................. 212372510744914026
+PLAN_ID .................. 212375211147531882
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and c in (1,3);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -1374,7 +1412,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -1406,7 +1444,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -1416,8 +1454,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -1429,8 +1467,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and ((C = 1) or (C
@@ -1442,11 +1479,12 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y4
-PLAN_ID .................. 212372510744992870
+PLAN_ID .................. 212375211147688049
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ delete from t010t2
where a in (2,4,6) and b='a' and c in (1,3);
+MUST_MATCH ............... forced nested join(forced scan, Cut (0))
------------------------------------------------------------------ NODE LISTING
@@ -1454,7 +1492,7 @@ ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est ........... 4
@@ -1487,7 +1525,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
input_variables ........ %('a')
@@ -1495,7 +1533,7 @@ TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0.01
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est ........... 4
fragment_id ............ 0
@@ -1525,8 +1563,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -1538,11 +1576,9 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ A, B, C
- executor_predicates .... (B = %('a'))
mdam_disjunct .......... (((A = 2) or (A = 4)) or (A = 6)) and ((C = 1) or (C
= 3)) and (B = %('a'))
@@ -1552,7 +1588,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y5
-PLAN_ID .................. 212372510745170839
+PLAN_ID .................. 212375211147942334
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -1599,7 +1635,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705208633
+ ObjectUIDs ............. 5140439103333367240
input_variables ........ %('uuu'), %(4)
@@ -1776,7 +1812,7 @@ _SALT_ A B C D
>>invoke t010t1;
-- Definition of Trafodion table TRAFODION.SCH.T010T1
--- Definition current Mon Sep 18 16:06:13 2017
+-- Definition current Thu Oct 19 22:12:54 2017
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -1789,7 +1825,7 @@ _SALT_ A B C D
>>invoke hbase."_CELL_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_CELL_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:06:13 2017
+-- Definition current Thu Oct 19 22:12:54 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -1808,7 +1844,7 @@ _SALT_ A B C D
>>invoke hbase."_ROW_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_ROW_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:06:13 2017
+-- Definition current Thu Oct 19 22:12:54 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -1939,7 +1975,7 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510775355462
+PLAN_ID .................. 212375211176420764
ROWS_OUT ................ 11
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 or b='1';
@@ -1981,7 +2017,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705211716
+ ObjectUIDs ............. 5140439103333370094
select_list ............ TRAFODION.SCH.T010T1.A, TRAFODION.SCH.T010T1.B
@@ -2022,7 +2058,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510775435387
+PLAN_ID .................. 212375211176519441
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 and b='1';
@@ -2065,7 +2101,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705211716
+ ObjectUIDs ............. 5140439103333370094
select_list ............ 1, '1'
@@ -2104,7 +2140,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510775513753
+PLAN_ID .................. 212375211176605130
ROWS_OUT ................ 10
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where b='1';
@@ -2146,7 +2182,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705211716
+ ObjectUIDs ............. 5140439103333370094
select_list ............ TRAFODION.SCH.T010T1.A, '1'
@@ -2186,7 +2222,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510775595020
+PLAN_ID .................. 212375211176696337
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1;
@@ -2229,7 +2265,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705211716
+ ObjectUIDs ............. 5140439103333370094
select_list ............ 1, TRAFODION.SCH.T010T1.B
@@ -2492,7 +2528,7 @@ A B C D
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X1
-PLAN_ID .................. 212372510777807806
+PLAN_ID .................. 212375211179079242
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -2536,7 +2572,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
select_list ............ TRAFODION.SCH.T010T2.A, 'a', 1,
TRAFODION.SCH.T010T2.D
@@ -2571,7 +2607,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X2
-PLAN_ID .................. 212372510777847080
+PLAN_ID .................. 212375211179132421
ROWS_OUT ................. 5
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -2616,7 +2652,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -2664,7 +2700,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X3
-PLAN_ID .................. 212372510777884929
+PLAN_ID .................. 212375211179174633
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -2708,7 +2744,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -2747,7 +2783,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X4
-PLAN_ID .................. 212372510777938513
+PLAN_ID .................. 212375211179219489
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ delete from t010t2
@@ -2791,7 +2827,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
@@ -2859,7 +2895,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X5
-PLAN_ID .................. 212372510778036529
+PLAN_ID .................. 212375211179343865
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -2905,7 +2941,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
TRAFODION_UPDATE ========================== SEQ_NO 1 NO CHILDREN
@@ -2935,7 +2971,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y1
-PLAN_ID .................. 212372510778110007
+PLAN_ID .................. 212375211179430092
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -2980,7 +3016,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
select_list ............ TRAFODION.SCH.T010T2.A, 'a', 1,
TRAFODION.SCH.T010T2.D
@@ -3016,7 +3052,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y2
-PLAN_ID .................. 212372510778154911
+PLAN_ID .................. 212375211179477243
ROWS_OUT ................. 5
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -3062,7 +3098,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -3099,7 +3135,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y3
-PLAN_ID .................. 212372510778204183
+PLAN_ID .................. 212375211179524021
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -3144,7 +3180,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -3181,7 +3217,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y4
-PLAN_ID .................. 212372510778249743
+PLAN_ID .................. 212375211179568701
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ delete from t010t2
@@ -3226,7 +3262,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
@@ -3291,7 +3327,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y5
-PLAN_ID .................. 212372510778336974
+PLAN_ID .................. 212375211179667464
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -3338,7 +3374,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705212102
+ ObjectUIDs ............. 5140439103333370450
TRAFODION_UPDATE ========================== SEQ_NO 1 NO CHILDREN
@@ -3515,7 +3551,7 @@ _SALT_ A B C D
>>invoke t010t1;
-- Definition of Trafodion table TRAFODION.SCH.T010T1
--- Definition current Mon Sep 18 16:06:48 2017
+-- Definition current Thu Oct 19 22:13:32 2017
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -3528,7 +3564,7 @@ _SALT_ A B C D
>>invoke hbase."_CELL_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_CELL_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:06:48 2017
+-- Definition current Thu Oct 19 22:13:32 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -3547,7 +3583,7 @@ _SALT_ A B C D
>>invoke hbase."_ROW_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_ROW_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:06:49 2017
+-- Definition current Thu Oct 19 22:13:32 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -3678,7 +3714,7 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510810689778
+PLAN_ID .................. 212375211214603886
ROWS_OUT ................ 11
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 or b='1';
@@ -3720,7 +3756,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215251
+ ObjectUIDs ............. 5140439103333373856
select_list ............ TRAFODION.SCH.T010T1.A, TRAFODION.SCH.T010T1.B
@@ -3761,7 +3797,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510810785182
+PLAN_ID .................. 212375211214727858
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 and b='1';
@@ -3804,7 +3840,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215251
+ ObjectUIDs ............. 5140439103333373856
select_list ............ %(1), %('1')
input_variables ........ %(1), %('1')
@@ -3844,7 +3880,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510810887577
+PLAN_ID .................. 212375211214827240
ROWS_OUT ................ 10
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where b='1';
@@ -3886,7 +3922,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215251
+ ObjectUIDs ............. 5140439103333373856
select_list ............ TRAFODION.SCH.T010T1.A, %('1')
input_variables ........ %('1')
@@ -3927,7 +3963,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510809929666
+PLAN_ID .................. 212375211213767413
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a = 2;
@@ -3970,7 +4006,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215251
+ ObjectUIDs ............. 5140439103333373856
select_list ............ %(2), TRAFODION.SCH.T010T1.B
input_variables ........ %(2)
@@ -4234,7 +4270,7 @@ A B C D
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X1
-PLAN_ID .................. 212372510813424935
+PLAN_ID .................. 212375211217409963
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -4278,7 +4314,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
select_list ............ TRAFODION.SCH.T010T2.A, %('a'), %(1),
TRAFODION.SCH.T010T2.D
input_variables ........ %('a'), %(1)
@@ -4314,7 +4350,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X2
-PLAN_ID .................. 212372510813467701
+PLAN_ID .................. 212375211217459964
ROWS_OUT ................. 5
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -4359,7 +4395,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -4396,7 +4432,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X3
-PLAN_ID .................. 212372510813513582
+PLAN_ID .................. 212375211217505716
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -4440,7 +4476,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -4477,7 +4513,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X4
-PLAN_ID .................. 212372510813555013
+PLAN_ID .................. 212375211217557562
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ delete from t010t2
@@ -4521,7 +4557,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
input_variables ........ %('a')
@@ -4586,7 +4622,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X5
-PLAN_ID .................. 212372510813664352
+PLAN_ID .................. 212375211217660993
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -4632,7 +4668,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
input_variables ........ %('upd'), %(4)
@@ -4659,7 +4695,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y1
-PLAN_ID .................. 212372510813764883
+PLAN_ID .................. 212375211217753803
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -4704,7 +4740,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
select_list ............ TRAFODION.SCH.T010T2.A, %('a'), %(1),
TRAFODION.SCH.T010T2.D
input_variables ........ %('a'), %(1)
@@ -4740,7 +4776,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y2
-PLAN_ID .................. 212372510813822752
+PLAN_ID .................. 212375211217802644
ROWS_OUT ................. 5
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -4786,7 +4822,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -4823,7 +4859,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y3
-PLAN_ID .................. 212372510813873480
+PLAN_ID .................. 212375211217854498
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select *
@@ -4868,7 +4904,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -4905,7 +4941,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y4
-PLAN_ID .................. 212372510813926991
+PLAN_ID .................. 212375211217910650
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ delete from t010t2
@@ -4950,7 +4986,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
input_variables ........ %('a')
@@ -5015,7 +5051,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y5
-PLAN_ID .................. 212372510814057803
+PLAN_ID .................. 212375211218041292
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -5062,7 +5098,7 @@ DESCRIPTION
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... OFF
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705215610
+ ObjectUIDs ............. 5140439103333374241
input_variables ........ %('uuu'), %(4)
@@ -5222,7 +5258,7 @@ _SALT_ A B C D
--- SQL operation complete.
>>
->>obey TEST010(tests);
+>>obey TEST010(testsWithForces);
>>--------------------------------------------------------------------------
>>
>>create table if not exists t010t1 (a int not null, b char(10), primary key(a));
@@ -5239,7 +5275,7 @@ _SALT_ A B C D
>>invoke t010t1;
-- Definition of Trafodion table TRAFODION.SCH.T010T1
--- Definition current Mon Sep 18 16:07:19 2017
+-- Definition current Thu Oct 19 22:14:04 2017
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -5252,7 +5288,7 @@ _SALT_ A B C D
>>invoke hbase."_CELL_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_CELL_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:07:19 2017
+-- Definition current Thu Oct 19 22:14:04 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -5271,7 +5307,7 @@ _SALT_ A B C D
>>invoke hbase."_ROW_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_ROW_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:07:20 2017
+-- Definition current Thu Oct 19 22:14:04 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -5402,7 +5438,7 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510841638220
+PLAN_ID .................. 212375211246332375
ROWS_OUT ................ 11
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 or b='1';
@@ -5444,7 +5480,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218307
+ ObjectUIDs ............. 5140439103333377111
select_list ............ TRAFODION.SCH.T010T1.A, TRAFODION.SCH.T010T1.B
@@ -5485,7 +5521,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510841720052
+PLAN_ID .................. 212375211246427100
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 and b='1';
@@ -5528,7 +5564,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218307
+ ObjectUIDs ............. 5140439103333377111
select_list ............ 1, '1'
@@ -5567,7 +5603,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510841806680
+PLAN_ID .................. 212375211246514744
ROWS_OUT ................ 10
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where b='1';
@@ -5609,7 +5645,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218307
+ ObjectUIDs ............. 5140439103333377111
select_list ............ TRAFODION.SCH.T010T1.A, '1'
@@ -5649,7 +5685,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510841894956
+PLAN_ID .................. 212375211246603784
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1;
@@ -5692,7 +5728,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218307
+ ObjectUIDs ............. 5140439103333377111
select_list ............ 1, TRAFODION.SCH.T010T1.B
@@ -5739,6 +5775,10 @@ DESCRIPTION
--- 10 row(s) inserted.
>>
+>>control query shape scan(table 'T010T2', mdam forced);
+
+--- SQL operation complete.
+>>
>>prepare x1 from
+>select * from t010t2 where a in (1,4) and b='a' and c = 1;
@@ -5790,6 +5830,16 @@ A B C D
--- 4 row(s) selected.
>>-- expect 1a1, 1a3, 2a1, 4a1
>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
+>>control query shape nested_join(scan(path 'TRAFODION.SCH.T010T2', forward
++>, blocks_per_access 1 , mdam forced, mdam_columns all(sparse, sparse
++>, sparse)),anything);
+
+--- SQL operation complete.
+>>
>>prepare x4 from
+>delete from t010t2 where a in (2,4,6) and b='a' and c in (1,3);
@@ -5798,6 +5848,11 @@ A B C D
--- 2 row(s) deleted.
>>-- expect 2 rows deleted, 2a1 and 4a1
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
>>execute x3;
A B C D
@@ -5845,6 +5900,10 @@ A B C D
--- SQL operation complete.
>>
+>>control query shape scan(table 'T010T2', mdam forced);
+
+--- SQL operation complete.
+>>
>>prepare y1 from
+>select * from t010t2 where a in (1,4) and b='a' and c = 1;
@@ -5895,6 +5954,16 @@ A B C D
--- 4 row(s) selected.
>>-- expect 1a1, 1a3, 2a1, 4a1
>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
+>>control query shape nested_join(scan(path 'TRAFODION.SCH.T010T2', forward
++>, blocks_per_access 1 , mdam forced, mdam_columns all(sparse, sparse
++>, sparse)),anything);
+
+--- SQL operation complete.
+>>
>>prepare y4 from
+>delete from t010t2 where a in (2,4,6) and b='a' and c in (1,3);
@@ -5903,6 +5972,11 @@ A B C D
--- 2 row(s) deleted.
>>-- expect 2 rows deleted, 2a1 and 4a1
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
>>execute y3;
A B C D
@@ -5955,12 +6029,13 @@ A B C D
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X1
-PLAN_ID .................. 212372510844145441
+PLAN_ID .................. 212375211248914713
ROWS_OUT ................. 2
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
STATEMENT ................ select *
from t010t2
where a in (1,4) and b='a' and c = 1;
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -5968,7 +6043,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -5999,7 +6074,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
select_list ............ TRAFODION.SCH.T010T2.A, 'a', 1,
TRAFODION.SCH.T010T2.D
@@ -6008,8 +6083,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.08
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6021,8 +6096,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 3
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... ((A = 1) or (A = 4)) and (B = 'a') and (C = 1)
@@ -6033,13 +6107,14 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X2
-PLAN_ID .................. 212372510844225191
+PLAN_ID .................. 212375211248990476
ROWS_OUT ................. 5
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and (c in (1,
3) or c>=5);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -6047,7 +6122,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -6078,7 +6153,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -6087,8 +6162,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6100,8 +6175,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and (B = 'a') and
@@ -6113,12 +6187,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X3
-PLAN_ID .................. 212372510844303373
+PLAN_ID .................. 212375211249093605
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and c in (1,3);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -6126,7 +6201,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -6157,7 +6232,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -6166,8 +6241,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6179,8 +6254,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and ((C = 1) or (C
@@ -6192,11 +6266,12 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X4
-PLAN_ID .................. 212372510844389798
+PLAN_ID .................. 212375211249243720
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ delete from t010t2
where a in (2,4,6) and b='a' and c in (1,3);
+MUST_MATCH ............... forced nested join(forced scan, Cut (0))
------------------------------------------------------------------ NODE LISTING
@@ -6204,7 +6279,7 @@ ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est ........... 4
@@ -6236,14 +6311,14 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0.01
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est ........... 4
fragment_id ............ 0
@@ -6273,8 +6348,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6286,11 +6361,9 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ A, B, C
- executor_predicates .... (B = 'a')
mdam_disjunct .......... (((A = 2) or (A = 4)) or (A = 6)) and ((C = 1) or (C
= 3)) and (B = 'a')
@@ -6300,7 +6373,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X5
-PLAN_ID .................. 212372510844550022
+PLAN_ID .................. 212375211249478019
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -6346,7 +6419,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT OFF
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
TRAFODION_UPDATE ========================== SEQ_NO 1 NO CHILDREN
@@ -6376,12 +6449,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y1
-PLAN_ID .................. 212372510844652886
+PLAN_ID .................. 212375211249603770
ROWS_OUT ................. 2
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
STATEMENT ................ select *
from t010t2
where a in (1,4) and b='a' and c = 1;
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -6389,7 +6463,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -6421,7 +6495,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
select_list ............ TRAFODION.SCH.T010T2.A, 'a', 1,
TRAFODION.SCH.T010T2.D
@@ -6430,8 +6504,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.08
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6443,8 +6517,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 3
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... ((A = 1) or (A = 4)) and (B = 'a') and (C = 1)
@@ -6455,13 +6528,14 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y2
-PLAN_ID .................. 212372510844727465
+PLAN_ID .................. 212375211249685191
ROWS_OUT ................. 5
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and (c in (1,
3) or c>=5);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -6469,7 +6543,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -6501,7 +6575,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -6510,8 +6584,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6523,8 +6597,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and (B = 'a') and
@@ -6536,12 +6609,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y3
-PLAN_ID .................. 212372510844821878
+PLAN_ID .................. 212375211249786072
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and c in (1,3);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -6549,7 +6623,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -6581,7 +6655,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
select_list ............ TRAFODION.SCH.T010T2.A, 'a', TRAFODION.SCH.T010T2.C,
TRAFODION.SCH.T010T2.D
@@ -6590,8 +6664,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6603,8 +6677,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and ((C = 1) or (C
@@ -6616,11 +6689,12 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y4
-PLAN_ID .................. 212372510844904137
+PLAN_ID .................. 212375211249940535
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ delete from t010t2
where a in (2,4,6) and b='a' and c in (1,3);
+MUST_MATCH ............... forced nested join(forced scan, Cut (0))
------------------------------------------------------------------ NODE LISTING
@@ -6628,7 +6702,7 @@ ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est ........... 4
@@ -6661,14 +6735,14 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0.01
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est ........... 4
fragment_id ............ 0
@@ -6698,8 +6772,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -6711,11 +6785,9 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1,#1:2,#1:3
key_columns ............ A, B, C
- executor_predicates .... (B = 'a')
mdam_disjunct .......... (((A = 2) or (A = 4)) or (A = 6)) and ((C = 1) or (C
= 3)) and (B = 'a')
@@ -6725,7 +6797,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... Y5
-PLAN_ID .................. 212372510845062874
+PLAN_ID .................. 212375211250156714
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0
STATEMENT ................ update t010t2
@@ -6772,7 +6844,7 @@ DESCRIPTION
QUERY_CACHE ............ 0
MDAM_SCAN_METHOD ....... ON
HBASE_MAX_NUM_SEARCH_KE 1
- ObjectUIDs ............. 5892537540705218678
+ ObjectUIDs ............. 5140439103333377468
TRAFODION_UPDATE ========================== SEQ_NO 1 NO CHILDREN
@@ -6955,7 +7027,7 @@ _SALT_ A B C D
--- SQL operation complete.
>>
->>obey TEST010(tests);
+>>obey TEST010(testsWithForces);
>>--------------------------------------------------------------------------
>>
>>create table if not exists t010t1 (a int not null, b char(10), primary key(a));
@@ -6972,7 +7044,7 @@ _SALT_ A B C D
>>invoke t010t1;
-- Definition of Trafodion table TRAFODION.SCH.T010T1
--- Definition current Mon Sep 18 16:07:54 2017
+-- Definition current Thu Oct 19 22:14:41 2017
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
@@ -6985,7 +7057,7 @@ _SALT_ A B C D
>>invoke hbase."_CELL_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_CELL_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:07:54 2017
+-- Definition current Thu Oct 19 22:14:41 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -7004,7 +7076,7 @@ _SALT_ A B C D
>>invoke hbase."_ROW_"."TRAFODION.SCH.T010T1";
-- Definition of Trafodion table HBASE."_ROW_"."TRAFODION.SCH.T010T1"
--- Definition current Mon Sep 18 16:07:54 2017
+-- Definition current Thu Oct 19 22:14:42 2017
(
ROW_ID VARCHAR(10) CHARACTER SET ISO88591 COLLATE
@@ -7135,7 +7207,7 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510876531459
+PLAN_ID .................. 212375211283939586
ROWS_OUT ................ 11
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 or b='1';
@@ -7177,7 +7249,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705221801
+ ObjectUIDs ............. 5140439103333380547
select_list ............ TRAFODION.SCH.T010T1.A, TRAFODION.SCH.T010T1.B
@@ -7218,7 +7290,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510876627518
+PLAN_ID .................. 212375211284071529
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a=1 and b='1';
@@ -7261,7 +7333,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705221801
+ ObjectUIDs ............. 5140439103333380547
select_list ............ %(1), %('1')
input_variables ........ %(1), %('1')
@@ -7301,7 +7373,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510876720520
+PLAN_ID .................. 212375211284182877
ROWS_OUT ................ 10
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where b='1';
@@ -7343,7 +7415,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705221801
+ ObjectUIDs ............. 5140439103333380547
select_list ............ TRAFODION.SCH.T010T1.A, %('1')
input_variables ........ %('1')
@@ -7384,7 +7456,7 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
-PLAN_ID .................. 212372510875730083
+PLAN_ID .................. 212375211283074993
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select * from t010t1 where a = 2;
@@ -7427,7 +7499,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705221801
+ ObjectUIDs ............. 5140439103333380547
select_list ............ %(2), TRAFODION.SCH.T010T1.B
input_variables ........ %(2)
@@ -7475,6 +7547,10 @@ DESCRIPTION
--- 10 row(s) inserted.
>>
+>>control query shape scan(table 'T010T2', mdam forced);
+
+--- SQL operation complete.
+>>
>>prepare x1 from
+>select * from t010t2 where a in (1,4) and b='a' and c = 1;
@@ -7526,6 +7602,16 @@ A B C D
--- 4 row(s) selected.
>>-- expect 1a1, 1a3, 2a1, 4a1
>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
+>>control query shape nested_join(scan(path 'TRAFODION.SCH.T010T2', forward
++>, blocks_per_access 1 , mdam forced, mdam_columns all(sparse, sparse
++>, sparse)),anything);
+
+--- SQL operation complete.
+>>
>>prepare x4 from
+>delete from t010t2 where a in (2,4,6) and b='a' and c in (1,3);
@@ -7534,6 +7620,11 @@ A B C D
--- 2 row(s) deleted.
>>-- expect 2 rows deleted, 2a1 and 4a1
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
>>execute x3;
A B C D
@@ -7581,6 +7672,10 @@ A B C D
--- SQL operation complete.
>>
+>>control query shape scan(table 'T010T2', mdam forced);
+
+--- SQL operation complete.
+>>
>>prepare y1 from
+>select * from t010t2 where a in (1,4) and b='a' and c = 1;
@@ -7631,6 +7726,16 @@ A B C D
--- 4 row(s) selected.
>>-- expect 1a1, 1a3, 2a1, 4a1
>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
+>>control query shape nested_join(scan(path 'TRAFODION.SCH.T010T2', forward
++>, blocks_per_access 1 , mdam forced, mdam_columns all(sparse, sparse
++>, sparse)),anything);
+
+--- SQL operation complete.
+>>
>>prepare y4 from
+>delete from t010t2 where a in (2,4,6) and b='a' and c in (1,3);
@@ -7639,6 +7744,11 @@ A B C D
--- 2 row(s) deleted.
>>-- expect 2 rows deleted, 2a1 and 4a1
+>>
+>>control query shape off;
+
+--- SQL operation complete.
+>>
>>execute y3;
A B C D
@@ -7691,12 +7801,13 @@ A B C D
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X1
-PLAN_ID .................. 212372510879119333
+PLAN_ID .................. 212375211286758015
ROWS_OUT ................. 2
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
STATEMENT ................ select *
from t010t2
where a in (1,4) and b='a' and c = 1;
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -7704,7 +7815,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -7735,7 +7846,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705222172
+ ObjectUIDs ............. 5140439103333380976
select_list ............ TRAFODION.SCH.T010T2.A, %('a'), %(1),
TRAFODION.SCH.T010T2.D
input_variables ........ %('a'), %(1)
@@ -7745,8 +7856,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.08
+EST_TOTAL_COST ........... 0.08
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -7758,8 +7869,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 3
column_retrieved ....... #1:1
key_columns ............ A, B, C
mdam_disjunct .......... ((A = 1) or (A = 4)) and (B = %('a')) and (C = %(1))
@@ -7770,13 +7880,14 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X2
-PLAN_ID .................. 212372510879194787
+PLAN_ID .................. 212375211286838992
ROWS_OUT ................. 5
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and (c in (1,
3) or c>=5);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -7784,7 +7895,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -7815,7 +7926,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705222172
+ ObjectUIDs ............. 5140439103333380976
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -7825,8 +7936,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 5
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -7838,8 +7949,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and (B = %('a'))
@@ -7851,12 +7961,13 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X3
-PLAN_ID .................. 212372510879281025
+PLAN_ID .................. 212375211286932222
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ select *
from t010t2
where a in (1,2,4) and b='a' and c in (1,3);
+MUST_MATCH ............... forced scan(T010T2, index T010T2)
------------------------------------------------------------------ NODE LISTING
@@ -7864,7 +7975,7 @@ ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est .......... 99
@@ -7895,7 +8006,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705222172
+ ObjectUIDs ............. 5140439103333380976
select_list ............ TRAFODION.SCH.T010T2.A, %('a'),
TRAFODION.SCH.T010T2.C, TRAFODION.SCH.T010T2.D
input_variables ........ %('a')
@@ -7905,8 +8016,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -7918,8 +8029,7 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ........... ON
small_scanner .......... ON
- probes ................. 1
- rows_accessed ........ 100
+ rows_accessed .......... 6
column_retrieved ....... #1:1
key_columns ............ A, B, C
mdam_disjunct .......... (((A = 1) or (A = 2)) or (A = 4)) and ((C = 1) or (C
@@ -7931,11 +8041,12 @@ DESCRIPTION
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X4
-PLAN_ID .................. 212372510879367346
+PLAN_ID .................. 212375211287075899
ROWS_OUT ................. 4
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
STATEMENT ................ delete from t010t2
where a in (2,4,6) and b='a' and c in (1,3);
+MUST_MATCH ............... forced nested join(forced scan, Cut (0))
------------------------------------------------------------------ NODE LISTING
@@ -7943,7 +8054,7 @@ ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB
max_card_est ........... 4
@@ -7975,7 +8086,7 @@ DESCRIPTION
TRAF_ALIGNED_ROW_FORMAT ON
QUERY_CACHE ........ 1,024
MDAM_SCAN_METHOD ....... ON
- ObjectUIDs ............. 5892537540705222172
+ ObjectUIDs ............. 5140439103333380976
input_variables ........ %('a')
@@ -7983,7 +8094,7 @@ TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0.01
-EST_TOTAL_COST ........... 0.01
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est ........... 4
fragment_id ............ 0
@@ -8013,8 +8124,8 @@ TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... T010T2
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
-EST_OPER_COST ............ 0
-EST_TOTAL_COST ........... 0
+EST_OPER_COST ............ 0.11
+EST_TOTAL_COST ........... 0.11
DESCRIPTION
max_card_est .......... 99
fragment_id ............ 0
@@ -8026,11 +8137,9 @@ DESCRIPTION
cache_size ........... 100
cache_blocks ......
<TRUNCATED>