Summary
The key concept in large databases is making the job done fast. The faster the better and of cource less downtime for the application. Sometimes rebuilding an index may take hours and more to complete. If you have multiple CPUs and
parallel processing is enabled then you can do it much more faster than the traditional serial way on the single CPU system.
So the first step is making the parallel rebuild and second setting the index degree back to 1.
alter index INDEX_NAME rebuild parallel 8 nologging tablespace TABLESPACE_NAME;
alter index NDEX_NAME noparallel;
Some little tips:
While making the
parallel rebuild usually you cannot estimate the total remaining time of the procedure. You can know the elapsed time only
by monitoring parallel sessions
Doing it
without parallelism it has the small advantage of knowing the exact remaing time by
monitoring long operations
Another tip:
Another usefull tip for rebuilding indexes very fast, but in the special case where the table is
partitioned, is to use the package
DBMS_PCLXUTIL
If you are creating or rebuilding
local indexes on very large tables then you can consider the
DBMS_PCLXUTIL package. With this package you can parallelize index building against multiple partitions insted of doing partition local index one-by-one.
Pclxutil just automates the rebuilding of the index using the job queues. An example of calling the procedure
BEGIN
DBMS_PCLXUTIL.BUILD_PART_INDEX(10,8,'TABLE_NAME','INDEX_NAME',TRUE);
END;
/
dbms_pclxutil.build_part_index (
jobs_per_batch IN NUMBER DEFAULT 1,
procs_per_job IN NUMBER DEFAULT 1,
tab_name IN VARCHAR2 DEFAULT NULL,
idx_name IN VARCHAR2 DEFAULT NULL,
force_opt IN BOOLEAN DEFAULT FALSE);
jobs_per_batch = number of partitions
procs_per_batch = degree <= max_slaves
force_opt = If true forces rebuild of all indexes. If false rebuild only of index marked UNUSABLE.
