Hi,
I am currently researching a few queries that are showing in v$session_longops. When I view the explain plan for them they're showing full table scans but when I manually test the statement in Toad or sqlplus and trace them they're using an index and return almost instantly.
Or, the adverse to that, we've added an index and the query picks it up and uses it for some time, and then stops using it a few hours later.
What's going on? This is infuriating.
Is my only option to request code changes that add an index hint to force the index to be used?
Thanks.