Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
any
-
None
-
All
Description
On a large table with a divisioning column, it was noticed that occasionally UPDATE STATISTICS using sampling produces an unusually large UEC in the last interval of the histogram. The circumstances under which this happened was that the last divisioning value had just one row and, by luck of the draw, that row happened to be included in the sample.
This can be reproduced by manually creating such a sample (and using CQD USTAT_SAMPLE_TABLE_NAME to cause UPDATE STATS to use this sample). For example, run the following three scripts:
Script ddl.sql:
drop table if exists weird_table;
CREATE TABLE weird_table
(
TIME_STAMP TIMESTAMP(6) NO DEFAULT NOT NULL NOT
DROPPABLE NOT SERIALIZED
, GROUPID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, COMPANYID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, CHECKSUM LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, PRIMARY KEY (COMPANYID ASC, GROUPID ASC, TIME_STAMP DESC, CHECKSUM ASC)
)
SALT USING 4 PARTITIONS
ON (TIME_STAMP)
DIVISION BY (DATE_TRUNC('DAY',TIME_STAMP) DESC
NAMED AS ("DIVISION_1"))
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'GZ',
MEMSTORE_FLUSH_SIZE = '1073741824'
)
;
drop table if exists weird_sample;
CREATE TABLE weird_sample
(
TIME_STAMP TIMESTAMP(6) NO DEFAULT NOT NULL NOT
DROPPABLE NOT SERIALIZED
, GROUPID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, COMPANYID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, CHECKSUM LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
NOT SERIALIZED
, PRIMARY KEY (COMPANYID ASC, GROUPID ASC, TIME_STAMP DESC, CHECKSUM ASC)
)
SALT USING 4 PARTITIONS
ON (TIME_STAMP)
DIVISION BY (DATE_TRUNC('DAY',TIME_STAMP) DESC
NAMED AS ("DIVISION_1"))
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'GZ',
MEMSTORE_FLUSH_SIZE = '1073741824'
)
;
Script populate.sql:
– populate the table in such a way that the divisioning column
– has a few very frequent values but just one infrequent value
upsert using load into weird_table
select timestamp '1970-01-01 00:00:00' + cast(c0+10*c1+100*c2+1000*c3+10000*c4 as interval minute(8)),
3*c0 + 7*c4,
c4,
11*c2 + 2*c3 + 3*c1
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c0
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,10,11,12,13,14,15,16,17,18,19 as c4;
insert into weird_table select max(TIME_STAMP) + interval '1' day, 999, 999, 999 from weird_table;
upsert using load into weird_sample select * from weird_table sample random 10 percent;
upsert into weird_sample select * from weird_table where time_stamp = (select max(time_stamp) from weird_table);
Script repro.sql:
cqd USTAT_SAMPLE_TABLE_NAME 'trafodion.sch.weird_sample' ;
– below, use the number of rows in weird_sample
update statistics for table weird_table on "DIVISION_1" sample 20080 rows;
showstats for table weird_table on "DIVISION_1" detail;
In this particular example, the "showstats" showed a UEC of 9 for the last interval. More dramatic values can be obtained by increasing the table size relative to sample size; from the examples I have seen the UEC of the last interval seems to be about equal to the ratio of table size to sample size.
Attachments
Issue Links
- links to
