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!

Using SYS_CONTEXT and LIKE together is slow

BillyNov 13 2006 — edited Nov 16 2006
Hi,

I'm using Oracle Database 10g (10.2.0.2.0).

One of my stored procedures uses the sys_context function to build dynamic where clauses. A simple test has revealed that when used in conjunction with the LIKE operator the query execution is much slower.

Let's take 2 queries executed in SQLPLUS.....

This one returns in milli-seconds

select * from my_table where column1 like 'ABCD%';

This one takes 3 seconds

select * from my_table where column1 like sys_context('MY_CTX', 'COLUMN1');

Obviously both queries have a different execution plan. I have analyzed statistics etc. If I use the EQUAL (=) operator instead of the LIKE operator then both queries adopt the same execution path hence taking the same time to execute.

Has anyone experienced similar problems when using sys_context with the LIKE operator? Any advice will be truly appreciated.

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 14 2006
Added on Nov 13 2006
14 comments
751 views