Index Creation and Maintenance Issue in Oracle

•Drop the index that is not used by the application. Though as you may think that query optimizer select the best execution plan and thus avoid non selective index while execute query, so if I keep index and if I don't drop it what is the problem. The problem is before generating execution plan the SQL engine must continue to maintain all indexes defined against a table, regardless of whether they are used or not.

•One way to know whether application use index against table is issuing ALTER INDEX ... monitoring usage and then query from V$OBJECT_USAGE and look at USED column. The following section is an example to see whether index table_16K_I is used or not.

SQL> alter index table_16K_I monitoring usage;
Index altered.

SQL> select START_MONITORING,USED,MONITORING, INDEX_NAME from V$OBJECT_USAGE where table_name='TABLE_16K';
START_MONITORING USE MON INDEX_NAME
------------------- --- --- ------------------------------
06/23/2008 01:22:28 NO YES TABLE_16K_I


•If you create one index for a table then it may affect the execution plan of another sql statement. So after you create one index run SQL trace facility and reexamine application performance.

•You can use SQL Access advisor which automatically suggests you whether to create index on which column of the table based on the application current workload, or hypothetical workload or a user defined set of SQL statements, or a SQL Tuning set. You can easily run SQLAccess advisor from enterprise manager. To run it
Go to Enterprise Manager Home Page> Related Links> Advisor Central> SQL Access Advisor and then proceed as you wish.