CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_PCLXUTIL
Version 11.1
 
General Information
Note: 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.
Source {ORACLE_HOME}/rdbms/admin/dbmspclx.sql
Defined Data Type TYPE JobList IS TABLE OF NUMBER;
Dependencies
DBA_IND_PARTITIONS DBMS_JOB
DBA_IND_SUBPARTITIONS DBMS_LOCK
DBA_JOBS DBMS_OUTPUT
DBA_TABLES DUAL
DBA_TAB_PARTITIONS GV$PARAMETER
DBMS_ASSERT PLITBLM
Object Privileges Execute is granted to PUBLIC
 
BUILD_PART_INDEX

Build local partition index
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);

jobs_per_batch = number of partitions
procs_per_batch = degree <= max_slaves
force_opt = If true forces rebuild of all indexes. If false rebuild only of index marked UNUSABLE.
-- create tablespace DDL on Partitions page
CREATE TABLE prof_hist (
prof_history_id  NUMBER(10),
person_id        NUMBER(10) NOT NULL,
organization_id  NUMBER(10) NOT NULL,
record_date      DATE NOT NULL,
prof_hist_comments VARCHAR2(2000))
PARTITION BY HASH (prof_history_id)
PARTITIONS 3
STORE IN (part1, part2, part3);

SELECT table_name, partition_name
FROM user_tab_partitions;

CREATE INDEX ix_prof_hist
ON prof_hist(prof_history_id)
LOCAL
UNUSABLE;

/* Causes the dictionary entries to be created without "building" the index itself, the time consuming part of creating an index. */

desc user_ind_partitions

SELECT index_name, partition_name, status
FROM user_ind_partitions;

/* Causes a concurrent build of local indexes with the specified degree of parallelism. */
exec dbms_pclxutil.build_part_index(3, 1, 'PROF_HIST', 'IX_PROF_HIST', TRUE);

SELECT index_name, partition_name, status
FROM user_ind_partitions;
 
Related Topics
DBMS_I_INDEX_UTL
DBMS_INDEX_UTL
Partitioning
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [260 users online]    © 2010 psoug.org