Re: how to use function-based indexes?

in article 3C1770C3.91534E05_at_crpa.it, Cristian Veronesi at c.veronesi_at_crpa.it wrote on 12/12/01 6:59 AM:

<snip>

> Thank you for all your hints. Now I succeeded to force the query to use
> the index by using the correct INDEX hint syntax, but without hints the
> query performs a full-table scan, even if I set optimizer to
> FIRST_ROWS_10:
<snip>

> SQL> select acquirente_rag_soc from acquirenti
> 2 where UPPER(ACQUIRENTE_RAG_SOC) like 'VERO%';
>

I haven't been following this, but a couple of things to consider

1. What happens if you say where UPPER(ACQUIRENTE_RAG_SOC) like upper('VERO%');
2. How many rows in the table and what % of the rows qualify the condition ? - it's entirely possible that the FTS is faster than the index lookup