Index on FAGL_SPLINFO_VAL is larger than the table
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.
SAP Basis Administrator
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.