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

From: Heini Nolsøe <HNO_at_dlg.dk>
Date: Fri, 11 Aug 2006 11:46:56 +0200
Message-ID: <44dc5213$0$12632$edfadb0f_at_dread14.news.tele.dk>

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?

Received on Fri Aug 11 2006 - 11:46:56 CEST