UNUSABLE Indexes Query in Oracle
With the article “UNUSABLE Indexes Query in Oracle,” we can quickly identify all the INVALID indexes in a SCHEMA.
It may happen that, due to various factors, the indexes in a SCHEMA change status from USABLE to UNUSABLE.
There are various factors indeed. One of the main causes can be a data reorganization involving MOVE TABLE. Indexes associated with the table moved to eliminate fragmentation become invalidated.
Alternatively, in the case of DDL modifications to the table, indexes can become invalid.
An invalid index causes significant slowdowns because every SELECT on the table would read all records.
The FULL_TABLE_SCAN generated is the cause of the query’s poor performance and, in some cases, of the entire schema. The actual cause is the high resource consumption required for massive data reading, large amounts of disk I/O in reading, and high CPU usage.
How do we check if there are any invalid indexes?
Unusable Indexes in Oracle Database Schema
We execute the provided query to identify them and perform the rebuild.
The fields we will select are:
OWNER: Owner of the index.
INDEX_NAME: Name of the index.
INDEX_TYPE: Type of the index.
STATUS: Status of the index.
Furthermore, the above-mentioned information is extracted from the DBA_INDEXES view.
Finally, the information is filtered for STATUS <> ‘VALID’ and OWNER different from SYS and SYSTEM.
Query for Invalid Indexes in Oracle:
set lines 180
col owner for a30
col index_name for a30
col index_type for a30
col status for a15
select owner, index_name,index_type,status
from dba_indexes
where owner not in ('SYSTEM','SYS')
and status <> 'VALID'
order by 1,2;To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin
For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.
0 Comments