Tag: rebuild Invalid Indexes

Indexes will become invalid for various reason. When indexes are invalid your query may not perform well and sometime it will lead into your operation fail during few DML. So it is DBA responsibility periodically check and rebuild during off business hours.

1. How to find the Invalid Indexes
Indexes can be from normal tables or partitioned/subpartitioned tables respectively we will have NONPARTITIONED INDEXES and PARTITIONED INDEXES/SUBPARTITIONED INDEXES

We have three different views to check for respective Indexes.
1.1 DBA_INDEXES
1.2 DBA_IND_PARTITIONS
1.3 DBA_IND_SUBPARTITIONS

You can use the STATUS of these views to find the invalid Indexes. Status with VALID, USABLE and N/A is good.

you have have to write simple SQL using the all three with proper subtitle and run it periodically.

——Snip of Invalid Index SQL – Start——–

set linesize 200
set pagesize 200
col owner format a20
col index_owner format a20
col index_name format a30
col parition_name format a30
col subpartition_name format a30
col status format a10

prompt —– Invalid NONPARTITIONED Indexes
select owner,index_name,status from dba_indexes where
status not in (‘VALID’,’USABLE’,’N/A’);
prompt —– Invalid PARTITIONED Indexes
select index_owner,index_name,parition_name,status from
dba_indexes where status not in (‘VALID’,’USABLE’,’N/A’);
prompt —– Invalid SUBPARTITIONED Indexes
select index_owner,index_name,parition_name,subpartition_name,status
from dba_indexes where status not in (‘VALID’,’USABLE’,’N/A’);

——Snip of Invalid Index SQL – End——–

2. How to fix the Invalid Indexes:

Once invalid indexes are identified we have to rebuild

We can use REBUILD to rebuild the indexes. We can use below keywords appropriately to rebuild indexes and safer manner

2.1 PARALLEL
2.2 NOLOGGING
2.3 ONLINE

Note: Please be careful while doing rebuilding indexes will make some current operation on hold/abort so better you check before doing rebuild operation and I would recommend you to do it during off business hours until unless you need it as emergency. If need to be done during business hours please make sure users aware that you are doing it.

NONPARTITIONED indexes rebuild:

ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD NOLOGGING;

PARTITIONED indexes rebuild:

ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD PARTITION <PARTITION_NAME> PARALLEL NOLOGGING;