Re: Q: sys_context() vs. regular function call

From: joel garry <joel-garry_at_home.com>
Date: Mon, 18 May 2009 09:56:39 -0700 (PDT)
Message-ID: <8e6c1828-f616-4b6d-a9ee-e41a2b704bf4_at_f28g2000pra.googlegroups.com>

On May 15, 10:22 am, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Hello
>
> Are there any differences between using sys_context in an SQL statement
> and using a regular function?  I'm not concerned here with the security
> around setting the values, just the speed of accessing the value in an SQL
> statement, and in particular whether Oracle has any special ability to
> optimize calls to SYS_CONTEXT that it doesn't have with a user defined
> function.
>
> A did a couple of explain plans on some queries using a packaged function
> and SYS_CONTEXT, and they both show up as basically the same plan (the
> sys_context needed a to_number).
>
>         select * from my_table
>         where org_id = my_pkg.fn_wanted_org_id;
>
>         select * from my_table
>         where org_id = SYS_CONTEXT('APP_CTX','RPT_CENTRE_ID');
>
> but maybe there are situations where it would make a speed difference that
> I haven't considered.
>
> Thanks for feedback.

I dunno, if the plan the way you are using it is the same, it should be the same, but consider http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:897510400346203314 when you will be using it could make a differrence. I'm wondering where http://yogeedba.blogspot.com/2008/05/enforcing-row-level-security-using.html type of situations (extending your question to that sort of situation) might make a difference, such as not being able to use an FBI or some such thing. Some peoplesoft sites apparently have had issues that desc indices are actually FBI's. Hopefully that's far enough away from what you intend to not matter.

jg

--
_at_home.com is bogus.
Shackles are not restraints.
http://www3.signonsandiego.com/stories/2009/may/18/1n18detain233238-mentally-ill-detainees-treatment-/?uniontrib

Received on Mon May 18 2009 - 11:56:39 CDT