Oracle DBMS_PCLXUTIL_INTERNAL
Version 18.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 Internal support for the DBMS_PCLXUTIL package supporting local partition index creation.
AUTHID DEFINER
Data Types TYPE JobList IS TABLE OF NUMBER;
Dependencies
DBA_IND_PARTITIONS DBMS_ASSERT DBMS_STANDARD
DBA_IND_SUBPARTITIONS DBMS_LOCK DUAL
DBA_SCHEDULER_JOBS DBMS_OUTPUT PCLX_JOBSEQ
DBA_TABLES DBMS_PCLXUTIL PLITBLM
DBA_TAB_PARTITIONS DBMS_SCHEDULER V$PARAMETER
Documented No
First Available 12.2.0.1
Security Model Owned by SYS with no privilege granted
Source {ORACLE_HOME}/rdbms/admin/prvthpclxi.plb
 
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,
curr_user      IN VARCHAR2);

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_internal.build_part_index(3, 1, 'PROF_HIST', 'IX_PROF_HIST', TRUE, USER);

SELECT index_name, partition_name, status
FROM user_ind_partitions;

Related Topics
Built-in Functions
Built-in Packages
DBMS_I_INDEX_UTL
DBMS_INDEX_UTL
DBMS_PCLXUTIL
Indexes
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
  DBSecWorx