|Oracle8i Supplied Packages Reference
DBMS_PCLXUTIL package provides intra-partition parallelism for creating partition-wise local indexes.
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.
DBMS_PCLXUTIL package can be used during the following DBA tasks:
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
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.
For composite partitions, the procedure automatically builds local indices for all subpartitions of the composite table.
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):
Rebuild all (sub)partitions using
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).
DBMS_PCLXUTIL uses the
DBMS_JOB package, you must be aware of the following limitations pertaining to
job_queue_intervalinitalization parameters. Clearly, if the job processes are not started before calling
BUILD_PART_INDEX(), then the package will not function properly. The background processes are specified by the following
job_queue_processes=n #the number of background processes = n job_queue_interval=m #the processes wake-up every m seconds
Therefore, the upper limit on
job_queue_processes). The default value for
jobs_per_batch is 1; i.e., indexes will be built one partition at a time.
DBMS_JOBlimitation), making it impossible to give interactive feedback to the user. This package simply prints a failure message, removes unfinished jobs from the queue, and requests the user to take a look at the
DBMS_PCLXUTIL contains just one procedure:
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);
Number of local indexes to be built concurrently (1 <=
Number of parallel query slaves to be utilized per local index build (1 <=
Name of the partitioned table (an exception is raised if the table does not exist or not partitioned).
Name given to the local index (an exception is raised if a local index is not created on the table
Suppose a table
PROJECT is created with two partitions
PROJ002, along with a local index
A call to the procedure
TRUE) produces the following output:
SVRMGR> EXECUTE dbms_pclxutil.build_part_index(2,4,'PROJECT','IDX',TRUE); Statement processed. INFO: Job #21 created for partition PROJ002 with 4 slaves INFO: Job #22 created for partition PROJ001 with 4 slaves SVRMGR>