Index Rebuild Scripts

Some Important Links:

* http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/
* http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/
* http://asktom.oracle.com/pls/asktom/f?p=100:11:3318814234016508::::P11_QUESTION_ID:2913600659112
* http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35336203098853

Analyze INDEX <index name> validate structure;

this is session specific command. To check stats from index_stats view, you need to execute query from the same session where the above command is fired

index stats contains one row at a time. It contains the outcome of the last validate structure. it goes away when you exit your session.

To check for index fragmentation

From Allappsdba blog:

 -- +------------------------------------------------------------------------------+
-- | PURPOSE  : To check for index fragmentation. As a rule of thumb if 10-15%  |
-- |            of the table data changes, then you should consider rebuilding the index  |
-- +-------------------------------------------------------------------------------+

ANALYZE INDEX &&index_name VALIDATE STRUCTURE; 

COL name         HEADING 'Index Name'          FORMAT a30 
COL del_lf_rows  HEADING 'Deleted|Leaf Rows'   FORMAT 99999999 
COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999 
COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999 

SELECT
    name
  , del_lf_rows
  , lf_rows - del_lf_rows lf_rows_used
  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness 
FROM   index_stats
/

prompt 
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt 

undefine index_name

============================================================================

From ORAFAQ

============================================================================

/* ************************************************************* */
/* Index Fragmentation Status (idsfrag.sql):                     */
/*                                                               */
/* This script will report the index fragmentation status        */
/*   for a schema.                                               */
/*                                                               */
/* Note: - Do not run this scrip during peak processing hours!!! */
/*       - This script will fail for locked tables.              */
/*                                                               */
/* ************************************************************* */

prompt -- Drop and create temporary table to hold stats...
drop table my_index_stats
/
create table my_index_stats (
        index_name              varchar2(30),
        height                  number(8),
        del_lf_rows             number(8),
        distinct_keys           number(8),
        rows_per_key            number(10,2),
        blks_gets_per_access    number(10,2)
)
/

prompt -- Save script which we will later use to populate the above table...
insert into my_index_stats
select NAME, HEIGHT, DEL_LF_ROWS, DISTINCT_KEYS, ROWS_PER_KEY,
       BLKS_GETS_PER_ACCESS
from   INDEX_STATS
-- Note this open line...

save /tmp/save_index_stats.sql replace

prompt
prompt -- Spool listing with validate commands...
col line1 newline
col line2 newline
col line3 newline
set pagesize 0
set echo off
set termout off
set trimspool on
set feed off
set linesize 200
spool /tmp/validate_indexes.sql
select 'prompt Process table '||owner||'.'||table_name||
       ', index '||index_name||'...' line1,
       'validate index '||owner||'.'||index_name||';' line2,
       '@/tmp/save_index_stats.sql' line3
from   sys.dba_indexes where owner = 'SCOTT'
order  by table_name, index_name
/
spool off
set termout on
set feed on

prompt
prompt -- Run script to validate indexes...
@/tmp/validate_indexes.sql

prompt -- Print nice report...
set pagesize 50000
set trimspool on
col height format 99999
col del_rows format 9999999
col rows/key format 999999.9
spool idxfrag.lst
select INDEX_NAME, HEIGHT, DEL_LF_ROWS "DEL_ROWS", DISTINCT_KEYS "DIST KEYS",
       ROWS_PER_KEY "ROWS/KEY",
       BLKS_GETS_PER_ACCESS "BLKS/ACCESS"
from   MY_INDEX_STATS
/
spool off

-- Cleanup
drop table my_index_stats
/
! rm /tmp/validate_indexes.sql
! rm /tmp/save_index_stats.sql

prompt
prompt Report is in idxfrag.lst
prompt Done!!!

=============================================================================

Index Fragmentation / Rebuild





How to find index is fragmented?

First analyze index 



SQL>analyze index INDEX_NAME validate structure;
Then query INDEX_STATS view
1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.
2. If height is 4 then index should be rebuild.
3. If lf_rows is lower than lf_blks then index should be rebuild.



SQL> column status format a10
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status
----------
21.83%

How to remove index fragmentation?
There are two way to remove fragmentation.
1. index coalesce
2. index rebuild

What is difference between coalesce and rebuild please go through below link for more details
http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548



SQL> alter index IDX_OBJ_ID coalesce;

SQL> alter index IDX_OBJ_ID rebuild;

SQL> alter index IDX_OBJ_ID rebuild online;

Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.



SQL> analyze index idx_obj_id validate structure;

Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status
-------
40.85%

SQL> alter index IDX_OBJ_ID rebuild online;

Index altered.

SQL> analyze index idx_obj_id validate structure;

Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

status
--------
0%

Note: Index rebuild when index is fragmented or it is needed, otherwise index rebuilding is myth for improve performance.
=============================================================================

select
‘exec analyzedb.reorg_a_table4(’||””||rtrim(t.table_owner)||””||’,’||””||
rtrim(t.table_name)||””||’);’,
t.table_owner||’.’||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,’999999.99′) per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from analyzedb.table_modifications t,
all_tables a
where t.timestamp >= to_date(’&from_date’,’dd-mon-yyyy’) and
t.table_owner = a.owner and t.table_owner not in (’SYS’,’SYSTEM’) and
t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;

=====================================================================