Re: how to use function-based indexes?

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                   

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)