Oracle 12c DBMS_INDEX

Oracle DBMS_INDEX_UTL Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

Purpose

APIs to build partitioned and non-partitioned indexes with parallel slaves.

AUTHID

CURRENT_USER

Dependencies

DBMS_I_INDEX_UTL	DBMS_STANDARD	DBMS_UTILITY
DBMS_SNAPSHOT	DBMS_SYSTEM	PLITBLM

Documented

First Available

10gR1

Security Model

Owned by SYS with EXECUTE granted to PUBLIC

Source

{ORACLE_HOME}/rdbms/admin/dbmsidxu.sql

Subprograms

BUILD_INDEXES	BUILD_SCHEMA_INDEXES	BUILD_TABLE_INDEXES
BUILD_INDEX_COMPONENTS	BUILD_TABLE_COMPONENT_INDEXES	MULTI_LEVEL_BUILD

BUILD_INDEXES (new 12.1 parameters)

Provides an interface for finding and concurrently, and in parallel, rebuild all the index components (including entire indexes, index partitions, and index subpartitions) for a list of indexes

Overload 1

dbms_index_utl.build_indexes(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);

SELECT index_name
FROM user_indexes;

set serveroutput on

DECLARE
x PLS_INTEGER;
BEGIN
dbms_index_utl.build_indexes('UWCLASS.PK_SERV_INST,
UWCLASS.IX_SERV_INST', FALSE, 'ALL', FALSE, FALSE, 2, x);

dbms_output.put_line('Failed to rebuild: ' || TO_CHAR(x));
END;
/

Overload 2

SELECT index_name
FROM user_indexes;

exec dbms_index_utl.build_indexes('UWCLASS.PK_SERV_INST, UWCLASS.IX_SERV_INST', FALSE, 'ALL', FALSE, FALSE, 2);

BUILD_INDEX_COMPONENTS (new 12.1 parameters)

Provide an interface for finding and concurrently, and in parallel, rebuilding a given list of index components (partitions and subparts). This will not rebuild entire indexes

Overload 1

dbms_index_utl.build_index_components(
list           IN VARCHAR2 DEFAULT NULL, -- <owner>.<index>.<comp>)
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);

See Demo for Overload 2 Below

Overload 2

dbms_index_utl.build_index_components(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE);

SQL> SELECT index_name
   2 FROM dba_indexes
   3 WHERE owner = 'SH'
   4 AND table_name = 'SALES';

INDEX_NAME
------------------------------
SALES_TIME_BIX
SALES_CUST_BIX
SALES_PROD_BIX
SALES_PROMO_BIX
SALES_CHANNEL_BIX

DECLARE
complist VARCHAR2(128) := 'SH.SALES_TIME_BIX.SALES_Q1_2002,
                            SH.SALES_TIME_BIX.SALES_Q1_2003';
BEGIN
dbms_index_utl.build_index_components(complist);
END;
/

BUILD_SCHEMA_INDEXES (new 12.1 parameters)

Provides an interface for finding and concurrently and rebuilding, in parallel, all the index components (including entire indexes, index partitions, and index subpartitions) for a list of schemas

Overload 1

dbms_index_utl.build_schema_indexes(
list           IN VARCHAR2 DEFAULT NULL, -- comma delimited schemas
just_unusable IN BOOLEAN DEFAULT TRUE, -- unusable only or all
locality       IN VARCHAR2 DEFAULT 'ALL', -- GLOBAL | LOCAL | ALL
concurrent     IN BOOLEAN DEFAULT TRUE, -- if TRUE uses Jobs
cont_after_err IN BOOLEAN DEFAULT FALSE, -- TRUE | FALSE
max_slaves     IN INT      DEFAULT NULL, -- degree of parallelism
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);    -- number of failures

set serveroutput on

DECLARE
x PLS_INTEGER;
BEGIN
dbms_index_utl.build_schema_indexes('UWCLASS', FALSE, 'ALL', FALSE,
FALSE, 2, x);

dbms_output.put_line('Failed to rebuild: ' || TO_CHAR(x));
END;
/

Overload 2

dbms_index_utl.build_schema_indexes(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE);

exec dbms_index_utl.build_schema_indexes('UWCLASS', FALSE, 'ALL', FALSE, FALSE, 2);

BUILD_TABLE_COMPONENT_INDEXES (new 12.1 parameters)

Provide an interface for finding and concurrently, and in parallel rebuilding all the index components (including index partitions, and index subpartitions) for a given list of table components

Overload 1

dbms_index_utl.build_table_component_indexes(
list           IN VARCHAR2 DEFAULT NULL,
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);

conn / as sysdba

ALTER SESSION SET CONTAINER = PDBDEV;

SQL> SELECT partition_name
2 FROM dba_tab_partitions
3 WHERE table_owner = 'SH'
4 AND table_name = 'SALES'
5 AND partition_name LIKE '%2000%'
6 ORDER BY 1;

PARTITION_NAME
-------------------------------------
SALES_Q1_2000
SALES_Q2_2000
SALES_Q3_2000
SALES_Q4_2000

DECLARE
complist VARCHAR2(128);
retval PLS_INTEGER;
BEGIN
complist := 'SH.SALES.SALES_Q1_2000, SH.SALES.SALES_Q2_2000';

dbms_index_utl.build_table_component_indexes(complist, num_errors=>retval);
-- note that this does not print if no exception occurs
dbms_output.put_line('NUM_ERRORS: ' || TO_CHAR(retval));
END;
/

Overload 2

DECLARE
complist VARCHAR2(128);
BEGIN
complist := 'SH.SALES.SALES_Q1_2000, SH.SALES.SALES_Q2_2000';

dbms_index_utl.build_table_component_indexes(complist, FALSE, 'ALL');
END;
/

BUILD_TABLE_INDEXES (new 12.1 parameters)

Provide an interface for finding and concurrently rebuilding, in parallel, all the index components (including entire indexes, index partitions, and index subpartitions) for a list of tables

Overload 1

dbms_index_utl.build_table_indexes (
list           IN VARCHAR2 DEFAULT NULL, -- list of tables
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);

set serveroutput on

DECLARE
x PLS_INTEGER;
BEGIN
dbms_index_utl.build_table_indexes ('UWCLASS.SERVERS,
UWCLASS.AIRPLANES', FALSE, 'ALL', FALSE, FALSE, 2, x);

dbms_output.put_line('Failed to rebuild: ' || TO_CHAR(x));
END;
/

Overload 2

dbms_index_utl.build_table_indexes (
list           IN VARCHAR2 DEFAULT NULL,
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,

BEGIN
dbms_index_utl.build_table_indexes('UWCLASS.SERVERS, UWCLASS.AIRPLANES', FALSE, 'ALL', FALSE, FALSE, 2);

MULTI_LEVEL_BUILD (new 12.1 parameters)

Provide an interface for finding and concurrently, and in parallel, rebuilding all the index components (including index partitions, and index subpartitions) for several lists of schema objects. This function has an advantage over others in that it provides the maximum amount of concurrency possible by removing serialization forced by making sequential calls to different rebuild functions

Overload 1

dbms_index_utl.multi_level_build (
schema_list    IN VARCHAR2 DEFAULT NULL,
table_list     IN VARCHAR2 DEFAULT NULL,
index_list     IN VARCHAR2 DEFAULT NULL,
idx_comp_list IN VARCHAR2 DEFAULT NULL,
tab_comp_list IN VARCHAR2 DEFAULT NULL,
just_unusable IN BOOLEAN DEFAULT TRUE,
locality       IN VARCHAR2 DEFAULT 'ALL',
concurrent     IN BOOLEAN DEFAULT TRUE,
cont_after_err IN BOOLEAN DEFAULT FALSE,
max_slaves     IN INT      DEFAULT NULL,
forced_degree IN INT      DEFAULT NULL,
retry_online   IN BOOLEAN DEFAULT FALSE,
num_errors     OUT NOCOPY PLS_INTEGER);

TBD

Overload 2

TBD