==========================================
How to find the indexes for a given table.
==========================================

select i.index_name, i.tablespace_name, ceil(s.bytes / 1048576) “Size MB”
from dba_indexes i, dba_segments s
where i.index_name = s.segment_name
and table_name like ‘&table’
order by 2 /

============================================
How to see when index was rebuilt last time
============================================
SQL> select object_name, to_char(last_ddl_time,’dd-mon-yyyy hh24:mi:ss’) from dba_objects where object_name IN (‘index_name’) ;

OBJECT_NAME TO_CHAR(LAST_DDL_TIME,’DD-MON-
—————————— —————————————————————————
Index_name 04-oct-2011 09:00:50

=============================
HOW TO FIND IF INDEX SHOULD BE REBUILD OR NOT.
=============================

select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3 ;

=======================
How to get index script.
=======================

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘INDEX’,”) FROM dual;

==========================================
HOW TO FIND IF INDEX IS BEING USED OR NOT
==========================================

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/toc.htm

=================================
HOW TO TRY TO FORCE TO USE INDEX
=================================