Q: sys_context() vs. regular function call

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 15 May 2009 10:22:35 -0700
Message-ID: <4a0da4db$1_at_news.victoria.tc.ca>


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. Received on Fri May 15 2009 - 12:22:35 CDT

Original text of this message