Bind variable and the "like" operator

From: Christian Kleinewaechter <christian.kleinewaechter_at_USEDEASTLDtelefonica>
Date: Thu, 24 Feb 2005 15:32:28 +0100
Message-ID: <421de57d$0$18074$750803fa@corp.news.telefonica.de>

Hello!

Is there an efficient way to use the "like" operator with bind variables if I intend to find rows where a column contains a given argument as prefix or is there another way to efficiently simulate "where s like :variable || '%'"?
With literals in place, the optimizer knows that it can use indexes for the clause "where s like 'XYZ%'", with bind variables of course it cannot know that i will use only a trailing wildcard and do a full tablescan instead.

Regards
Christian

SQL> create table stringtab (s varchar2(100));

Table created.

SQL> insert into stringtab select object_name from all_objects;

30253 rows created.

SQL> create index ix_stringtab on stringtab(s);

Index created.

[analyze table and index]

SQL> create or replace function testforlike(pre in varchar2) return integer

   2 as
   3 i integer;
   4 begin

   5          select count(*) into i from stringtab where s like pre||'%';
   6          return i;

   7 end;
   8 /

Function created.

SQL> set autotrace traceonly explain;

SQL> select count(*) from stringtab where s like 'STR%';

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=23)     1 0 SORT (AGGREGATE)

    2    1     INDEX (RANGE SCAN) OF 'IX_STRINGTAB' (NON-UNIQUE) (Cost=
           2 Card=2 Bytes=46)

SQL> select testforlike('STR') from dual;

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE     1 0 TABLE ACCESS (FULL) OF 'DUAL' SQL> select count(*) from stringtab where s like 'STR'||'%';

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=23)     1 0 SORT (AGGREGATE)

    2    1     INDEX (RANGE SCAN) OF 'IX_STRINGTAB' (NON-UNIQUE) (Cost=
           2 Card=2 Bytes=46)

Received on Thu Feb 24 2005 - 08:32:28 CST