How to join USER_TABLES with its corresponding indexes in USER_OBJECTS?

Question

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)

Answer 1

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...

Answer 2

select * from user_tables where iot_type is not null;

will return index-organized tables.

As I said, when working with indexes, consider querying USER_INDEXES.