Re: how to use function-based indexes?

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Tue, 11 Dec 2001 14:18:09 GMT
Message-ID: <BAoR7.32749$ER5.369473@rwcrnsc52>

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.
Jim
"Cristian Veronesi" <c.veronesi_at_crpa.it> wrote in message news:3C161340.D3125449_at_crpa.it...
Hello.
I'm running Oracle 9.0.1.2 but I cannot use function-based indexes.

SQL> CREATE INDEX XIE1ACQUIRENTI ON ACQUIRENTI   2 (
  3 UPPER(ACQUIRENTE_RAG_SOC)
  4 );

Index created.

SQL> explain plan for
2 select * from acquirenti
3 where upper(acquirente_rag_soc) like '%VERO%' ;

Explained.

SQL> select * from plan_view;

OPERATION OPTIONS OBJECT_NAME POSITION

-------------------- -------------------- --------------------

SELECT STATEMENT
1

  TABLE ACCESS       FULL                 ACQUIRENTI

Where is my error?

TIA, Cris

--
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 Tue Dec 11 2001 - 08:18:09 CST