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