Fast index rebuild


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. a
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s