Oracle 10g: query takes long time and adding index does not help I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process.
What I do is:


Get "explain plan" for the query
I noticed a "table scan" in the "explain plan" graph
I added an index for the specific column
I refreshed statistics for this table


I now get "explain plan" but the figures are the same and no execution optimization.

Any ideas?
The optimizer may still look at the table scan to be a good idea.  To insure the optimizer uses the index, have you tried adding a hint to your query to insure the optimizer uses the index?  And echoing the earlier response, more information to work with would be nice.