Re: how to use function-based indexes?

Cristian

some more questions for you.

how many rows does your query return?
how many blocks in the table?
have you analyzed the table (sorry can't find if you've posted this info or not).

And some asides.

thanks to Tom for the suggestion about utilising functions which return null for selective indexing - a nice idea that one. if this is performance critical - I read your posts as just investigation - you could of course hint the statement.

--
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:3C17174E.922A7642_at_crpa.it...
Thomas Kyte wrote:



> also to use function based indexes -- we HAVE to use CBO.  RBO doesn't

"see"


> them.  I'd suspect here they are using rbo.


I would like to thank Jim, Howard and Thomas for their answers.

Some more details:

- table ACQUIRENTI has 10904 rows

- I tried to issue the following command in order to force optimizer to
use CBO:

SQL> ALTER SESSION SET OPTIMIZER_MODE = all_rows;

... but nothing changed. Moreover, I found the following note in the
online doc "Oracle9i Database Performance Guide and Reference":

"Note: Even if the parameter OPTIMIZER_MODE is set to RULE, the use of
these features [Partitioned tables and indexes, Function-based indexes
etc.] enables the CBO."

- I tried to set column ACQUIRENTE_RAG_SOC to NOT NULL and I tried to
match the function case: nothing changed.

Thanks again for your help.

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)