index use

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. Received on Thu Jul 18 2002 - 03:24:23 CDT