Strange behaviour in a select
Hi experts,
i'd like you help to understand a strange behaviour on a 10.2.0.4 db.
i use a select instruction in a plsql cursor with a where condition like the following:
select fields
from table1, table2, table3
where
field1 = parameter 1 and
field2 = parameter 2 and
field3 = parameter 3 and
function(field1, field2) = 0
usually this instruction works without problems.
Sometimes (i think depending on the parameter) it stucks.
I have moved the last row of the where condition right after the where instruction:
select fields
from table1, table2, table3
where
function(field1, field2) = 0 and
field1 = parameter 1 and
field2 = parameter 2 and
field3 = parameter 3
and it works with the usual performances (with the parameters that does not work on the first select).
The function returns quickly it's result regardless the parameter used.
I've compared (by toad) the execuption plan of the two selects and they are the same.
Could you please explain this behaviour ?
i can leave on the package the release 2 select but i'm not sure it fixes the problem.
I'd like to understand something more ?
thanks in advance
best regards
Stefano