1. Problem description
Table card is significantly high compared to the actual row
count when indexsampling option is provided.
This issue occurs if table data pages are highly skewed and we
have both 'tablesample' and 'indexsample' options specified.
Ex:
CREATE TABLE TABLE1( a BIGINT NOT NULL, b INTEGER
NOT NULL, c VARCHAR(254), d
VARCHAR(254), e VARCHAR(254), f VARCHAR(254) )
COMPRESS YES
STATIC VALUE COMPRESSION
DISTRIBUTE BY HASH(a) in TBS6 ORGANIZE BY ROW USING (b)
CREATE UNIQUE INDEX INDEX1 ON DB2INST1.TABLE1 (b asc, c asc, a
asc) COMPRESS YES INCLUDE NULL KEYS ALLOW REVERSE SCANS
load from data.del of del messages msg1.txt insert into
db2inst1.table1
Number of rows read = 250000
Number of rows skipped = 0
Number of rows loaded = 250000
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 250000
select count(*) from db2inst1.table1
1
-----------
250000
1 record(s) selected.
RUNSTATS ON TABLE db2inst1.table1 on all columns with
distribution on all columns and sampled detailed indexes all
SELECT substr(tabschema,1,10) as tabschema, substr(tabname,1,10)
as tabname, card, active_blocks,npages, fpages from
syscat.tables where tabname='TABLE1' and tabschema='DB2INST1'
TABSCHEMA TABNAME CARD ACTIVE_BLOCKS
NPAGES FPAGES
---------- ---------- -------------------- --------------------
-------------------- ---------------
DB2INST1 TABLE1 250000 611
18982 19584
-- carefully deleted 154993 rows to create skew intable data
distribution. After delete we have 95007 rows
select count(*) from db2inst1.table1
1
-----------
95007
-- runstats without sampling results in good card value
RUNSTATS ON TABLE db2inst1.table1 on all columns with
distribution on all columns and sampled detailed indexes all
SELECT substr(tabschema,1,10) as tabschema, substr(tabname,1,10)
as tabname, card, active_blocks,npages, fpages from
syscat.tables where tabname='TABLE1' and tabschema='DB2INST1'
TABSCHEMA TABNAME CARD ACTIVE_BLOCKS
NPAGES FPAGES
---------- ---------- -------------------- --------------------
-------------------- ---------------
DB2INST1 TABLE1 95007 427
13269 19584
-- CARD is almost double compared to original row count when we
have both tablesample and indexsample option specified.
RUNSTATS ON TABLE db2inst1.table1 on all columns with
distribution on all columns and sampled detailed indexes all
TABLESAMPLE SYSTEM(1) REPEATABLE(4196) INDEXSAMPLE SYSTEM(1)
SELECT substr(tabschema,1,10) as tabschema, substr(tabname,1,10)
as tabname, card, active_blocks, npages, fpages from
syscat.tables where tabname='TABLE1' and tabschema='DB2INST1'
TABSCHEMA TABNAME CARD ACTIVE_BLOCKS
NPAGES FPAGES
---------- ---------- -------------------- --------------------
-------------------- ---------------
DB2INST1 TABLE1 166988 598
19136 19584 |
