| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Index Compression
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
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
PL/SQL procedure successfully completed.
create or replace procedure indexopt
(
i_owner in varchar2, i_index_name in varchar2, parameters in varchar2
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;
/ Charles J. Fisher | "Those who do not understand UNIX / / cfisher_at_rhadmin.org | are condemned to reinvent it, / / http://rhadmin.org | poorly." -- Henry Spencer /Received on Wed Nov 16 2005 - 13:13:52 CST
---------------------------------------------------------------------------
 |
 |