Re: how to use function-based indexes?

From you example it seems that you have missed the point of function based indexes.

In your example

select * from acquirenti
where acquirente_rag_soc like 'VERO%';

a straightforward index on acquirente_rag_soc will suffice. Function based indexes are for cases where you are issuing queries like

select * from acquirenti
where upper(acquirente_rag_soc) like 'VERO%';

conventional indices cannot be utilised for queries of this nature. HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

"Cristian Veronesi" <c.veronesi_at_crpa.it> wrote in message
news:3C161C88.D90F4A75_at_crpa.it...
Jim Kennedy wrote:



> Your test is not going to use an index in any event.  You are trying to

find


> a column value where it is like %VERO% which means if the string VERO

> appears anywhere in the column.  Oracle must do a full table scan in this

> case.  Change it to VERO% and try it.  Also in 8.1.7 you had to have

> query_rewrite=trusted.


Thanks for your answer. Unfortunately :

SQL> connect / as sysdba
Connected.
SQL> ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY=TRUSTED SCOPE=MEMORY;

System altered.

SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE SCOPE=MEMORY;

System altered.

SQL> connect uid/pwd
Connected.
SQL> explain plan for
  2  select * from acquirenti
  3  where acquirente_rag_soc like 'VERO%';

Explained.

SQL> select * from plan_view;

OPERATION            OPTIONS              OBJECT_NAME
POSITION
-------------------- -------------------- --------------------
----------
SELECT STATEMENT
10
  TABLE ACCESS       FULL                 ACQUIRENTI
1

COMPATIBLE was already set to 9.0.0. I also tried to drop and recreate
the index and to analyze table but with the same result.

Any suggestion?

Thanks again, 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)