oracle-databaseindexingrebuilduser-object

How to join USER_TABLES with its corresponding indexes in USER_OBJECTS?


I have for in loop to rebuild indexes of certain tables that have changed primary key. Anyway to select only indexes of these tables from USER_OBJECTS linking with TABLE_NAME in USER_TABLES, also to exclude any IOT table's index.

 FOR r IN (SELECT OBJECT_NAME AS OBJ FORM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX') LOOP
        l_sql := 'ALTER INDEX '||r.obj||' REBUILD'||'';
        EXECUTE IMMEDIATE l_sql;
 END LOOP; 

Above code just simply rebuild all indexes in the schema (included IOT, hence hit error ORA-28650: Primary index on an IOT cannot be rebuilt)


Solution

  • I wouldn't use user_objects at all; why not go from user_indexes, and join that to user_tables?

    select ui.index_name from user_indexes ui
    join user_tables ut on ut.table_name = ui.table_name
    where ut.iot_type is null
    

    So your loop becomes:

    FOR r IN (
        select ui.index_name from user_indexes ui
        join user_tables ut on ut.table_name = ui.table_name
        where ut.iot_type is null
    )
    LOOP
        l_sql := 'ALTER INDEX "'||r.index_name||'" REBUILD';
        EXECUTE IMMEDIATE l_sql;
    END LOOP; 
    

    You don't really need l_sql, but it might be useful for debugging.

    Of course, you need to question why you're rebuilding all the indexes in the first place...