Last updated on July 27th, 2018 at 04:56 pm
And also you sometime want to know the columns which are assigned to the indexes.
Following views would be helpful in these cases
dba_ind_columns
all_ind_columns
Query to Find the indexes with assigned column is
set pagesize 50000 verify off echo off
col table_name head ‘Table Name’ format a20
col index_name head ‘Index Name’ format a25
col column_name head ‘Column Name’ format a30
break on table_name on index_name
select table_name, index_name, column_name
from all_ind_columns
where table_name like upper(‘&Table_Name’)
order by table_name, index_name, column_position
/
Now you want to know the status of the indexes also,We can use below queries
Normal Index
SELECT owner, index_name, tablespace_name,status
FROM dba_indexes;
If you want to know unusable index, then we can use below query
SELECT owner, index_name, tablespace_name,status
FROM dba_indexes
WHERE status = ‘UNUSABLE’;
Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS;
If you want to know unusable ind partitions, then we can use below query
SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS
WHERE status = ‘UNUSABLE’;
Index subpartitions:
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS;
If you want to know unusable ind subpartitions, then we can use below query
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS
WHERE status = ‘UNUSABLE’;
With 12c, Oracle has introduced the concept of Partial indexes in Partitioned table.The *_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL.
Check the indexing status of the index.
SELECT index_name,
indexing
FROM dba_indexes
ORDER BY 1;
INDEX_NAME INDEXING
————————- ——-
EXP_INDEX_N1 PARTIAL
This happened when we have indexing partial tag in the create index statement.
CREATE INDEX EXP_INDEX_NI ON tab_name(col1) Local INDEXING PARTIAL;
Related Articles
How to use Virtual Index in Oracle
How Oracle Index clustering factor is calculated
Oracle Partitioning Explained -Tables (Part -I)