Oracle8i Supplied Packages Reference
Release 8.1.5

A68001-01

Library

Product

Contents

Index

Prev Next

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:

  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,<idx_name>,<tab_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,<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:

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>



Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index