Re: Bind variable and the "like" operator

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 24 Feb 2005 06:44:55 -0800
Message-ID: <1109256295.869234.169820@o13g2000cwo.googlegroups.com>

No, the use of a bind variable in the like does not force the CBO to always perform a full table scan instead of use the index, if one is available. That decision depends on the statistics for the indexed columns, other where clause conditions, and the usual factors that influence the optimizer.

 > EXPLAIN PLAN SET statement_id = 'mpowel01' FOR   2 -- Insert sql after this line. WARNING - Do not end sql in ';'

  3  select item_no
  4  from   item_master
  5  where  item_no like :var||'%'

  6 /

Explained.

> rem
> set echo off

QUERY_PLAN
        COST CARDINALITY

---

• ----------- SELECT STATEMENT 15 21971 2.1 INDEX RANGE SCAN ITEM_MASTER_PRIME UNIQUE 15 21971

HTH -- Mark D Powell -- Received on Thu Feb 24 2005 - 08:44:55 CST