Re: Index Not Used with Parameterized Query

Hello Bill,

Is it Oracle 9i or 8i ? Please specify your Oracle version.

In Oracle 9i, CBO does so-called bind variable peeking. It means that, when the query is parsed for the first time, the optimizer will peek at the binds in order to determine how to optimize the query. The index is helpful when the query is not using a leading '%" (as in your case).

When you specify a literal, the optimzer sees right away that you are not using a leading '%' so it decides to use the index. With the bind variable, it has to rely on the peek value obtained during the first parse.

It is possible that during the first parse your bind variable _did_ contain a leading '%', therefore, CBO decided to use an FTS for the first and all subsequent queries even if those queries used a pattern like 'ABC%'.

Rgds.

VC

"Biff Gaut" <biff_gaut_at_hotmail.com> wrote in message news:a6090148.0312231718.40367f16_at_posting.google.com...
> The following query returns its results from our database in well
> under a second-
>
> SELECT
> DISTINCT I.COL1,
> I.COL2,
> E.COL3,
> E.COL1
> FROM
> TABLE1 I,
> TABLE2 E
> WHERE
> I.COL1 = E.COL1 AND
> I.COL2 LIKE 'AVALUE%'
>
> If we convert the query to use a bind variable-
>
> SELECT
> DISTINCT I.COL1,
> I.COL2,
> E.COL3,
> E.COL1
> FROM
> TABLE1 I,
> TABLE2 E
> WHERE
> I.COL1 = E.COL1 AND
> I.COL2 LIKE :LastName
>
> and specify the parameter as 'AVALUE%' we get the same resultset, but
> the query takes 25-50 seconds. Running a trace on the server reveals
> that the index on E.COL1 is being used in the first case and is not
> used in the second case. Can anyone explain to me why the index isn't
> used in the latter example and what changes I can make to ensure that
> it is used?
>
> Thanks,
> Biff Gaut
> Gaithersburg, MD
Received on Tue Dec 23 2003 - 20:11:38 CST