If you are here, you have already:
Oracle will ignore an index for a number of reasons, but they boil down to two possibilities:
To find out which of these is true for your SQL, add an INDEX hint to your SQL.
SELECT /*+ INDEX(a, i_empno) */ a.* FROM emp a
WHERE a.emp_no = 111
Now run the SQL through Explain Plan. Is the index being used now? If the index is being used in a FULL SCAN, then this is pointless: it is just reading every row from the index. If the index is now being used with a UNIQUE or RANGE scan, it means that the Cost Based Optimiser doesn't think the index is very useful. Have you checked the statistics? Try running the SQL again; is the performance better now? If the performance is still poor, then Oracle was probably right to ignore the index - you almost certainly have a Range Scan problem.
If the INDEX hint did not work (ie. Oracle is still using Full Table Scan, Full Index Scan, or a different index), then there is something about the SQL preventing the index from being used.
- WHERE col IN (SELECT ...)
- WHERE col = (SELECT ...)
- WHERE col >= ANY (SELECT ...)
- WHERE col <= ALL (SELECT ...)
- WHERE col NOT IN (SELECT ...)
One of the bullet points above should determine why the index is not being used. If not, then work on the process of elimination. First confirm that oracle recognises the index - run the following through Explain Plan
SELECT /*+ INDEX(a, index_name)*/ *
FROM table_name a
WHERE index_col = :val
If this simple SQL refuses to use the index, then there is a fundamental problem. Get the DBA to drop and rebuild the index.
If the sample SQL above does use the index, then start with your original SQL and remove lines one at a time, running each successively smaller SQL through Explain Plan as you go. Somewhere between the original SQL and the one above, the index will start to work. Once you find the problem, check the bullet points above to see whether any of them explain the fault. If not, discuss the problem with the DBA.