How to check index size

[Luiz da Silva]

ello felows,

I'm getting different values when I check indexes sizes in SYSPROC.ADMIN_GET_TAB_INFO and SYSPROC.ADMIN_GET_INDEX_INFO:

Please, let me know if I'm not seeing anything obvious...

Let me first describe my environment:

DB2 Linux 10.5 fp5 using DPF ( 16 nodes, using 0 for the catalog )

CREATE TABLE "MYSCHEMA "."MYTABLE" (
"RPT_SUMM_KEY_ID" BIGINT NOT NULL ,
"ARR_ID" BIGINT NOT NULL ,
"RPT_UNIT_CDE" CHAR(5 OCTETS) ,
"ACCT_CDE" CHAR(10 OCTETS) ,
"CCT_CDE" CHAR(10 OCTETS) ,
"SUMM_PROD_CDE" CHAR(6 OCTETS) ,
"DETL_PROD_CDE" CHAR(6 OCTETS) ,
"SUMM_CUST_TYPE_CDE" CHAR(10 OCTETS) ,
"DETL_CUST_TYPE_CDE" CHAR(10 OCTETS) ,
"GAAP_TYPE_CDE" CHAR(4 OCTETS) ,
"CHTFLD_SRCE_SYS_CDE" CHAR(10 OCTETS) ,
"AFFL_UNIT_CDE" CHAR(5 OCTETS) ,
"INTF_SRCE_SYS_CDE" CHAR(3 OCTETS) ,
"INTF_SBLR_CDE" CHAR(2 OCTETS) ,
"CUST_PORTF_CDE" CHAR(4 OCTETS) ,
"ALT_ACCT_CDE" CHAR(10 OCTETS) ,
"REFER_CCT_CDE" CHAR(10 OCTETS) ,
"REFER_ACCT_NUM" CHAR(10 OCTETS) ,
"BUS_EVENT_CDE" CHAR(8 OCTETS) ,
"ARR_CDE" CHAR(1 OCTETS) ,
"OVERLAY_CDE" CHAR(1 OCTETS) ,
"INTF_DESC_CDE" CHAR(3 OCTETS) ,
"ENTRY_METHOD_CDE" CHAR(1 OCTETS) )
COMPRESS YES ADAPTIVE
DISTRIBUTE BY HASH("RPT_SUMM_KEY_ID")
IN "DATA_ALL" INDEX IN "INDEX_ALL"
ORGANIZE BY ROW;

CREATE UNIQUE INDEX "MYSCHEMA "."INDEX_00" ON "MYSCHEMA "."MYTABLE"
("RPT_SUMM_KEY_ID" ASC)
COMPRESS YES
INCLUDE NULL KEYS ALLOW REVERSE SCANS;

CREATE INDEX "MYSCHEMA "."INDEX_01" ON "MYSCHEMA "."MYTABLE"
("ACCT_CDE" ASC,
"RPT_UNIT_CDE" ASC,
"CHTFLD_SRCE_SYS_CDE" ASC,
"SUMM_PROD_CDE" ASC,
"SUMM_CUST_TYPE_CDE" ASC,
"GAAP_TYPE_CDE" ASC,
"ARR_ID" ASC,
"RPT_SUMM_KEY_ID" ASC,
"CCT_CDE" ASC)
COMPRESS YES
INCLUDE NULL KEYS ALLOW REVERSE SCANS;

This is the result when I check the indexes' sizes through ADMIN_GET_TAB_INFO:

SELECT
SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,
SUBSTR(TABNAME,1,20) AS TABNAME,
DBPARTITIONNUM,
DATA_PARTITION_ID,
INDEX_OBJECT_P_SIZE AS INDEXSIZE
FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'MYSCHEMA', 'MYTABLE' ) )
ORDER BY
TABSCHEMA,
TABNAME,
DBPARTITIONNUM

TABSCHEMA TABNAME DBPARTITIONNUM DATA_PARTITION_ID INDEXSIZE
---------- -------------------- -------------- ----------------- --------------------
MYSCHEMA MYTABLE 1 0 406784
MYSCHEMA MYTABLE 2 0 405248
MYSCHEMA MYTABLE 3 0 405248
MYSCHEMA MYTABLE 4 0 405504
MYSCHEMA MYTABLE 5 0 404992
MYSCHEMA MYTABLE 6 0 405248
MYSCHEMA MYTABLE 7 0 405248
MYSCHEMA MYTABLE 8 0 405248
MYSCHEMA MYTABLE 9 0 404992
MYSCHEMA MYTABLE 10 0 405248
MYSCHEMA MYTABLE 11 0 405248
MYSCHEMA MYTABLE 12 0 404992
MYSCHEMA MYTABLE 13 0 405248
MYSCHEMA MYTABLE 14 0 404992
MYSCHEMA MYTABLE 15 0 404992

15 record(s) selected.

And this is the through the ADMIN_GET_INDEX_INFO:

SELECT
SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,
SUBSTR(TABNAME,1,20) AS TABNAME,
SUBSTR(INDNAME,1,20) AS INDEX_NAME,
DBPARTITIONNUM,
INDEX_OBJECT_P_SIZE AS INDEXSIZE,
CASE INDEX_PARTITIONING --- values below are documented on Knowledge Center
WHEN 'N' THEN 'NONPARTITIONED INDEX'
WHEN 'P' THEN 'PARTITIONED INDEX'
WHEN ' ' THEN 'INDEX IS NOT ON A PARTITIONED TABLE'
END AS INDEX_PARTITIONING,
DATAPARTITIONID AS RANGE_PARTITION_ID
FROM TABLE( SYSPROC.ADMIN_GET_INDEX_INFO( 'T', 'MYSCHEMA', 'MYTABLE' ) )
ORDER BY
TABSCHEMA,
TABNAME,
INDEX_NAME,
DBPARTITIONNUM

TABSCHEMA TABNAME INDEX_NAME DBPARTITIONNUM INDEXSIZE INDEX_PARTITIONING RANGE_PARTITION_ID
---------- -------------------- -------------------- -------------- -------------------- ----------------------------------- ------------------
MYSCHEMA MYTABLE INDEX_00 1 406784 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 2 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 3 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 4 405504 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 5 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 6 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 7 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 8 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 9 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 10 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 11 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 12 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 13 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 14 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_00 15 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 1 406784 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 2 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 3 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 4 405504 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 5 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 6 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 7 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 8 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 9 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 10 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 11 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 12 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 13 405248 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 14 404992 INDEX IS NOT ON A PARTITIONED TABLE 0
MYSCHEMA MYTABLE INDEX_01 15 404992 INDEX IS NOT ON A PARTITIONED TABLE 0

30 record(s) selected.

Here I'm just grouping to summarize:

SELECT
SUBSTR(TABSCHEMA,1,10) AS TABSCHEMA,
SUBSTR(TABNAME,1,20) AS TABNAME,
SUBSTR(INDNAME,1,30) AS INDEX_NAME,
SUM(INDEX_OBJECT_P_SIZE) AS INDEXSIZE
FROM TABLE( SYSPROC.ADMIN_GET_INDEX_INFO( 'T', 'MYSCHEMA', 'MYTABLE' ) )
GROUP BY TABSCHEMA, TABNAME, INDNAME
ORDER BY TABSCHEMA, TABNAME, INDNAME

TABSCHEMA TABNAME INDEX_NAME INDEXSIZE
---------- -------------------- ------------------------------ --------------------
MYSCHEMA MYTABLE INDEX_00 6073856
MYSCHEMA MYTABLE INDEX_01 6073856

2 record(s) selected.

Reading the documentation, I see that the column INDEX_OBJECT_P_SIZE in ADMIN_GET_TAB_INFO is supposed to be lower than in ADMIN_GET_INDEX_INFO.
I assume this is because of this:

"INDEX_OBJECT_P_SIZE: For partitioned indexes on partitioned tables, this is the physical size of the index object containing index partitions for the data partition identified by DATA_PARTITION_ID.
This value does not take into account nonpartitioned indexes on partitioned tables.
For information about both partitioned and nonpartitioned indexes, you can use the ADMIN_GET_INDEX_INFO function."

That's the reason I checked ADMIN_GET_INDEX_INFO instead, but I still don't understand 2 points:

1) If my 2 indexes are not range-partitioned, why do I see some values in INDEX_OBJECT_P_SIZE at ADMIN_GET_TAB_INFO table function? Precisely, half of what I see in ADMIN_GET_INDEX_INFO.
2) Checking ADMIN_GET_INDEX_INFO, why both indexes are the exactly same size if they have very distinct definitions?

Reorg/Runstats were performed on table and both indexes.

Am I checking/interpreting it wrongly?

Thanks!

Luiz da Silva

[Ian]

On Monday, September 21, 2015 at 1:11:09 PM UTC-7, Luiz da Silva wrote:

> Reading the documentation, I see that the column INDEX_OBJECT_P_SIZE
> in ADMIN_GET_TAB_INFO is supposed to be lower than in
> ADMIN_GET_INDEX_INFO. I assume this is because of this:
>
> "INDEX_OBJECT_P_SIZE: For partitioned indexes on partitioned tables,
> this is the physical size of the index object containing index
> partitions for the data partition identified by
> DATA_PARTITION_ID. This value does not take into account
> nonpartitioned indexes on partitioned tables. For information about
> both partitioned and nonpartitioned indexes, you can use the
> ADMIN_GET_INDEX_INFO function."
>
> That's the reason I checked ADMIN_GET_INDEX_INFO instead, but I still
> don't understand 2 points:
>
> 1) If my 2 indexes are not range-partitioned, why do I see some values
> in INDEX_OBJECT_P_SIZE at ADMIN_GET_TAB_INFO table function?
> Precisely, half of what I see in ADMIN_GET_INDEX_INFO.
>
> 2) Checking ADMIN_GET_INDEX_INFO, why both indexes are the exactly
> same size if they have very distinct definitions?

I'm not completely sure about this, but you'll notice that the values
you get for INDEXSIZE from ADMIN_GET_INDEX_INFO for each index are
identical (on each database partition), and you may also notice that
these values are the same as what gets reported from ADMIN_GET_TAB_INFO.

Reading the documentation for ADMIN_GET_TAB_INFO, you may notice the
following for INDEX_OBJECT_L_SIZE:

"this is the amount of disk space logically allocated
for *all indexes* defined on the table."

(emphasis added), It mentions that the size is for all indexes. The
wording is similar for INDEX_OBJECT_P_SIZE.

So it would seem that, while ADMIN_GET_INDEX_INFO can provide
information about each index on a table (compression state, whether
the index is partitioned, etc, the index size statistics are
not provided on a per-index basis. So there's a bit of a
normalization issue here :-)

I would suggest that you rely on ADMIN_GET_TAB_INFO for size
information, not ADMIN_GET_INDEX_INFO.

[Mohan Kumar]

Hi admin can u please add my mail id askto...@gmail.com

[Jerry Stuckle]

On 2/10/2016 1:16 AM, Mohan Kumar wrote:
>
> Hi admin can u please add my mail id askto...@gmail.com
>

This is usenet, not a mail list. Google Groups is just a poor interface
to it.

If you want to see new posts, you should get a usenet reader and access
through a usenet server.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================