Using index is not always good

Index can let your select very very fast. but can also let your update/insert very very slow, (Index is ordered, if update or insert is fired, it will re-order which is very costly), If your environment is more opt to system throughput, I will recommend you to use full can (full table scan + full index scan).

index logic failure:  index is ok to be used , but your query doesn't go by index.:)
index physical failure: index is removed.

Reverse index is good sometime becuase if so many indexes involved in this table , reverse index can avoid the hot block competing each other.

create index idx_id t(id) reverse;

Partition table insert : however, if index is not added, insert into partition table will be more costly than insert into normal table.

Aviod type conversion : put the specifc value into the column type specifed, type coversion will be very costly with index added.

Substr() trunc() : substr(column) and trunc(column) won't go by index , if these 2 functions are applied to columns.

Alter table t shrink space; //release high water mark . but index is still valid

Composite index : if query by a composite index, you gotta follow the order of indexes to fire the select query. reversed order or wrong order won't go by index

Hint way to create index : this part i think most developers are not clear . the hint way to create index is use /" "/,  this is very fast, try to use the hint way to create index rather than the parallel way.

Like : like "%peter" // won't go by index, like "per%" = like reverse("%per") // will go by index

Flashback :

flashback table t to before drop; // restore table from recyclebin，

                                     but all index & constrains are gone

Function index : if asc and decs are both involed like "order by col1 asc col2 desc" , try to create a function index (col1 asc, col2 desc)

Dummy index/virtual index : dummy index is not existed in reality , however it can deceive the oracle expain plan and simulate the result with indexs, if the result is good, then create a real index, if not ,don't need to do anything.

Alter session set "use nosegment indexes = true"
Create index idx_id on t(object_id) nosegment; //create dummy index

Finally please take notes :

• check whether you add indexes in some big tables in oracle
• check whether there are some indexes which never are used
• check whether you add some indexes on foreign key columns in tables
• check which tables have too many indexes
• check whether you overuse too many composite indexes
• check whether there are some foreign key constraint invalidated
• check which indexes have a high height
• check whether there are some index invalidated
• check whether your indexes are duplicated with composite index
• check which indexes have parallel setted
• check which statistic infor is too old