Re: How's this for space saving:

Hi Jonathan,
I had compressed all my indexes, but my mistake was not to check how
much was the improvement. I always set COMPRESS (maximum compression).
One day I noted when it was compressed, it was bigger than uncompressed.
Tom Kyte was who suggested to use it.
My database is about 3GB, once I re-compressed my index, using the
suggested compression I save 1GB of space.
So NEVER COMPRESS WITHOUT ANALYZING THE INDEX.

I created a function to get it.
I didt some test to get the correct idea, about the relation between
the order of the columns and the compression and size o the index. if
you are interested.


CREATE OR REPLACE FUNCTION SYS.DB_UTL_REBUILD_INDEX(cOwner 
VARCHAR2,cIndex VARCHAR2)
RETURN VARCHAR2
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 cReturn      VARCHAR2(1000); 
 nCompression NUMBER;
BEGIN
 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;
 RETURN cReturn;
EXCEPTION WHEN OTHERS THEN
 RETURN cOwner||'.'||cIndex||'-ERR:'||SQLERRM;
END;
/


    hcf_fecha                       ASC,    hcf_codcli                
     ASC,    hcf_ctacorr                     ASC,    hcf_status       
              ASC,    hcf_dbcuo                       ASC,   
hcf_crcuo                       ASC,    hcf_iva                       
 ASC,    hcf_monto_iva                   ASC    No Compressed      
243.4375Optimum Compression 1Compressed          205.78125
    hcf_monto_iva                   ASC,    hcf_fecha                 
     ASC,    hcf_codcli                      ASC,    hcf_ctacorr      
              ASC,    hcf_status                      ASC,   
hcf_dbcuo                       ASC,    hcf_crcuo                     
 ASC,    hcf_iva                         ASC    No Compressed      
243.359375Optimum Compression 1Compressed          226.71875
    hcf_iva                         ASC,    hcf_monto_iva             
     ASC,    hcf_fecha                       ASC,    hcf_codcli       
              ASC,    hcf_ctacorr                     ASC,   
hcf_status                      ASC,    hcf_dbcuo                     
 ASC,    hcf_crcuo                       ASC    No Compressed      
243.4375Optimum Compression 1Compressed          226.25
    hcf_crcuo                       ASC,    hcf_iva                   
     ASC,    hcf_monto_iva                   ASC,    hcf_fecha        
              ASC,    hcf_codcli                      ASC,   
hcf_ctacorr                     ASC,    hcf_status                    
 ASC,    hcf_dbcuo                       ASC    No Compressed      
243.4375Optimum Compression 3Compressed          214.453125
    hcf_dbcuo                       ASC,    hcf_crcuo                 
     ASC,    hcf_iva                         ASC,    hcf_monto_iva    
              ASC,    hcf_fecha                       ASC,   
hcf_codcli                      ASC,    hcf_ctacorr                   
 ASC,    hcf_status                      ASC    No Compressed      
243.515625Optimum Compression 4Compressed          200.9375
    hcf_dbcuo                       ASC,    hcf_crcuo                 
     ASC,    hcf_monto_iva                   ASC,    hcf_iva          
              ASC,    hcf_codcli                      ASC,   
hcf_fecha                       ASC,    hcf_status                    
 ASC,    hcf_ctacorr                     ASC    No Compressed      
243.59375Optimum Compression 5Compressed          178.59375
--
http://www.freelists.org/webpage/oracle-l

Other related posts: