Oracle:How to check index status?

Question

I create an index createdate_idx on field createdate, then make this query:

select * from tablename where createdate>=to_date('2016-02-29','yyyy-mm-dd');

But I am not sure whether the index createdate_idx has been used or not. So How can I make this confirm?

Answer 1

EXPLAIN PLAN will show you what index is used and other information.

For example:

explain plan for
select * from tablename where createdate>=to_date('2016-02-29','yyyy-mm-dd');

select * from table(dbms_xplan.display);

Plan hash value: 3955337982

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLENAME |     1 |     9 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CREATEDATE">=TO_DATE(' 2016-02-29 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Answer 2

Index status can be checked with query to Oracle system view:

select TABLE_NAME, INDEX_NAME, STATUS from USER_INDEXES where TABLE_NAME like '%';

Value N/A for the STATUS field means that index is partitioned. Individual index partitions' status can be checked with this query:

select INDEX_NAME, PARTITION_NAME, STATUS from USER_IND_PARTITIONS where INDEX_NAME like '%';