Re: how to use function-based indexes?

No the functions don't have to match in case. This is function-based indexes were dealing with here, not stored outlines. An index built on emp(sum(sal)+avg(sal)+32) can even be used by the optimizer if the eventual select is for 32+sum(sal)+avg(sal)>53. It's aware of commutative mathematical operations, therefore.

I suspect the problem here is in your first line of response. Since we are dealing with the optimizer, it's up to the optimizer to determine whether the use of the function-based index is of merit. That requires statistics on the table (and on the index, I'd suggest). It helps if the column being searched for is declared NOT NULL, too -since it has always been tricky persuading the optimizer that a function-based index on a nullable column references all rows in the table, but they got that right in 9i. And if the thing being selected for represents more than about 5% of the rows in the table, the optimizer is unlikely to use an index at all, of whatever sort. And if there are only (say) 50 rows in the table to begin with, we're in for an efficient full table scan come what may.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:blqR7.32893$ER5.374350_at_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

> 1

>

> 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)

>

>