Re: how to use function-based indexes?

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Tue, 11 Dec 2001 16:18:15 GMT
Message-ID: <blqR7.32893$ER5.374350@rwcrnsc52>

How many rows are in the acquirenti table? (a full scan might be efficient.)

Also when you defined the function based index you did: ... ( UPPER(... ))
when you issed the query you did
... upper(...)
I know this sounds silly but try in your select statement ...UPPER(...)
I believe that the function and the select have to match even in case. Again sounds silly, but that might be it. Jim
"Cristian Veronesi" <c.veronesi_at_crpa.it> wrote in message news:3C162538.F77972CB_at_crpa.it...
Niall Litchfield wrote:

> Function based
> indexes are for cases where you are issuing queries like
>
> select * from acquirenti
> where upper(acquirente_rag_soc) like 'VERO%';

Sorry, in my second post I forgot to include the UPPER function in the query. But the problem still remains:

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
10

  TABLE ACCESS       FULL                 ACQUIRENTI

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 Tue Dec 11 2001 - 10:18:15 CST