Using SYS_CONTEXT and LIKE together is slow
BillyNov 13 2006 — edited Nov 16 2006Hi,
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