Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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
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);
PL/SQL procedure successfully completed.
SELECT index_name, partition_name, status
FROM user_ind_partitions;