Could you please explain me what need to do if our Index is over-allocated. How to make space available for index spaces.
Thanks in advance!!!
We have changed PRIQTY -1 and SECQTY -1 for indexes but still not able to save more space. Could you please let me know if any other way to save space.
In Reply to Philip Sevetson:
· Already considered index compression
· Have PADDED NO for any variable-length index fields
· Minimize values for PCTFREE and FREEPAGE minimized
· Aren’t putting up huge wasteful secondary extents
You’ve covered about everything.
Apologies in advance for any spelling errors. My fingers get out of sync and I leave out words too. LOL
Yes good ideas. I tune some applications by reducing PCTFREE and FREEPAGE (when a lot more than necessary). This can make good Getpage reductions for scanners. Could be as little as PCTFREE 5 FREEPAGE 0 for indexes. Of course reorg then needed.
Padded NO can be very important for VARCHAR, but unfortunately does not help some customers that used quite long CHAR columns.
Some customers could have too many columns in an index that are not needed, especially if a well clustered index, then Index Only access is not needed (often). i.e. Some indexes are much larger than the compressed table.
If your index always has more than one Equals matching column, make sure that the leading column is not a very long one with low cardinality (if possible), since that severely limits the non-leaf entry key truncation, not saving that much space I guess (digressing), but keeping the number of levels down. More important for very long keys.
Index space allocation is not normally a big problem, unless site has many different envirorment copies of same set of tables. Some sites allocated in Cylinder multiples for large numbers of trivial tiny indexes (and tables) wasting a lot of space (when space was short).
I am not at all keen on Index Compression. I once asked IBM if it had any performance advantages, since I could not see the point in it, and basically got a big NO. It is true that it could save some space if you are really desperate. Last resort. For me, somewhat of a solution looking for a problem. Would only consider it for gi-normous indexes when really cannot buy storage. I don't recommend sites to use Index Compression just because it is there. Larger page size without compression can be more useful to cut index levels and scan Getpages.
DB2 Application Performance Specialist
CPT Global Ltd