Re: how to use function-based indexes?

In article <3C1770C3.91534E05_at_crpa.it>, Cristian says...
>
>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:
>

I'm pretty sure its going to a function of your data or some other init.ora parameter here. Every test I do (i upped to 9i since that is obviously what you have given your optimizer goal) results in the index being used.

If you run this test:

drop table
acquirenti;

create table acquirenti ( ACQUIRENTE_RAG_SOC varchar2(80),
data char(1) default 'x' );

insert into acquirenti
select object_name, 'x' from all_objects where rownum < 10900;

insert into acquirenti values ( 'vero1', 'x');
insert into acquirenti values ( 'vero2', 'x');
insert into acquirenti values ( 'vero3', 'x');
insert into acquirenti values ( 'vero4', 'x');
insert into acquirenti values ( 'vero5', 'x');

analyze table acquirenti
compute statistics;

alter session set query_REWRITE_ENABLED=TRUE; alter session set query_REWRITE_INTEGRITY=TRUSTED; alter session set optimizer_mode = first_rows_10;

set autotrace on
select acquiRENTE_RAG_SOC
  from acquirenti
 where upper(ACQUIRENTE_RAG_SOC) like 'VERO%';

create index xie1acquirenti on
acquirenti(upper(ACQUIRENTE_RAG_SOC));

select acquiRENTE_RAG_SOC
  from acquirenti
 where upper(ACQUIRENTE_RAG_SOC) like 'VERO%';

set autotrace off

what is the output? In every case -- i get an index range scan on the second query.

>SQL> alter session set QUERY_REWRITE_ENABLED=3DTRUE;
>
>Session altered.
>
>SQL> alter session set QUERY_REWRITE_INTEGRITY=3DTRUSTED;
>
>Session altered.
>
>SQL> alter session set optimizer_mode =3D 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=3DFIRST_ROWS (Cost=3D2 Card=3D10 Byt=
>es=3D
> 200)
>
> 1 0 TABLE ACCESS (FULL) OF 'ACQUIRENTI' (Cost=3D2 Card=3D10 Bytes=
>=3D
> 200)
>
>Thanks for your help.
>
>Best regards, Cristian
>
>-- =
>
>Cristian Veronesi ><((((=BA> 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)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp