Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Strange behaviour in a select

stefano.cafieroJan 20 2010 — edited Jan 20 2010
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 17 2010
Added on Jan 20 2010
2 comments
530 views