Re: Index Compression

Charles J. Fisher wrote:
>
> In spite of the fact that my PL/SQL skills are really rather poor, I have
> attached a procedure that I wrote that minimizes the physical size of an
> index using the COMPRESS parameter.
>
> The procedure adopts a rather brute force approach by continually
> rebuilding the index (with NOLOGGING to reduce archived log output), until
> either the number of extents occupied by the index ceases declining, or we
> reach the maximum compression factor; at this point I back off the factor
> by one if necessary, then rebuild the index with logging so it is
> recoverable. This may (will) be resource-intensive on large indexes.
>
> I am aware of the ixora utility for evaluation of the compression
> parameter (http://www.ixora.com.au/scripts/io_opt.htm), but my procedure
> has the benefit of not requiring statistics and also the ability of
> evaluating compression factors higher than 3.
>
> In order for this procedure to work, the index must occupy a number of
> extents that are sufficiently granular to gauge it's size (i.e. if the
> entire index fits in one extent after rebuild, then the procedure might
> actually increase the index physical size).
>
> Also, bear in mind that indexes "get fater" as they reach steady-state,
> and space savings on a thin index might not necessarily mean space savings
> on a fat one. I'm not certain how frequent index coalesce activity might
> impact this, but some of my users have reported strong performance gains
> after their indexes were optimized in this way (although ixora implies
> that a performance loss is possible).
>
> I thought that I would run this procedure by the group before I begin an
> extensive index-tablespace reorganization (upgrading siebel is so much fun).
> I haven't really done extensive debugging yet.
>
> SQL> set serveroutput on size 1000000
> SQL> execute indexopt('SIEBEL_AMP', 'XIE1NEXT_YEAR_CONTRACT_VALUES', 'tablespace index128k pctfree 3');
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild nologging
> nocompress tablespace index128k pctfree 3
> UNCOMPRESSED EXTENT COUNT:33
> -------------------------------
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild nologging compress
> 1 tablespace index128k pctfree 3
> COMPRESS FACTOR 1:27
> -------------------------------
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild nologging compress
> 2 tablespace index128k pctfree 3
> COMPRESS FACTOR 2:23
> -------------------------------
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild nologging compress
> 3 tablespace index128k pctfree 3
> COMPRESS FACTOR 3:18
> -------------------------------
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild nologging compress
> 4 tablespace index128k pctfree 3
> COMPRESS FACTOR 4:16
> -------------------------------
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild nologging compress
> 5 tablespace index128k pctfree 3
> COMPRESS FACTOR 5:38
> -------------------------------
> REVERTING TO COMPRESS FACTOR 4
> alter index SIEBEL_AMP.XIE1NEXT_YEAR_CONTRACT_VALUES rebuild compress 4
> tablespace index128k pctfree 3
>
> PL/SQL procedure successfully completed.
>
> SQL> execute indexopt('WEBAPPL', 'XIE3PDF_SOFT_BLOCK_CACHE', 'tablespace index128k pctfree 3');
> alter index WEBAPPL.XIE3PDF_SOFT_BLOCK_CACHE rebuild nologging nocompress
> tablespace index128k pctfree 3
> UNCOMPRESSED EXTENT COUNT:10
> -------------------------------
> alter index WEBAPPL.XIE3PDF_SOFT_BLOCK_CACHE rebuild nologging compress 1
> tablespace index128k pctfree 3
> COMPRESS FACTOR 1:7
> -------------------------------
> alter index WEBAPPL.XIE3PDF_SOFT_BLOCK_CACHE rebuild nologging compress 2
> tablespace index128k pctfree 3
> -------------------------------
> MAX COMPRESS FACTOR REACHED
> alter index WEBAPPL.XIE3PDF_SOFT_BLOCK_CACHE rebuild
>
> PL/SQL procedure successfully completed.
>
> create or replace procedure indexopt
> (
> i_owner in varchar2,
> i_index_name in varchar2,
> parameters in varchar2
> ) IS
> segcount number;
> oldsegcount number;
> factor number := 1;
> begin
> dbms_output.put_line('alter index '|| i_owner || '.' || i_index_name ||
> ' rebuild nologging nocompress ' || parameters);
>
> execute immediate('alter index '|| i_owner || '.' || i_index_name ||
> ' rebuild nologging nocompress ' || parameters);
>
> select count(*) into oldsegcount from dba_extents
> where owner = upper(i_owner)
> and segment_name = UPPER(i_index_name)
> and segment_type = 'INDEX';
>
> dbms_output.put_line('UNCOMPRESSED EXTENT COUNT:' || oldsegcount);
>
> dbms_output.put_line('-------------------------------');
>
> loop
> dbms_output.put_line('alter index '|| i_owner || '.' ||
> i_index_name || ' rebuild nologging compress '
> || factor || ' ' || parameters);
>
> begin
> execute immediate('alter index '|| i_owner || '.' ||
> i_index_name || ' rebuild nologging compress '
> || factor || ' ' || parameters);
> exception
> when others then
>
> dbms_output.put_line('-------------------------------');
>
> dbms_output.put_line('MAX COMPRESS FACTOR REACHED');
>
> dbms_output.put_line('alter index ' || i_owner || '.' ||
> i_index_name || ' rebuild');
>
> execute immediate('alter index ' || i_owner || '.' ||
> i_index_name || ' rebuild');
>
> return;
> end;
>
> select count(*) into segcount from dba_extents
> where owner = upper(i_owner)
> and segment_name = upper(i_index_name)
> and segment_type = 'INDEX';
>
> dbms_output.put_line('COMPRESS FACTOR ' || factor|| ':' ||
> segcount);
>
> dbms_output.put_line('-------------------------------');
>
> exit when segcount >= oldsegcount;
>
> oldsegcount := segcount;
>
> factor := factor + 1;
> end loop;
>
> factor := factor - 1;
>
> dbms_output.put_line('REVERTING TO COMPRESS FACTOR ' || factor);
>
> if factor = 0
> then
> dbms_output.put_line('alter index '|| i_owner || '.' ||
> i_index_name || ' rebuild nocompress ' || parameters);
>
> execute immediate('alter index '|| i_owner || '.' ||
> i_index_name || ' rebuild nocompress ' || parameters);
> else
> dbms_output.put_line('alter index '|| i_owner || '.' ||
> i_index_name || ' rebuild compress ' || factor
> || ' ' || parameters);
>
> execute immediate('alter index '|| i_owner || '.' ||
> i_index_name || ' rebuild compress ' || factor
> || ' ' || parameters);
> end if;
> end;
> /
>
> ---------------------------------------------------------------------------
> / Charles J. Fisher | "Those who do not understand UNIX /
> / cfisher_at_rhadmin.org | are condemned to reinvent it, /
> / http://rhadmin.org | poorly." -- Henry Spencer /
> ---------------------------------------------------------------------------

how about 'analyze index ... validate structure'

and then look at the OPT_... columns to determine compression level.

Available from (I think) 9.0 onwards...

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------