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
Post a Comment