27
DBMS_PCLXUTIL

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.

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

Using DBMS_PCLXUTIL

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 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,<idx_name>,<tab_name>,FALSE); 
    
 
         
```
For composite partitions, the procedure automatically builds local indices 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. 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,<idx_name>,<tab_name>,FALSE); 
    
 
         
```
Rebuild all (sub)partitions using force_opt = TRUE:
```
EXECUTE dbms_pclxutil.build_part_index(4,4,<idx_name>,<tab_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).

Limitations

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 job_queue_processes and job_queue_interval initalization 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 init.ora parameters:
```
job_queue_processes=n   #the number of background processes = n  
job_queue_interval=m    #the processes wake-up every m seconds  
```
There is an upper limit to the number of simultaneous jobs in the queue, dictated by the upper limit on the number of background processes marked SNP[0..9] and SNP[A..Z], which is 36.

See Also:
Oracle8i Administrator's Guide

Therefore, the upper limit on jobs_per_batch is MIN(#partitions, #job_queue_processes). The default value for jobs_per_batch is 1; i.e., indexes will be built one partition at a time.
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 simply prints a failure message, removes unfinished jobs from the queue, and requests the user to take a look at the snp*.trc trace files.
The primary ramification of the above point is that you are expected to know how to tune oracle (especially to set various storage parameters) in order to build large indexes. This package is not intended to assist in that tuning process.

Summary of Subprograms

DBMS_PCLXUTIL contains just one procedure: BUILD_PART_INDEX.

BUILD_PART_INDEX procedure

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 27-1 BUILD_PART_INDEX Procedure Parameters

Parameter	Description
jobs_per_batch	Number of local indexes to be built concurrently (1 <= `jobs_per_batch` <= number of partitions).
procs_per_job	Number of parallel query slaves to be utilized per local index build (1 <= `procs_per_job` <= `max_slaves`).
tab_name	Name of the partitioned table (an exception is raised if the table does not exist or not partitioned).
idx_name	Name given to the local index (an exception is raised if a local index is not created on the table `tab_name`).
force_opt	If `TRUE`, then force rebuild of all partitioned indices; otherwise, rebuild only the partitions marked '`UNUSABLE`'.

Example

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:

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>

27DBMS_PCLXUTIL