Re: Mythical space savings of IOTs

Hi Bill,
hope this help,
If you are fighting to reduce your index size. I have 3 advices useful for me.

I think you can get some small reduction of your index if you use a 16k tablespace, I did, and it reduce very few. But finally it reduced.

I set pctfree to 0, because I don't do updates

I see you use the OPT_CMPR_COUNT, critical when compressing indexes. as the parameter to the COMPRESS

EXECUTE IMMEDIATE(' ANALYZE INDEX '||cOwner||'.'||cIndex||' VALIDATE STRUCTURE ');
 SELECT OPT_CMPR_COUNT INTO nCompression FROM INDEX_STATS WHERE NAME = cIndex;  IF NOT nCompression = 0 THEN
  cReturn := 'ALTER INDEX '||cOwner||'.'||cIndex||' REBUILD COMPRESS '||nCompression||' PCTFREE 3 NOLOGGING;';  ELSE
  cReturn := 'ALTER INDEX '||cOwner||'.'||cIndex||' REBUILD PCTFREE 3 NOLOGGING;';
 END IF;

--
http://www.freelists.org/webpage/oracle-l