Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)

Part Number A76936-01





Go to previous page Go to next page


The DBMS_PCLXUTIL package provides intra-partition parallelism for creating partition-wise local indexes.

See Also:

There are several rules concerning partitions and indexes. For more information, see Oracle8i Concepts and Oracle8i Administrator's Guide

DBMS_PCLXUTIL circumvents the limitation that, for local index creation, the degree of parallelism is restricted to the number of partitions as only one slave process per partition is utilized.

DBMS_PCLXUTIL uses the DBMS_JOB package to provide a greater degree of parallelism for creating a local index for a partitioned table. This is achieved by asynchronous inter-partition parallelism using the background processes (with DBMS_JOB), in combination with intra-partition parallelism using the parallel query slave processes.

DBMS_PCLXUTIL works with both range and range-hash composite partitioning.


For range partitioning, the minimum compatibility mode is 8.0; for range-hash composite partitioning, the minimum compatibility mode is 8i.  


The DBMS_PCLXUTIL package can be used during the following DBA tasks:

  1. Local index creation

    The procedure BUILD_PART_INDEX assumes that the dictionary information for the local index already exists. This can be done by issuing the create index SQL command with the UNUSABLE option.

    CREATE INDEX <idx_name> on <tab_name>(...) local(...) unusable; 

    This causes the dictionary entries to be created without "building" the index itself, the time consuming part of creating an index. Now, invoking the procedure BUILD_PART_INDEX causes a concurrent build of local indexes with the specified degree of parallelism.

    EXECUTE dbms_pclxutil.build_part_index(4,4,<tab_name>,<idx_name>,FALSE); 

    For composite partitions, the procedure automatically builds local indices for all subpartitions of the composite table.

  2. Local index maintenance

    By marking desired partitions usable or unusable, the BUILD_PART_INDEX procedure also enables selective rebuilding of local indexes. The force_opt parameter provides a way to override this and build local indexes for all partitions.

    ALTER INDEX <idx_name> local(...) unusable; 

    Rebuild only the desired (sub)partitions (that are marked unusable):

    EXECUTE dbms_pclxutil.build_part_index(4,4,<tab_name>,<idx_name>,FALSE); 

    Rebuild all (sub)partitions using force_opt = TRUE:

    EXECUTE dbms_pclxutil.build_part_index(4,4,<tab_name>,<idx_name>,TRUE); 

    A progress report is produced, and the output appears on screen when the program is ended (because the DBMS_OUTPUT package writes messages to a buffer first, and flushes the buffer to the screen only upon termination of the program).


Because DBMS_PCLXUTIL uses the DBMS_JOB package, you must be aware of the following limitations pertaining to DBMS_JOB:

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.