Trafodion

Excessive execution time for query on key columns; unsuitable MDAM plan generated.

Bug #1415251 reported by Julie Thai on 2015-01-27

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	Trafodion	Fix Released	High	Ravisha Neelakanthappa	Trafodion r1.1

Bug Description

This query:
PREPARE XX FROM SELECT * FROM salttbl1
WHERE colnum < 65000.00 AND collint BETWEEN 549755862888 AND 549755864389;

takes approx 32+ mins on 6 node cluster. A MDAM plan is generated. The table is
salted on primary key (colnum, collint), 8 partitions. The table contains 100,000 rows.

With cqd MDAM_SCAN_METHOD 'OFF', query execution is 11 secs.
As per Qifan, MDAM is not suitable because column COLNUM is high in UEC.

SQL>PREPARE XX FROM SELECT * FROM salttbl1
WHERE colnum < 65000.00 AND collint BETWEEN 549755862888 AND 549755864389;

--- SQL command prepared.

SQL>EXECUTE XX;
…
…
--- 1502 row(s) selected.
>>get statistics;

Start Time 2015/01/27 13:44:07.257133
End Time 2015/01/27 14:16:18.776921
Elapsed Time 00:32:11.519788
Compile Time 00:00:03.085995
Execution Time 00:32:11.519788

Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.SEABASE.SALTTBL1
6461902 1502 260020 41463918 1095187807

--- SQL operation complete.
>>EXPLAIN options 'f' XX;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root 9.76E+002
. . 1 trafodion_scan SALTTBL1 9.76E+002

--- SQL operation complete.
>>EXPLAIN XX;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212289155047250875
ROWS_OUT ............... 977
EST_TOTAL_COST ........... 0.54
STATEMENT ................ SELECT *
                           FROM salttbl1
                           WHERE colnum < 65000.00 AND collint BETWEEN
                             549755862888 AND 549755864389 ;

------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ............... 977
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.54
DESCRIPTION
  max_card_est ....... 1,503
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  max_max_cardinality 977
  total_overflow_size .... 0.00 KB
  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
  ObjectUIDs ............. 671093363741838505
  select_list ............ TRAFODION.SEABASE.SALTTBL1.COLINTU,
                             TRAFODION.SEABASE.SALTTBL1.COLINTS,
                             TRAFODION.SEABASE.SALTTBL1.COLSINTU,
                             TRAFODION.SEABASE.SALTTBL1.COLSINTS,
                             TRAFODION.SEABASE.SALTTBL1.COLLINT,
                             TRAFODION.SEABASE.SALTTBL1.COLNUM,
                             TRAFODION.SEABASE.SALTTBL1.COLFLT,
                             TRAFODION.SEABASE.SALTTBL1.COLDEC,
                             TRAFODION.SEABASE.SALTTBL1.COLREAL,
                             TRAFODION.SEABASE.SALTTBL1.COLDBL,
                             TRAFODION.SEABASE.SALTTBL1.COLDATE,
                             TRAFODION.SEABASE.SALTTBL1.COLTIME,
                             TRAFODION.SEABASE.SALTTBL1.COLTS,
                             TRAFODION.SEABASE.SALTTBL1.COLCHRISO,
                             TRAFODION.SEABASE.SALTTBL1.COLCHRUCS2,
                             TRAFODION.SEABASE.SALTTBL1.COLVCHRISO,
                             TRAFODION.SEABASE.SALTTBL1.COLVCHRUCS2

TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... SALTTBL1
REQUESTS_IN .............. 1
ROWS_OUT ............... 977
EST_OPER_COST ............ 0.54
EST_TOTAL_COST ........... 0.54
DESCRIPTION
  max_card_est ....... 1,503
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan limited by mdam of table
                             TRAFODION.SEABASE.SALTTBL1
  object_type ............ Trafodion
  cache_size ......... 1,503
  probes ................. 1
  rows_accessed ........ 978
  key_columns ............ _SALT_, COLNUM, COLLINT
  mdam_disjunct .......... (COLNUM < 65000000) and (COLLINT >= 549755862888)
                             and (COLLINT <= 549755864389)
  part_key_predicates .... (COLNUM < 65000000)

--- SQL operation complete.

The histogram stats:
>>SHOWSTATS FOR TABLE salttbl1 ON EXISTING COLUMNS;

Histogram data for Table TRAFODION.SEABASE.SALTTBL1
Table ID: 171756752133979809

Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
756206601 8 100000 8 "_SALT_"
756206606 62 100000 100000 COLVCHRUCS2
756206611 62 100000 100000 COLVCHRISO
756206616 62 100000 100000 COLCHRUCS2
756206621 62 100000 100000 COLCHRISO
756206626 50 100000 100000 COLTS
756206631 3 100000 3 COLTIME
756206636 50 100000 100000 COLDATE
756206641 48 100000 100000 COLDBL
756206646 48 100000 100000 COLREAL
756206651 48 100000 100000 COLDEC
756206656 48 100000 100000 COLFLT
756206661 48 100000 100000 COLNUM
756206666 48 100000 100000 COLLINT
756206671 48 100000 32767 COLSINTS
756206676 48 100000 65535 COLSINTU
756206681 48 100000 100000 COLINTS
756206686 48 100000 100000 COLINTU
756206691 1 100000 100000 "_SALT_", COLNUM, COLLINT

--- SQL operation complete.
>>showstats for table salttbl1 on "_SALT_", colnum, collint detail;

Detailed Histogram data for Table TRAFODION.SEABASE.SALTTBL1
Table ID: 671093363741838505

Hist ID: 1565500798
Column(s): "_SALT_"
Total Rows: 100000
Total UEC: 8
Low Value: (0)
High Value: (7)
Intervals: 8

Number Rowcount UEC Boundary
====== =========== =========== ======================================
     0 0 0 (0)
     1 12450 1 (0)
     2 12658 1 (1)
     3 12511 1 (2)
     4 12437 1 (3)
     5 12481 1 (4)
     6 12644 1 (5)
     7 12447 1 (6)
     8 12372 1 (7)

Hist ID: 1565500858
Column(s): COLNUM
Total Rows: 100000
Total UEC: 100000
Low Value: (.000)
High Value: (99999.000)
Intervals: 48

Number Rowcount UEC Boundary
====== =========== =========== ======================================
     0 0 0 (.000)
     1 2083 2083 (2082.000)
     2 2083 2083 (4165.000)
     3 2083 2083 (6248.000)
     4 2083 2083 (8331.000)
     5 2083 2083 (10414.000)
     6 2083 2083 (12497.000)
     7 2083 2083 (14580.000)
     8 2083 2083 (16663.000)
     9 2083 2083 (18746.000)
    10 2083 2083 (20829.000)
    11 2083 2083 (22912.000)
    12 2083 2083 (24995.000)
    13 2083 2083 (27078.000)
    14 2083 2083 (29161.000)
    15 2083 2083 (31244.000)
    16 2083 2083 (33327.000)
    17 2083 2083 (35410.000)
    18 2083 2083 (37493.000)
    19 2083 2083 (39576.000)
    20 2083 2083 (41659.000)
    21 2083 2083 (43742.000)
    22 2083 2083 (45825.000)
    23 2083 2083 (47908.000)
    24 2083 2083 (49991.000)
    25 2083 2083 (52074.000)
    26 2083 2083 (54157.000)
    27 2083 2083 (56240.000)
    28 2083 2083 (58323.000)
    29 2083 2083 (60406.000)
    30 2083 2083 (62489.000)
    31 2083 2083 (64572.000)
    32 2083 2083 (66655.000)
    33 2084 2084 (68739.000)
    34 2084 2084 (70823.000)
    35 2084 2084 (72907.000)
    36 2084 2084 (74991.000)
    37 2084 2084 (77075.000)
    38 2084 2084 (79159.000)
    39 2084 2084 (81243.000)
    40 2084 2084 (83327.000)
    41 2084 2084 (85411.000)
    42 2084 2084 (87495.000)
    43 2084 2084 (89579.000)
    44 2084 2084 (91663.000)
    45 2084 2084 (93747.000)
    46 2084 2084 (95831.000)
    47 2084 2084 (97915.000)
    48 2084 2084 (99999.000)

Hist ID: 1565500863
Column(s): COLLINT
Total Rows: 100000
Total UEC: 100000
Low Value: (549755813888)
High Value: (549755913887)
Intervals: 48

Number Rowcount UEC Boundary
====== =========== =========== ======================================
     0 0 0 (549755813888)
     1 2083 2083 (549755815970)
     2 2083 2083 (549755818053)
     3 2083 2083 (549755820136)
     4 2083 2083 (549755822219)
     5 2083 2083 (549755824302)
     6 2083 2083 (549755826385)
     7 2083 2083 (549755828468)
     8 2083 2083 (549755830551)
     9 2083 2083 (549755832634)
    10 2083 2083 (549755834717)
    11 2083 2083 (549755836800)
    12 2083 2083 (549755838883)
    13 2083 2083 (549755840966)
    14 2083 2083 (549755843049)
    15 2083 2083 (549755845132)
    16 2083 2083 (549755847215)
    17 2083 2083 (549755849298)
    18 2083 2083 (549755851381)
    19 2083 2083 (549755853464)
    20 2083 2083 (549755855547)
    21 2083 2083 (549755857630)
    22 2083 2083 (549755859713)
    23 2083 2083 (549755861796)
    24 2083 2083 (549755863879)
    25 2083 2083 (549755865962)
    26 2083 2083 (549755868045)
    27 2083 2083 (549755870128)
    28 2083 2083 (549755872211)
    29 2083 2083 (549755874294)
    30 2083 2083 (549755876377)
    31 2083 2083 (549755878460)
    32 2083 2083 (549755880543)
    33 2084 2084 (549755882627)
    34 2084 2084 (549755884711)
    35 2084 2084 (549755886795)
    36 2084 2084 (549755888879)
    37 2084 2084 (549755890963)
    38 2084 2084 (549755893047)
    39 2084 2084 (549755895131)
    40 2084 2084 (549755897215)
    41 2084 2084 (549755899299)
    42 2084 2084 (549755901383)
    43 2084 2084 (549755903467)
    44 2084 2084 (549755905551)
    45 2084 2084 (549755907635)
    46 2084 2084 (549755909719)
    47 2084 2084 (549755911803)
    48 2084 2084 (549755913887)

--- SQL operation complete.

With cqd MDAM_SCAN_METHOD 'OFF':
SQL>PREPARE XX FROM SELECT * FROM salttbl1
WHERE colnum < 65000.00 AND collint BETWEEN 549755862888 AND 549755864389;

--- SQL command prepared.

SQL>EXECUTE XX;
…
…
--- 1502 row(s) selected.
>>get statistics;

Start Time 2015/01/27 15:05:19.506685
End Time 2015/01/27 15:05:29.783267
Elapsed Time 00:00:10.276582
Compile Time 00:00:03.317591
Execution Time 00:00:10.276582

Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.SEABASE.SALTTBL1
100000 1502 12 62366670 6089427

--- SQL operation complete.
>>EXPLAIN options 'f' XX;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root 9.76E+002
. . 1 trafodion_scan SALTTBL1 9.76E+002

--- SQL operation complete.
>>EXPLAIN XX;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212289159919494232
ROWS_OUT ............... 977
EST_TOTAL_COST ........... 0.53
STATEMENT ................ SELECT *
                           FROM salttbl1
                           WHERE colnum < 65000.00 AND collint BETWEEN
                             549755862888 AND 549755864389 ;

------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ............... 977
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.53
DESCRIPTION
  max_card_est ....... 1,503
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  max_max_cardinality 977
  total_overflow_size .... 0.00 KB
  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
  MDAM_SCAN_METHOD ....... OFF
  ObjectUIDs ............. 671093363741838505
  select_list ............ TRAFODION.SEABASE.SALTTBL1.COLINTU,
                             TRAFODION.SEABASE.SALTTBL1.COLINTS,
                             TRAFODION.SEABASE.SALTTBL1.COLSINTU,
                             TRAFODION.SEABASE.SALTTBL1.COLSINTS,
                             TRAFODION.SEABASE.SALTTBL1.COLLINT,
                             TRAFODION.SEABASE.SALTTBL1.COLNUM,
                             TRAFODION.SEABASE.SALTTBL1.COLFLT,
                             TRAFODION.SEABASE.SALTTBL1.COLDEC,
                             TRAFODION.SEABASE.SALTTBL1.COLREAL,
                             TRAFODION.SEABASE.SALTTBL1.COLDBL,
                             TRAFODION.SEABASE.SALTTBL1.COLDATE,
                             TRAFODION.SEABASE.SALTTBL1.COLTIME,
                             TRAFODION.SEABASE.SALTTBL1.COLTS,
                             TRAFODION.SEABASE.SALTTBL1.COLCHRISO,
                             TRAFODION.SEABASE.SALTTBL1.COLCHRUCS2,
                             TRAFODION.SEABASE.SALTTBL1.COLVCHRISO,
                             TRAFODION.SEABASE.SALTTBL1.COLVCHRUCS2

TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... SALTTBL1
REQUESTS_IN .............. 1
ROWS_OUT ............... 977
EST_OPER_COST ............ 0.53
EST_TOTAL_COST ........... 0.53
DESCRIPTION
  max_card_est ....... 1,503
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SEABASE.SALTTBL1
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_size ........ 10,000
  probes ................. 1
  rows_accessed .... 100,000
  key_columns ............ _SALT_, COLNUM, COLLINT
  executor_predicates .... (COLNUM < cast(65000000)) and (COLLINT >=
                             549755862888) and (COLLINT <= 549755864389) and
                             (COLLINT >= 549755862888) and (COLLINT <=
                             549755864389)
  part_key_predicates .... (COLNUM < 65000000)

--- SQL operation complete.

To reproduce:
CREATE TABLE salttbl1(
colintu int unsigned not null, colints int signed not null,
colsintu smallint unsigned not null, colsints smallint signed not null,
collint largeint not null, colnum numeric(11,3) not null,
colflt float not null, coldec decimal(11,2) not null,
colreal real not null, coldbl double precision not null,
coldate date not null, coltime time not null,
colts timestamp not null,
colchriso char(90) character set iso88591 not null,
colchrucs2 char(111) character set ucs2 not null,
colvchriso varchar(113) character set iso88591 not null,
colvchrucs2 varchar(115) character set ucs2 not null,
primary key (colnum, collint))
salt using 8 partitions;
LOAD INTO salttbl1 SELECT
c1+c2*10+c3*100+c4*1000+c5*10000,
(c1+c2*10+c3*100+c4*1000+c5*10000) - 50000,
mod(c1+c2*10+c3*100+c4*1000+c5*10000, 65535),
mod(c1+c2*10+c3*100+c4*1000+c5*10000, 32767),
(c1+c2*10+c3*100+c4*1000+c5*10000) + 549755813888,
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as numeric(11,3)),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as float),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as decimal(11,2)),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as real),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as double precision),
cast(converttimestamp(210614299200000000 +
(86400000000 * (c1+c2*10+c3*100+c4*1000+c5*10000))) as date),
time'00:00:00' + cast(mod(c1+c2*10+c3*100+c4*1000+c5*10000,3)
as interval minute),
converttimestamp(210614299200000000 + (86400000000 *
(c1+c2*10+c3*100+c4*1000+c5*10000)) + (1000000 * (c1+c2*10+c3*100)) +
(60000000 * (c1+c2*10)) + (3600000000 * (c1+c2*10))),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(90) character set iso88591),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as char(111) character set ucs2),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as varchar(113) character set iso88591),
cast(c1+c2*10+c3*100+c4*1000+c5*10000 as varchar(115) character set ucs2)
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5;
select count(*) from salttbl1;
UPDATE STATISTICS FOR TABLE salttbl1 ON EVERY COLUMN;
SHOWSTATS FOR TABLE salttbl1 ON EXISTING COLUMNS;
PREPARE XX FROM SELECT * FROM salttbl1
WHERE colnum < 65000.00 AND collint BETWEEN 549755862888 AND 549755864389;
EXECUTE XX;
get statistics;
EXPLAIN options 'f' XX;
EXPLAIN XX;

Tags:

Revision history for this message

Ravisha Neelakanthappa (ravisha-neelakanthappa) wrote on 2015-04-02:

I think this bug got fixed because of recent change to MDAM costing where cost of Cost of sending 'probes' to materialize values of prefix key column(s) with missing predicate(s) is added to the MDAM cost.

Now I get regular single subset scan and it finishes in 2+ seconds.

TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... SALTTBL1
REQUESTS_IN .............. (not found)
ROWS_OUT ............... 977
EST_OPER_COST .... 6,370,999
EST_TOTAL_COST ... 6,370,999
DESCRIPTION
  max_card_est ....... 1,503
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.RSCH.SALTTBL1
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_size ........ 10,000
  probes ................. 1
  rows_accessed .... 100,000
  key_columns ............ _SALT_, COLNUM, COLLINT
  executor_predicates .... (COLNUM < cast(65000000)) and (COLLINT >=
                             549755862888) and (COLLINT <= 549755864389) and
                             (COLLINT >= 549755862888) and (COLLINT <=
                             549755864389)
  part_key_predicates .... (COLNUM < 65000000)

>> get statistics;

Start Time 2015/04/02 14:42:53.509907
End Time 2015/04/02 14:42:55.939102
Elapsed Time 00:00:02.429195
Compile Time 00:00:04.891789
Execution Time 00:00:02.429195

Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.RSCH.SALTTBL1
100000 1502 12 62366670 833949

Revision history for this message

Ravisha Neelakanthappa (ravisha-neelakanthappa) wrote on 2015-04-07:

Julie has confirmed this bug has been fixed. I am closing it.

Changed in trafodion:
milestone:	none → r1.1
status:	New → Fix Released

Revision history for this message

Julie Thai (julie-y-thai) wrote on 2015-04-07:

Download full text (7.2 KiB)

verified on cluster with 20150329_0830 build and again with 20150405_0830 build:

SQL>PREPARE XX FROM
SELECT * FROM salttbl1 WHERE colnum < 65000.00
AND collint BETWEEN 549755862888 AND 549755864389;
--- SQL command prepared.

SQL>EXPLAIN options 'f' XX;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

1 . 2 root 4.00E+000
. . 1 trafodion_scan SALTTBL1 4.00E+000

--- SQL operation complete.

SQL>EXPLAIN XX;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212295197625372509
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ SELECT *
                           FROM salttbl1
                           WHERE colnum < 65000.00
AND collint BETWEEN
                             549755862888 AND 549755864389

------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est .......... 50
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  max_max_cardinality .... 4
  total_overflow_size .... 0.00 KB
  xn_access_mode ......... read_only
  xn_autoabort_interval 0
  auto_query_retry ....... enabled
  plan_version ....... 2,600
  embedded_arkcmp ........ used
  LDAP_USERNAME .......... QAUSER_CMP
  SCHEMA ................. TRAFODION.SALT_SALTTBL
  CATALOG ................ TRAFODION
  QUERY_CACHE ............ 0
  ObjectUIDs ............. 187525312438635580
  select_list ............ TRAFODION.SALT_SALTTBL.SALTTBL1.COLINTU,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLINTS,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLSINTU,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLSINTS,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLLINT,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLNUM,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLFLT,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLDEC,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLREAL,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLDBL,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLDATE,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLTIME,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLTS,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLCHRISO,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLCHRUCS2,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLVCHRISO,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLVCHRUCS2

TRAFODION_SCAN ====...

verified on cluster with 20150329_0830 build and again with 20150405_0830 build:

SQL>PREPARE XX FROM
SELECT * FROM salttbl1 WHERE colnum < 65000.00
AND collint BETWEEN 549755862888 AND 549755864389;
--- SQL command prepared.

SQL>EXPLAIN options 'f' XX;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  4.00E+000
.    .    1    trafodion_scan                  SALTTBL1              4.00E+000

--- SQL operation complete.

SQL>EXPLAIN XX;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212295197625372509
ROWS_OUT ................. 4
EST_TOTAL_COST ........... 0.01
STATEMENT ................ SELECT *
                           FROM salttbl1
                           WHERE colnum < 65000.00
AND collint BETWEEN
                             549755862888 AND 549755864389

------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est .......... 50
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  max_max_cardinality .... 4
  total_overflow_size .... 0.00 KB
  xn_access_mode ......... read_only
  xn_autoabort_interval    0
  auto_query_retry ....... enabled
  plan_version ....... 2,600
  embedded_arkcmp ........ used
  LDAP_USERNAME .......... QAUSER_CMP
  SCHEMA ................. TRAFODION.SALT_SALTTBL
  CATALOG ................ TRAFODION
  QUERY_CACHE ............ 0
  ObjectUIDs ............. 187525312438635580
  select_list ............ TRAFODION.SALT_SALTTBL.SALTTBL1.COLINTU,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLINTS,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLSINTU,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLSINTS,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLLINT,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLNUM,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLFLT,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLDEC,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLREAL,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLDBL,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLDATE,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLTIME,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLTS,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLCHRISO,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLCHRUCS2,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLVCHRISO,
                             TRAFODION.SALT_SALTTBL.SALTTBL1.COLVCHRUCS2

TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... SALTTBL1
REQUESTS_IN .............. 1
ROWS_OUT ................. 4
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est .......... 50
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SALT_SALTTBL.SALTTBL1
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_size ........... 100
  probes ................. 1
  rows_accessed ........ 100
  key_columns ............ _SALT_, COLNUM, COLLINT
  executor_predicates .... (COLNUM < cast(65000000)) and (COLLINT >=
                             549755862888) and (COLLINT <= 549755864389) and
                             (COLLINT >= 549755862888) and (COLLINT <=
                             549755864389)
  part_key_predicates .... (COLNUM < 65000000)

--- SQL operation complete.

SQL>EXECUTE XX;
COLINTU    COLINTS     COLSINTU COLSINTS COLLINT              COLNUM        COLFLT                   COLDEC        COLREAL         COLDBL                   COLDATE    COLTIME  COLTS                      COLCHRISO                                                                                  COLCHRUCS2                                                                                                                       COLVCHRISO                                                                                                        COLVCHRUCS2                                                                                            
---------- ----------- -------- -------- -------------------- ------------- ------------------------ ------------- --------------- ------------------------ ---------- -------- -------------------------- ------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
     49002        -998    49002    16235         549755862890     49002.000                  49002.0      49002.00         49002.0                  49002.0 2096-02-29 00:00:00 2096-02-29 02:02:02.000000 49002                                                                                      49002                                                                                                                            49002                                                                                                             49002                                                                                                  
     49005        -995    49005    16238         549755862893     49005.000                  49005.0      49005.00         49005.0                  49005.0 2096-03-03 00:00:00 2096-03-03 05:05:05.000000 49005                                                                                      49005                                                                                                                            49005                                                                                                             49005                    
...
...
...
--- 1502 row(s) selected.

SQL>get statistics;

Start Time             2015/04/07 13:13:50.383251
End Time               2015/04/07 13:14:21.631590
Elapsed Time                      00:00:31.248339
Compile Time                      00:00:00.034715
Execution Time                    00:00:31.248339

Table Name             Records        Records      Hdfs       Hdfs I/O    Hdfs Access
                      Accessed           Used      I/Os          Bytes     Time(usec)
TRAFODION.SALT_SALTTBL.SALTTBL1
                        100000           1502      1002       62366670       19408894

--- SQL operation complete.

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.