Manage rebuilding of indexes...

I've been doing some speed testing of rebuilding indexes. I have been adding the PARALLEL clause to my alter index ... rebuild; statements but it has been suggested to me to also run multiple indexes rebuilds in parallel. So if I am wanting to run 4 rebuilds in parallel I could try to make 4 .SQL scripts with the appropriate commands. But what if one finishes early? Then I will be left with only 3 running. Here is a solution: 1) Create a table to hold the list of indexes you want to rebuild:
create table rebuild_indexes (
    index_name varchar2(30),
    sid number,
    start_date date,
    end_date date
);


insert into rebuild_indexes (index_name) 
    select index_name 
    from user_indexes 
    where status='UNUSABLE'
    order by index_name;

commit;
2) Open up 1..n SQLPLUS sessions and run the following anon PL/SQL block. It will pick the next index from the list and start rebuilding it. It marks the index it is building so that multiple processes will not rebuild the same index:
SET SERVEROUTPUT ON
DECLARE
  my_sid NUMBER;
  my_total NUMBER;
  my_processed NUMBER;
BEGIN

FOR c1 in (SELECT index_name FROM rebuild_indexes)
LOOP

    SELECT COUNT(*) 
      INTO my_total 
      FROM rebuild_indexes;
      
    SELECT COUNT(*)
      INTO my_processed
      FROM rebuild_indexes
     WHERE sid IS NOT NULL;

    SELECT sid
      INTO my_sid
      FROM rebuild_indexes
     WHERE index_name=c1.index_name;
      
    -- If no sid is working on this index yet...
    IF my_sid IS NULL THEN

        -- Mark as in progress
        UPDATE rebuild_indexes 
           SET sid=USERENV('sid'), 
               start_date=SYSDATE
         WHERE index_name=c1.index_name;
         COMMIT;
        
        -- Rebuild the index
        dbms_output.put_line('Rebuilding ' || c1.index_name || 
                             '     Status: ' || my_processed || 
                             ' of ' || my_total);

        execute immediate 'alter index ' || c1.index_name || ' rebuild nologging parallel 64';
        
        
       UPDATE rebuild_indexes
           SET end_date=SYSDATE
         WHERE index_name=c1.index_name;
       COMMIT;
    
    END IF;

END LOOP;

END;
/
3) Status queries to check progress:
-- Rebuild Detail - Completed
select index_name, round((end_date-start_date)*1440) as minutes
from rebuild_indexes
 where end_date is not null;

-- Number completed
select count(*) 
  from rebuild_indexes 
 where sid is not null;

-- In progress
select * 
  from rebuild_indexes 
 where start_date is not null 
   and end_date is null;
4) When you are done:
 select TO_CHAR(MIN(START_DATE), 'Month DD, YYYY HH24:MI:SS') START_DATE,
        TO_CHAR(MAX(END_DATE), 'Month DD, YYYY HH24:MI:SS') END_DATE
 from rebuild_indexes;
-- Dave

Add new comment