Rebuilding all indexes (the (even) easier way)



Occasionally there is the need to rebuild all the indexes in a schema, or more likely all the indexes on a particular table.

This is relatively easy to do by using 'dynamically created SQL' - i.e. using SQL to create the SQL command to then run.

Something like

select 'alter index '||index_name||' rebuild ;' from user_indexes;

This creates a SQL file with a row for each index in that schema to be rebuilt.

This works well enough and is a useful technique for lots of requirements.

However there is a better way (and it's actually been around since v10 believe it or not).

There is a package called DBMS_INDEX_UTL which has a number of useful looking procedures - I've just picked out one to see what it can do. In the example below I want to rebuild all the indexes in my schema called ALIGNE.

set serverout on -- set so we can see the number of indexes processed

declare
retval pls_integer;
begin
dbms_index_utl.build_schema_indexes('ALIGNE',FALSE,'ALL',TRUE,TRUE,4,retval);
dbms_output.put_line(retval);
end;
/


This rebuild all the indexes in the ALIGNE user (this can be a comma separated list of schemas), FALSE means rebuild everything (not just unusable ones), ALl means all index types,the first TRUE means run things concurrently, the seconf TRUE means continue after error, the number 4 means run 4 jobs at the same the final argument is the number of errors encountered.

As you can see from the screenshot from cloud control, it correctly spawns 4 jobs all rebuilding indexes:



There are a number of other options for doing rebuild - as can be seen from a describe of the package.

SYS@>desc dbms_index_utl
PROCEDURE BUILD_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
PROCEDURE BUILD_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
PROCEDURE BUILD_INDEX_COMPONENTS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
PROCEDURE BUILD_INDEX_COMPONENTS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
PROCEDURE BUILD_SCHEMA_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
PROCEDURE BUILD_SCHEMA_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
PROCEDURE BUILD_TABLE_COMPONENT_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
PROCEDURE BUILD_TABLE_COMPONENT_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
PROCEDURE BUILD_TABLE_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
PROCEDURE BUILD_TABLE_INDEXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LIST                           VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
PROCEDURE MULTI_LEVEL_BUILD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA_LIST                    VARCHAR2                IN     DEFAULT
 TABLE_LIST                     VARCHAR2                IN     DEFAULT
 INDEX_LIST                     VARCHAR2                IN     DEFAULT
 IDX_COMP_LIST                  VARCHAR2                IN     DEFAULT
 TAB_COMP_LIST                  VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT
 NUM_ERRORS                     BINARY_INTEGER          OUT
PROCEDURE MULTI_LEVEL_BUILD
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA_LIST                    VARCHAR2                IN     DEFAULT
 TABLE_LIST                     VARCHAR2                IN     DEFAULT
 INDEX_LIST                     VARCHAR2                IN     DEFAULT
 IDX_COMP_LIST                  VARCHAR2                IN     DEFAULT
 TAB_COMP_LIST                  VARCHAR2                IN     DEFAULT
 JUST_UNUSABLE                  BOOLEAN                 IN     DEFAULT
 LOCALITY                       VARCHAR2                IN     DEFAULT
 CONCURRENT                     BOOLEAN                 IN     DEFAULT
 CONT_AFTER_ERR                 BOOLEAN                 IN     DEFAULT
 MAX_SLAVES                     NUMBER(38)              IN     DEFAULT



Comments