139 DBMS_PCLXUTIL
The DBMS_PCLXUTIL
package provides intra-partition parallelism for creating partition-wise local indexes. DBMS_PCLXUTIL
circumvents the limitation that, for local index creation, the degree of parallelism is restricted to the number of partitions as only one parallel execution server process for each partition is used.
See Also:
There are several rules concerning partitions and indexes. For more information, see Oracle Database Concepts andOracle Database Administrator’s Guide.
This chapter contains the following topics:
139.1 DBMS_PCLXUTIL Overview
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_SCHEDULER
), in combination with intra-partition parallelism using the parallel execution server.
DBMS_PCLXUTIL
works with both range and range-hash composite partitioning.
The DBMS_PCLXUTIL
package can be used during the following DBA tasks:
-
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 theUNUSABLE
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 indexes for all subpartitions of the composite table.
-
Local index maintenance
By marking desired partitions usable or unusable, the
BUILD_PART_INDEX
procedure also enables selective rebuilding of local indexes. Theforce_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).
139.2 DBMS_PCLXUTIL Security Model
This utility can be run only as table owner, and not as any other user.
139.3 DBMS_PCLXUTIL Operational Notes
DBMS_PCLXUTIL
submits a job for each partition. It is the responsibility of the user/dba to control the number of concurrent jobs by setting the INIT
.ORA
parameter JOB_QUEUE_PROCESSES
correctly. There is minimal error checking for correct syntax. Any errors are reported in the job queue process trace files.
139.4 DBMS_PCLXUTIL Rules and Limits
Because DBMS_PCLXUTIL
uses the DBMS_JOB
package, you must be aware of the following limitations pertaining to DBMS_JOB
:
-
You must decide appropriate values for the
job_queue_processes
initialization parameter. Clearly, if the job processes are not started before callingBUILD_PART_INDEX
(), then the package will not function properly. The background processes are specified by the followinginit
.ora
parameters:
job_queue_processes=n #the number of background processes = n
-
Failure conditions are reported only in the trace files (a
DBMS_JOB
limitation), making it impossible to give interactive feedback to the user. This package prints a failure message, removes unfinished jobs from the queue, and requests the user to take a look at thej*.trc
trace files.
Note:
For range partitioning, the minimum compatibility mode is 8.0; for range-hash composite partitioning, the minimum compatibility mode is 8i.
139.5 Summary of DBMS_PCLXUTIL Subprograms
The DBMS_PCLXUTIL
package has one subprogram, the BUILD_PART_INDEX
procedure.
Table 139-1 DBMS_PCLXUTIL Package Subprograms
Subprogram | Description |
---|---|
Provides intra-partition parallelism for creating partition-wise local indexes |
139.5.1 BUILD_PART_INDEX Procedure
This procedure provides intra-partition parallelism for creating partition-wise local indexes.
Syntax
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);
Parameters
Table 139-2 BUILD_PART_INDEX Procedure Parameters
Parameter | Description |
---|---|
|
The number of concurrent partition-wise "local index builds". |
|
The number of parallel execution servers to be utilized for each local index build (1 <= |
|
The name of the partitioned table (an exception is raised if the table does not exist or not partitioned). |
|
The name given to the local index (an exception is raised if a local index is not created on the table |
|
If |
Usage Notes
This utility can be run only as table owner, and not as any other user.
Examples
Suppose a table PROJECT
is created with two partitions PROJ001
and PROJ002
, along with a local index IDX
.
A call to the procedure BUILD_PART_INDEX(2,4,'PROJECT','IDX',TRUE)
produces the following output:
SQLPLUS> 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