Oracle 18cR3 DBMS_INDEX

Oracle DBMS_INDEX_UTL Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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_KKXRCA	DBMS_SYSTEM	PLITBLM

Documented No

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

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

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

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

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

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

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

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

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

TBD