Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index use

Re: index use

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: Thu, 18 Jul 2002 08:47:45 GMT
Message-ID: <ah5vo8$7gc$1@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.
>
>
>
>
Received on Thu Jul 18 2002 - 03:47:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US