Re: The LIKE Clause and the optimizer!

From: <karsten_schmidt8891_at_my-deja.com>
Date: Thu, 25 Nov 1999 14:16:08 GMT
Message-ID: <81jgb6$lb4$1@nnrp1.deja.com>

Hi,
there is no point in using an index for that query. to find your result, you 'd have to scan the entire index, and then get the matching rows from your table.

it is likely that it will be faster to go to the table right away.

Karsten

In article <81iajg$27n$1_at_perki.connect.com.au>, "George" <george_at_expohire.com.au> wrote:
> I have a query:
> SELECT /* + INDEX(invoices inv_num) */
> FROM invoices
> where inv_num LIKE '%xxxx'
>
> inv_num is of VARCHAR2(10) type and has an index on it.
>
> I am aware that Oracle will choose to do a FULL TABLE SCAN because
> the character string contains a '%' as the first letter, however I
find it
> hard to believe that
> Oracle would rather use a full table scan rather than use the index
in this
> case.
> The table contains around 50000 records and has 15 fields. Query using
> cost-based optimizer.
>
> Is there a way to force the optimzer to use the index?
>
> Using Oracle 8.0.5 on WindowsNT 4 SP4
>
> Thanks in advance
>
> George
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 25 1999 - 08:16:08 CST