I have often seen it asked online many times how to get a list of invalid indexes. The information was mostly incomplete, and often contained misinformation. Determined to solve this problem once and for all I wrote a query to get a list of invalid indexes on our Oracle 10g database.
Before we get on to the query, I'd like to note what makes finding invalid indexes so difficult, and how to overcome these obstacles.
Finding invalid global indexes is rather easy. Global indexes are your standard indexes you use and love every day. They are simply an index on a column, or columns in a standard non-partitioned table. Oracle provides a nice little view called all_indexes, which has a column named status. This view will return a list of all indexes that the user running the query has permissions to view. There is also the dba_indexes view and the user_indexes view. The status column can be one of three values, VALID, INVALID, or N/A. Global indexes will never have a status of N/A.
We run into trouble if we try to query WHERE status != 'VALID' if our database has any partitioned tables with partitioned indexes and potentially bring back valid indexes. Partitioned tables, and their indexes, save the lives of millions of babies every year, so we must give them our full respect, and do the necessary extra work that is required to account for the quirks they sometimes bring to our code.
Partitioned indexes will always have a status of N/A in the all_indexes view. Each of these indexes will have a partitioned index, which we can see by querying the all_ind_partitions view. Again, we also have the dba_ind_partitions and user_ind_partitions views available to us as well. This table contains all partitioned indexes, along with a status column. This status column works a little bit differently. Its possible values are USABLE, UNUSABLE, and N/A.
If our index'es partitioned indexes are marked as usable then we know our index is valid. However, we could again run into a case where our status column is marked as N/A. Partitioned indexes can go a level deeper and have their own subpartitioned indexes. Determined, we pick up our shovel and keep digging deeper.
Oracle provides us with a third view named all_ind_subpartitions. Like the above views, we also have user_ind_subpartitions and dba_ind_subpartitions. Again, we have a status column. This time, though, we can only have two possible values, USABLE, and UNUSABLE. At this point, we can determine if our index is valid or not by looking at its partition's subpartition's status.
Okay, so this is all well and good, but what we really need is a query that will give us the name of indexes that need rebuilding. Global indexes do not have partitions, and not all partitioned indexes have subpartitions, yet we want to know all invalid indexes, regardless of their makeup. The following query accomplishes this.
select
index_name
from
all_indexes
where
owner not in ('SYS', 'SYSTEM')
and
status != 'VALID'
and (
status != 'N/A'
or
index_name in (
select
index_name
from
all_ind_partitions
where
status != 'USABLE'
and (
status != 'N/A'
or
index_name in (
select
index_name
from
all_ind_subpartitions
where
status != 'USABLE'
)
)
)
);
I'll leave dissecting how this query works as a lesson to the reader. Here I am also leaving out the indexes owned by SYS and SYSTEM, but you can remove that line if you need those indexes listed as well.
I do presently have an anonymous block that makes a cursor out of this query, loops through it, and builds all indexes, partitioned indexes, and subpartitioned indexes, as is appropriate. I am still working on making sure that it is battle hardened. After that I'll post about how I accomplish this. Combined they provide an elegant solution to finding all of your invalid indexes and rebuild them.
If you find this helpful, or have some additional information to provide, please do so in the comment section below.
Edit: The script is available at Rebuilding invalid indexes in Oracle
