Forget missing indexes, have you thought about UNUSABLE indexes

So I did all that good work in https://shannonscncjdeblog.blogspot.com.au/2017/06/jde-slow-missing-indexes-find-it-fast.html but I find a bunch of my jobs are still not using indexes.  When I look into this some more, I see that the indexes are UNUSABLE!

Wow, deeper into the rabbit hole.

Use the following SQL to find indexes that are not valid.

select * from all_indexes where status <> 'VALID';

Then use the following to generate the statements to fix them.

select 'ALTER INDEX CRPDTA.' || index_name || ' REBUILD TABLESPACE CRPINDEX2;' FROM ALL_INDEXES where status = 'UNUSABLE';

And run the results, easy!

ALTER INDEX CRPDTA.F07351T_PK REBUILD TABLESPACE CRPINDEX2;
ALTER INDEX CRPDTA.F07351_PK REBUILD TABLESPACE CRPINDEX2;
ALTER INDEX CRPDTA.F07350_PK REBUILD TABLESPACE CRPINDEX2;
ALTER INDEX CRPDTA.F07315_PK REBUILD TABLESPACE CRPINDEX2;
ALTER INDEX CRPDTA.F073111_PK REBUILD TABLESPACE CRPINDEX2;