cancel
Showing results for 
Search instead for 
Did you mean: 

Index on FAGL_SPLINFO_VAL is larger than the table

Former Member
0 Kudos

Hi

The index on FAGL_SPLINFO_VAL is about 30GB larger than the table, however the index contains 2 columns less than the table. We are running DB2 V9.1, however have only just migrated from DB2 8.2. Can anyone explain why this index would be so much larger than the table - the table is 167GB and the index is 200GB.

Thanks

Leigh White

SAP Basis Administrator

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi

this is possible, if these 2 columns are small. If it is a primary or unique index, each row in the table requires one entry in the leaf pages of the index. In addition to the content of the index column the RID (record identifier) need to be stored per row. This are 4 bytes without large RIDs and 6 bytes with larger RIDs.

In addition to the leaf pages, you need to take then the non-leaf pages of a b-tree into account.

To be sure, that the index does not contain pseudo deleted entries, you should run an REORG INDEX ... CLEANUP ONLY reorg. The pseudo deleted keys may increase the "virtual" size of the index.

Regards, Jens

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

is this a compressed table?

Best regards,

Joachim Müller

Former Member
0 Kudos

Hi

The table is definitely not using row compression. We are using the 6.40 kernel and have not implemented value compression either.

Thanks

Leigh

Former Member
0 Kudos

Hi Francois,

than that happen what Jens wrote. We have seen this when we use compression, table partitoning and mdc on a table.

Best regards,

Joachim Müller