Re: Use of sys_context changes execution plan

You've probably answered your own question.

>sys_context ( in order to make use of bind variables )

Bind variables are generally of unknown value at optimisation time, so may cause the path to change, especially if your query involves a range operator.

There is also the problem that sys_context() returns a character value, so perhaps you are also being caught by an unexpected coercion effect.

For comparative purposes, try getting an execution path (from explain plan) using things like :n instead of the literals, this may give you an idea of what the path would be with sys_context().

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Paul Ansell wrote in message ...


>I have a dynamically generated query which when executed with

>hard-coded where clause values uses an execution plan that I am happy

>with. However, when I replace the hard-coded values with calls to

>sys_context ( in order to make use of bind variables ) a different,

>inferior execution plan is chosen. Any ideas why this should happen?