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