| >> |
| >>obey TEST016(mcStatsPart_CQDs); |
| >>create schema trafodion.hbase; |
| |
| --- SQL operation complete. |
| >>set schema Trafodion.hbase; |
| |
| --- SQL operation complete. |
| >> |
| >>cqd query_cache '0'; |
| |
| --- SQL operation complete. |
| >>cqd cache_histograms 'off'; |
| |
| --- SQL operation complete. |
| >>cqd HBASE_MIN_BYTES_PER_ESP_PARTITION '1'; |
| |
| --- SQL operation complete. |
| >>cqd USTAT_COLLECT_MC_SKEW_VALUES 'on'; |
| |
| --- SQL operation complete. |
| >>cqd PARALLEL_NUM_ESPS '4'; |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST016(mcStatsPart_Setup); |
| >>-- int, smallint, largeint |
| >>create table mcStatPart7 |
| +>(a smallint not null not droppable, |
| +>b largeint not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart7 values (1,100000,1,'xyz'),(1,200000,1,'xyz'),(1,600000,1,'xyz'),(2,300000,1,'xyz'),(2,400000,1,'xyz'),(2,500000,1,'xyz'),(2,600000,1,'xyz'), |
| +> (3,100000,1,'xyz'),(3,200000,1,'xyz'),(3,600000,1,'xyz'),(4,300000,1,'xyz'),(4,400000,1,'xyz'),(4,500000,1,'xyz'),(4,600000,1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart7 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>-- decimal |
| >>create table mcStatPart5 |
| +>(a int not null not droppable, |
| +>b decimal(10, 2) not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart5 values (1,1.25,1,'xyz'),(1,2.25,1,'xyz'),(1,6.25,1,'xyz'),(2,3.25,1,'xyz'),(2,4.25,1,'xyz'),(2,5.25,1,'xyz'),(2,6.23,1,'xyz'), |
| +> (3,1.25,1,'xyz'),(3,2.22,1,'xyz'),(3,2.25,1,'xyz'),(4,3.65,1,'xyz'),(4,3.55,1,'xyz'),(4,3.75,1,'xyz'),(4,3.50,1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart5 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>-- timestamp type |
| >>create table mcStatPart3 |
| +>(a int not null not droppable, |
| +>b timestamp not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart3 values (1, timestamp'1990-01-01 00:00:00',1,'xyz'),(1, timestamp'1990-02-01 00:00:00',1,'xyz'),(1, timestamp'1990-03-01 00:00:00',1,'xyz'),(2, timestamp'1990-04-01 00:00:00',1,'xyz'), |
| +> (2, timestamp'1990-03-02 00:00:00',1,'xyz'),(2, timestamp'1990-04-02 00:00:00',1,'xyz'),(2, timestamp'1990-05-03 00:00:00',1,'xyz'), |
| +> (3, timestamp'1990-01-01 00:00:00',1,'xyz'),(3, timestamp'1990-02-01 00:00:00',1,'xyz'),(3, timestamp'1990-03-01 00:00:00',1,'xyz'),(3, timestamp'1990-04-01 00:00:00',1,'xyz'), |
| +> (4, timestamp'1990-03-01 00:00:00',1,'xyz'),(4, timestamp'1990-04-01 00:00:00',1,'xyz'),(4, timestamp'1990-05-01 00:00:00',1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart3 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>-- time, numeric |
| >>create table mcStatPart6 |
| +>(a numeric (8, 3) not null not droppable, |
| +>b time not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart6 values (1.25, time'10:12:23',1,'xyz'),(2.25, time'11:02:01',1,'xyz'),(6.25, time'08:08:02',1,'xyz'),(3.25, time'04:04:04',1,'xyz'), |
| +> (5.25, time'08:08:02',1,'xyz'),(6.24, time'04:05:04',1,'xyz'),(1.25, time'12:12:12',1,'xyz'), |
| +> (2.25, time'10:12:23',1,'xyz'),(3.65, time'11:02:01',1,'xyz'),(3.55, time'08:08:02',1,'xyz'),(3.5, time'04:04:04',1,'xyz'), |
| +> (2.6, time'08:08:02',1,'xyz'),(2.25, time'04:04:04',1,'xyz'),(6.25, time'12:12:12',1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart6 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>-- date type |
| >>create table mcStatPart2 |
| +>(a int not null not droppable, |
| +>b date not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart2 values (1, date'1990-01-01',1,'xyz'),(1, date'1990-02-01',1,'xyz'),(1, date'1990-03-01',1,'xyz'),(2, date'1990-04-01',1,'xyz'), |
| +> (2, date'1990-03-01',1,'xyz'),(2, date'1990-05-01',1,'xyz'),(2, date'1990-05-03',1,'xyz'), |
| +> (3, date'1990-01-01',1,'xyz'),(3, date'1990-02-01',1,'xyz'),(3, date'1990-03-01',1,'xyz'),(3, date'1990-04-01',1,'xyz'), |
| +> (4, date'1990-03-01',1,'xyz'),(4, date'1990-04-01',1,'xyz'),(4, date'1990-05-01',1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart2 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>-- char type |
| >>create table mcStatPart1 |
| +>(a int not null not droppable, |
| +>b char(10) not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart1 values (1,'123',1,'xyz'),(1,'133',1,'xyz'),(1,'423',1,'xyz'),(2,'111',1,'xyz'),(2,'223',1,'xyz'),(2,'323',1,'xyz'),(2,'423',1,'xyz'), |
| +> (3,'123',1,'xyz'),(3,'133',1,'xyz'),(3,'423',1,'xyz'),(4,'111',1,'xyz'),(4,'223',1,'xyz'),(4,'323',1,'xyz'),(4,'423',1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart1 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>-- varchar type |
| >>create table mcStatPart8 |
| +>(a int not null not droppable, |
| +>b varchar(10) not null not droppable, |
| +>f int, txt char(100), |
| +>primary key (a,b)) |
| +>salt using 8 partitions ; |
| |
| --- SQL operation complete. |
| >> |
| >>insert into mcStatPart8 values (1,'123',1,'xyz'),(1,'133',1,'xyz'),(1,'423',1,'xyz'),(2,'111',1,'xyz'),(2,'223',1,'xyz'),(2,'323',1,'xyz'),(2,'423',1,'xyz'), |
| +> (3,'123',1,'xyz'),(3,'133',1,'xyz'),(3,'423',1,'xyz'),(4,'111',1,'xyz'),(4,'223',1,'xyz'),(4,'323',1,'xyz'),(4,'423',1,'xyz'); |
| |
| --- 14 row(s) inserted. |
| >>update statistics for table mcStatPart8 on every column, (b,a,"_SALT_"); |
| |
| --- SQL operation complete. |
| >> |
| >>obey TEST016(mcStatsPart_DML); |
| >> |
| >>-- int, smallint, largeint |
| >>prepare st1 from select f,count(f) from mcStatPart7 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART7 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184195273823 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 553.72 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart7 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543409395 |
| select_list ............ TRAFODION.HBASE.MCSTATPART7.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART7. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART7.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART7.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART7.A, |
| TRAFODION.HBASE.MCSTATPART7.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.22 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART7.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART7.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART7 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 477.46 |
| EST_TOTAL_COST ......... 477.46 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART7 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = <min>) |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = <max>) |
| |
| --- SQL operation complete. |
| >> |
| >>-- decimal |
| >>prepare st1 from select f,count(f) from mcStatPart5 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART5 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184195982638 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 568.72 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart5 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 568.72 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543411300 |
| select_list ............ TRAFODION.HBASE.MCSTATPART5.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART5. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 568.72 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART5.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART5.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 568.72 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART5.A, |
| TRAFODION.HBASE.MCSTATPART5.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.1 |
| EST_TOTAL_COST ......... 568.72 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART5.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART5.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART5 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 491.56 |
| EST_TOTAL_COST ......... 491.56 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART5 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = <min>) |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = <max>) |
| |
| --- SQL operation complete. |
| >> |
| >>-- timestamp type |
| >>prepare st1 from select f,count(f) from mcStatPart3 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART3 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184196719364 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 553.66 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart3 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 553.66 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543413146 |
| select_list ............ TRAFODION.HBASE.MCSTATPART3.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART3. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 553.66 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART3.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART3.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 553.66 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART3.A, |
| TRAFODION.HBASE.MCSTATPART3.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.16 |
| EST_TOTAL_COST ......... 553.66 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART3.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART3.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 477.46 |
| EST_TOTAL_COST ......... 477.46 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART3 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = <min>) |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = <max>) |
| |
| --- SQL operation complete. |
| >> |
| >>-- time, nemuric |
| >>prepare st1 from select f,count(f) from mcStatPart6 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART6 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184197447093 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 524.1 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart6 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 524.1 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543414693 |
| select_list ............ TRAFODION.HBASE.MCSTATPART6.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART6. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 524.1 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART6.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART6.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 524.1 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART6.A, |
| TRAFODION.HBASE.MCSTATPART6.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.16 |
| EST_TOTAL_COST ......... 524.1 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART6.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART6.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART6 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 450 |
| EST_TOTAL_COST ......... 450 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART6 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = <min>) |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = <max>) |
| |
| --- SQL operation complete. |
| >> |
| >>-- date type |
| >>prepare st1 from select f,count(f) from mcStatPart2 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART2 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184198151284 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 524.16 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart2 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 524.16 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543416280 |
| select_list ............ TRAFODION.HBASE.MCSTATPART2.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART2. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 524.16 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART2.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART2.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 524.16 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART2.A, |
| TRAFODION.HBASE.MCSTATPART2.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.22 |
| EST_TOTAL_COST ......... 524.16 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART2.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART2.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 450 |
| EST_TOTAL_COST ......... 450 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART2 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = <min>) |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = <max>) |
| |
| --- SQL operation complete. |
| >> |
| >>-- char type |
| >>prepare st1 from select f,count(f) from mcStatPart1 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART1 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184198803415 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 538.55 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart1 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 538.55 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543417806 |
| select_list ............ TRAFODION.HBASE.MCSTATPART1.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART1. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 538.55 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART1.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART1.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 538.55 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART1.A, |
| TRAFODION.HBASE.MCSTATPART1.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.22 |
| EST_TOTAL_COST ......... 538.55 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART1.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART1.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 463.33 |
| EST_TOTAL_COST ......... 463.33 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART1 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = '<min>') |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = '<max>') |
| |
| --- SQL operation complete. |
| >> |
| >>-- varchar type |
| >>prepare st1 from select f,count(f) from mcStatPart8 <<+ cardinality 10e8 >> group by f; |
| |
| --- SQL command prepared. |
| >>explain options 'f' st1; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 4 . 5 root 1.00E+000 |
| 3 . 4 hash_partial_groupby 1.00E+000 |
| 2 . 3 esp_exchange 1:4(hash2) 1.00E+000 |
| 1 . 2 hash_partial_groupby 1.00E+000 |
| . . 1 trafodion_scan MCSTATPART8 1.00E+009 |
| |
| --- SQL operation complete. |
| >>explain st1; |
| |
| ------------------------------------------------------------------ PLAN SUMMARY |
| MODULE_NAME .............. DYNAMICALLY COMPILED |
| STATEMENT_NAME ........... ST1 |
| PLAN_ID .................. 212371184199525160 |
| ROWS_OUT ................. 1 |
| EST_TOTAL_COST ......... 553.72 |
| STATEMENT ................ select f,count(f) |
| from mcStatPart8 <<+ cardinality 10e8 >> |
| group by f; |
| |
| |
| ------------------------------------------------------------------ NODE LISTING |
| ROOT ====================================== SEQ_NO 5 ONLY CHILD 4 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| est_memory_per_node .... 10240.00(Limit), 0.00(BMOs), 0.00(nBMOs) MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| affinity_value ......... 0 |
| max_max_cardinal 1,000,000,000 |
| esp_2_node_map ......... (\NSK:-1:-1:-1:-1) |
| xn_access_mode ......... read_only |
| xn_autoabort_interval 0 |
| auto_query_retry ....... enabled |
| plan_version ....... 2,600 |
| embedded_arkcmp ........ used |
| IS_SQLCI ............... ON |
| LDAP_USERNAME |
| SEABASE_VOLATILE_TABLES ON |
| HBASE_ASYNC_DROP_TABLE OFF |
| HBASE_SERIALIZATION .... ON |
| HBASE_SMALL_SCANNER .... SYSTEM |
| HBASE_FILTER_PREDS ..... 2 |
| TRAF_ALIGNED_ROW_FORMAT ON |
| TRAF_INDEX_CREATE_OPT ON |
| SCHEMA ................. TRAFODION.HBASE |
| QUERY_CACHE ............ 0 |
| CACHE_HISTOGRAMS ....... OFF |
| HBASE_MIN_BYTES_PER_ESP 1 |
| USTAT_COLLECT_MC_SKEW_V ON |
| PARALLEL_NUM_ESPS ...... 4 |
| ObjectUIDs ............. 8472535850543419349 |
| select_list ............ TRAFODION.HBASE.MCSTATPART8.F, |
| cast(sum(count_nonull(TRAFODION.HBASE.MCSTATPART8. |
| F))) |
| |
| |
| HASH_PARTIAL_GROUPBY_ROOT ================= SEQ_NO 4 ONLY CHILD 3 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| memory_quota ........... 1200 MB |
| max_card_est ........... 1 |
| fragment_id ............ 0 |
| parent_frag ............ (none) |
| fragment_type .......... master |
| est_memory_per_instance 0.041 KB |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART8.F |
| aggregates ............. sum(count_nonull(TRAFODION.HBASE.MCSTATPART8.F)) |
| |
| |
| ESP_EXCHANGE ============================== SEQ_NO 3 ONLY CHILD 2 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 0.01 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| est_memory_per_node .... 29.297 KB |
| buffer_size ........ 5,000 |
| record_length ......... 16 |
| parent_processes ....... 1 |
| child_processes ........ 4 |
| child_partitioning_func hash2 partitioned 4 ways on |
| (TRAFODION.HBASE.MCSTATPART8.A, |
| TRAFODION.HBASE.MCSTATPART8.B) |
| |
| |
| HASH_PARTIAL_GROUPBY_LEAF ================= SEQ_NO 2 ONLY CHILD 1 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ................. 1 |
| EST_OPER_COST ............ 5.22 |
| EST_TOTAL_COST ......... 553.72 |
| DESCRIPTION |
| max_card_est ........... 1 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| grouping_columns ....... TRAFODION.HBASE.MCSTATPART8.F |
| aggregates ............. count_nonull(TRAFODION.HBASE.MCSTATPART8.F) |
| |
| |
| TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN |
| TABLE_NAME ............... MCSTATPART8 |
| REQUESTS_IN .............. 1 |
| ROWS_OUT ..... 1,000,000,000 |
| EST_OPER_COST .......... 477.46 |
| EST_TOTAL_COST ......... 477.46 |
| DESCRIPTION |
| max_card_est ........... 1e+09 |
| fragment_id ............ 2 |
| parent_frag ............ 0 |
| fragment_type .......... esp |
| scan_type .............. subset scan of table TRAFODION.HBASE.MCSTATPART8 |
| object_type ............ Trafodion |
| cache_size ......... 1,024 |
| cache_blocks ........... OFF |
| probes ................. 1 |
| rows_accessed .......... 1e+09 |
| column_retrieved ....... #1:1 |
| key_columns ............ _SALT_, A, B |
| begin_key .............. (_SALT_ = (\:_sys_HostVarLoHashPart Hash2Distrib |
| 8)), (A = <min>), (B = '<min>') |
| end_key ................ (_SALT_ = (\:_sys_HostVarHiHashPart Hash2Distrib |
| 8)), (A = <max>), (B = '<max>') |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
