Re: Mythical space savings of IOTs

  • From: Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx>
  • To: bcoulam@xxxxxxxxx
  • Date: Fri, 8 Jul 2005 18:32:06 -0400

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;
--
//www.freelists.org/webpage/oracle-l

Other related posts: