Re: The LIKE Clause and the optimizer!

Why do you find it hard to believe.

A couple of side-issues:

    Your hint has a space between the /* and the +     so it is a comment not a hint.

    You haven't listed the columns you want     to select from the table - different options     may result in different access paths.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

George wrote in message <81iajg$27n$1_at_perki.connect.com.au>...
>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
>
>
Received on Thu Nov 25 1999 - 13:47:50 CST