Re: how to use function-based indexes?

From: Cristian Veronesi <c.veronesi_at_crpa.it>
Date: Wed, 12 Dec 2001 15:59:15 +0100
Message-ID: <3C1770C3.91534E05@crpa.it>

Thomas Kyte wrote:

> read the posting at:
>
> http://osi.oracle.com/~tkyte/article1/index.html
>
> it'll help you get your example going.

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: SQL> alter session set QUERY_REWRITE_ENABLED=TRUE;

Session altered.

SQL> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;

Session altered.

SQL> alter session set optimizer_mode = first_rows_10;

Session altered.

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

ACQUIRENTE_RAG_SOC

---

VERONI SILVANO
VERONI ANGELO E P. PAOLO
VERONA ROBERTO EGIDIO E MUSSINI CRISTINA VERONESI DANTE
VERONESI SILVANO Execution Plan

---

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=10 Bytes=
          200)

   1    0   TABLE ACCESS (FULL) OF 'ACQUIRENTI' (Cost=2 Card=10 Bytes=
          200)

Thanks for your help.

Best regards, Cristian

-- 
Cristian Veronesi ><((((º> http://www.crpa.it

There are no good wars, with the following exceptions: The American 
Revolution, World War II, and the Star Wars Trilogy. (Bart Simpson)

Received on Wed Dec 12 2001 - 08:59:15 CST