Create a procedure that retrieves all indexes on my table and rebuilds

Question

I want to create a procedure that retrieves all the indexes on my table and rebuilds

i retrieves all indexes with this query:

select index_name from user_indexes where table_name='your_table_name' 

and I rebuilt using this query:

 alter index <index_name> rebuild; 

thanks.

Answer 1

 create or replace procedure rebuild_indexes( p_owner in varchar2, p_table_name in varchar2 ) as begin for indexes_to_rebuild in ( select index_name from all_indexes where owner = p_owner and table_name = p_table_name ) loop execute immediate 'alter index '||p_owner||'.' ||indexes_to_rebuild.index_name||' rebuild'; end loop; end; / 

Although this will only work with the simplest indexes. There are many limitations to rebuilding. For example, if the index is partitioned, you need to rebuild each section or unit.

And there are many options that you might want to consider. For example, use ONLINE if you want others to use the index during the rebuild, add the PARALLEL parameter for faster recovery (but this also changes the parallel index parameter, which can cause problems), etc.

And keep in mind that many of Oracle’s top experts think index recovery is usually a waste of time.