SELECT ... WHERE column LIKE parameter||'%'

Question posted by: Heini =?ISO-8859-2?B?Tm9sc/hl?= (Guest) on June 27th, 2008 07:04 PM

I have a database containing alot of procedures with this kind of coding logic:


BigTable
column1 varchar2(40);


Procedure SomeProcedure (in_parameter varchar2)
SELECT column1
FROM BigTable
WHERE column1 LIKE in_parameter||'%';
END;


The procedure is called like this:

SomeProcedure('abc');


Although this coding logic selects the correct rows, there is a big problem - it does not use the index on BigTable.
But if you do perform at little test and change the procedure like this:


Procedure SomeProcedure (in_parameter varchar2)
SELECT column1
FROM BigTable
WHERE column1 LIKE 'abc'||'%';
END;

Then it uses the index. But ofcourse now there is not the desired flexibility of using a parameter.


QUESTIONS:
1. Why does Oracle not use the index when a parameter is used in the LIKE-statement?

Not the answer you were looking for? Post your question . . .
182,265 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).