| 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; |
|
