Oracle | DBMS_INDEX

Oracle DBMS_INDEX_UTL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Reorg_action input defaults
INDEX_REORG_SAMPLE_PCT	NUMBER	10
INDEX_REORG_SHRINK_PCT	NUMBER	10
INDEX_REORG_REBUILD_PCT	NUMBER	35
Reorg_action output actions
INDEX_REORG_ACTION_NONE	NUMBER	1
INDEX_REORG_ACTION_SHRINK	NUMBER	2
INDEX_REORG_ACTION_REBUILD	NUMBER	4

Dependencies

DBMS_I_INDEX_UTL	DBMS_STANDARD	DBMS_UTILITY
DBMS_SNAPSHOT_KKXRCA	DBMS_SYSTEM	PLITBLM

Documented No

First Available 10.1

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsidxu.sql

Subprograms

BUILD_INDEXES	BUILD_TABLE_COMPONENT_INDEXES	GET_REORG_INDEX_ACTION
BUILD_INDEX_COMPONENTS	BUILD_TABLE_INDEXES	MULTI_LEVEL_BUILD
BUILD_SCHEMA_INDEXES

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_2019,

                                  SH.SALES_TIME_BIX.SALES_Q1_2020';

      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 '%202%'

      6 ORDER BY 1;

      

      PARTITION_NAME

      -------------------------------------

      SALES_Q1_2020

      SALES_Q2_2020

      SALES_Q3_2020

      SALES_Q4_2020

      

      DECLARE

       complist VARCHAR2(128);

       retval  PLS_INTEGER;

      BEGIN

        complist := 'SH.SALES.SALES_Q1_2020, SH.SALES.SALES_Q2_2020';

      

        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_2020, SH.SALES.SALES_Q2_2020';

      

        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,

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

GET_REORG_INDEX_ACTION (new 21c)

Provides an interface for executing a fast index analysis and to then determine if reorganization is required, based on internal criteria

Follow the demo, at right.
Count us as initially unimpressed.

We will perform some more testing in the future to see if specifying non-default values makes a difference.

This isn't a tool we will be relying on based on an initial peek at its new functionality. dbms_index_utl.get_reorg_index_action( index_owner IN VARCHAR2, index_name IN VARCHAR2, index_comp IN VARCHAR2 DEFAULT NULL, index_subcomp IN VARCHAR2 DEFAULT NULL, sample_pct IN PLS_INTEGER DEFAULT INDEX_REORG_SAMPLE_PCT, shrink_pct IN PLS_INTEGER DEFAULT INDEX_REORG_SHRINK_PCT, rebuild_pct IN PLS_INTEGER DEFAULT INDEX_REORG_REBUILD_PCT, reorg_action OUT NOCOPY PLS_INTEGER);

DECLARE

       outVal PLS_INTEGER;

      BEGIN

        dbms_index_utl.get_reorg_index_action('UWCLASS', 'PK_SERVERS', reorg_action=>outVal);

        dbms_output.put_line(TO_CHAR(outVal));

      END;

      /

      4

      

      PL/SQL procedure successfully completed.

      

      -- use the reorg constant definitions, above, to identify the action requested

      -- "4" corresponds with the constant INDEX_REORG_ACTION_REBUILD so Oracle is

      --  recommending the index be rebuilt

      

      ALTER INDEX uwclass.pk_servers COALESCE;

      

      Index altered.

      

      DECLARE

       outVal PLS_INTEGER;

      BEGIN

        dbms_index_utl.get_reorg_index_action('UWCLASS', 'PK_SERVERS', reorg_action=>outVal);

        dbms_output.put_line(TO_CHAR(outVal));

      END;

      /

      4

      

      ALTER INDEX uwclass.pk_servers REBUILD;

      

      Index altered.

      

      DECLARE

       outVal PLS_INTEGER;

      BEGIN

        dbms_index_utl.get_reorg_index_action('UWCLASS', 'PK_SERVERS', reorg_action=>outVal);

        dbms_output.put_line(TO_CHAR(outVal));

      END;

      /

      
      4

      

      ALTER TABLE uwclass.servers DROP PRIMARY KEY;

      

      Table altered.

      

      ALTER TABLE uwclass.servers

      ADD CONSTRAINT pk_servers

      PRIMARY KEY (srvr_id);

      

      Table altered.

      

      DECLARE

       outVal PLS_INTEGER;

      BEGIN

        dbms_index_utl.get_reorg_index_action('UWCLASS', 'PK_SERVERS', reorg_action=>outVal);

        dbms_output.put_line(TO_CHAR(outVal));

      END;

      /

      
      4

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