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