Oracle Index Rebuilds

While there may be no significant performance benefit by rebuilding indexes, the activity itself is usually low intensive and free in an on-premises environment. Therefore, a scheduled index maintenance job can only benefit the database. Usually, indexes on tables with a high number of deletes and updates are candidates for an index rebuild job. Rebuilding these indexes may lead to improved SQL query performance.

The first step to determining if an index rebuild is required is to analyse the index.

ANALYZE INDEX SCHEMA.INDEX_NAME VALIDATE STRUCTURE;

This command will gather information about the index and create a row in the sys.index_stats table which only holds one row.

Valid indexes will be rebuilt if either of the conditions are met:

• if fragmentation is 20% or more
• if fragmentation meets the value specified in the parameter p_frag_pct
• the index depth is more than 3 levels

CREATE OR REPLACE PROCEDURE INDEX_MAINTENANCE(p_schema in varchar2, p_frag_pct in number)
as 
    v_schema varchar2(128 BYTE);
    v_sql varchar(1000);
    v_rebuild_sql varchar(1000);
    v_deleted_rows_pct number(5, 0);
    v_frag_pct number(5, 0);
    v_height number(10, 0);
    
    cursor cur_indexes is
        SELECT table_name, index_name FROM all_indexes 
		WHERE UPPER(owner) = v_schema
		and status = 'VALID';    
begin
    v_schema := UPPER(p_schema);
    v_frag_pct := round(NVL(P_frag_pct, 20), 0);
    
    FOR cur_indexes_rec in cur_indexes
    loop
        v_sql := 'ANALYZE INDEX ' || v_schema || '.' || cur_indexes_rec.index_name || ' VALIDATE STRUCTURE';
        dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
        
        begin
            select round((DEL_LF_ROWS/LF_ROWS)*100, 0), HEIGHT 
            into v_deleted_rows_pct, v_height
            from sys.index_stats;
        exception when others then
            v_deleted_rows_pct := 0;
            v_height := 0;
        end;
        
        if v_deleted_rows_pct >= v_frag_pct or v_height >=4 then
            dbms_output.put_line('Rebuilding index ' || v_schema || '.' || cur_indexes_rec.index_name || '...');
            v_rebuild_sql := 'ALTER INDEX ' || v_schema || '.' || cur_indexes_rec.index_name || ' REBUILD';
            dbms_output.put_line(v_rebuild_sql);
            EXECUTE IMMEDIATE v_rebuild_sql;
        end if;
        
    end loop;
end;

Note: the schema is required but the fragmentation percent is not.
Example:
To rebuild all the qualifying indexes for the SCOTT schema:

INDEX_MAINTENANCE('SCOTT');

To rebuild all the qualifying indexes with fragmentation of 15 percent for the SCOTT schema:

INDEX_MAINTENANCE('SCOTT', 15);