Re: index use

That was the answer : I rebuilt SALARIE table, and re-analyze table and indexes, and it's OK : the optimizer chooses to use the index.

Thanks a lot Herman!

Regards.

--
Celine Armanet - Division ORACLE
EPICENTRE - 529 Route des Vernes 74370 PRINGY
carmanet_at_epicentre.fr
Tel. : +33 04 50 09 7000
"Herman de Boer" <h.de.boer_at_itcg.nl> a écrit dans le message news:
ah5vo8$7gc$1_at_news1.xs4all.nl...


> Hello Jack,

>

> my guess is that one of the columns enterprise_id and salarie_id has

> been defined as nullable in the data dictionary. Btree indexes do not

> store entries which have all column values NULL, whereas bitmap

> indexes do. Therefore, the query might return an incomplete answer if

> it used the btree index as its access path.

>

> Kind Regards,

>

> Herman de Boer

> sr consultant

> IT Consutancy Group bv

>

> Tuning wrote:

>

> >Hello,

> >

> >I've strange reactions when trying to use an index for a query,

> >to compare different explain plans (Oracle 8.1.7) :

> >

> >I've a test DB :

> >    - ENTREPRISE table (300,000 records)

> >    - SALARIE table (about 4,000,000 records), with

ENTREPRISE_OF_SALARIE_FK


> >index (normal)

> >                (about 240,000 distinct values in field entreprise_id for

> >the SALARIE table)

> >

> >I know that this index has a poor selectivity, but in order to compare

> >explain plans,

> >for the query :

> >

> >    select entreprise_id, count(salarie_id) from salarie group by

> >entreprise_id

> >

> >the explain plan gives me a FTS on ENTREPRISE (which is normal, due to

the


> >poor selectivity of the index),

> >but even with hint /*+index(salarie entreprise_of_salarie_fk)*/ ?!

> >(and I can't test with /*+first_rows*/, due to the GROUP BY clause)

> >

> >Q1 : Why does Oracle not want to use this index when asking it to do so?

> >(all tables and indexes are analyzed with compute option)

> >

> >Then, I dropped this index and rebuilt it in a bitmap index (I know : too

> >much distinct values for a bitmap index!)

> >After analyzing it, the same query uses this index!!

> >

> >Q2 : What do you think about this scenario?

> >        And what could be the solution for optimizing access to such a

> >column (entreprise_id), index or not?

> >

> >Thanks for your interest :-)

> >

> >Cordially,

> >

> >Jack.

> >

> >

> >

> >

>