Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose 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 DBMS_SCHEDULER. DBMS_PCLXUTIL works with both range and range-hash composite partitioning. Prior to job scheduling is performed using DBMS_JOB.
Documented Yes
First Available Not known
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmspclx.sql
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)
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)

-- 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
Built-in Functions
Built-in Packages
Partitioned Tables and Indexes
What's New In 18c
What's New In 19c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved