| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index use
I think you could help me again, with this "study case" (always Oracle
8.1.7):
As entreprise_id (240270 distinct keys), there is a ville_id (90 distinct keys) column in SALARIE table (4,226,250 rows), and for the 2 queries :
select ville_id, count(salarie_id) from salarie group by ville_id select entreprise_id, count(salarie_id) from salarie group by entreprise_id
the explain plans with FTS and both normal and bitmap indexes are the
following :
(all table and indexes full analyzed)
Operation Object Name Rows
Bytes Cost
SELECT STATEMENT 90
38991
SORT GROUP BY 90
180 38991
TABLE ACCESS FULL SALARIE 4 M 8 M
4232
Operation Object Name Rows
Bytes Cost
SELECT STATEMENT 90
8241
SORT GROUP BY NOSORT 90
180 8241
INDEX FULL SCAN VIL_OF_SAL_FK 4 M 8 M
8241
Operation Object Name
Rows Bytes Cost
SELECT STATEMENT
90 3966,6
SORT GROUP BY NOSORT 90
180 3966,6
BITMAP CONVERSION COUNT
BITMAP INDEX FULL SCAN VIL_OF_SAL_FK
2. For the second query, index ent_of_sal_fk :
entreprise_of_salarie (240270 valeurs distinctes)
Operation Object Name
Rows Bytes Cost
SELECT STATEMENT 112
K 44338
SORT GROUP BY
112 K 441 K 44338
TABLE ACCESS FULL SALARIE 4 M
16 M 4232
Operation Object Name
Rows Bytes Cost
SELECT STATEMENT 112
K 9120
SORT GROUP BY NOSORT 112 K
441 K 9120
INDEX FULL SCAN ENT_OF_SAL_FK 4 M 16 M
9120
Operation Object Name
Rows Bytes Cost
SELECT STATEMENT 112
K 1052,7
SORT GROUP BY NOSORT 112 K
441 K 1052,7
BITMAP CONVERSION COUNT
BITMAP INDEX FULL SCAN ENT_OF_SAL_FK
My question is : why in this case the ENT_OF_SAL_FK bitmap index is more efficient (best throughput, and best response time!) than VIL_OF_SAL_FK, whereas ville_id has much less distinct values, and is, by this way, a more efficient bitmap index, isn't it ???!!!
Thanks again!
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...Received on Thu Jul 18 2002 - 04:47:35 CDT
> 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.
> >
> >
> >
> >
>
 |
 |