The LIKE Clause and the optimizer!

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 Wed Nov 24 1999 - 21:33:39 CST