Re: index use

Size....

How big is the bm index... and how big is the fk index.... in blocks

if it can satisfy the request by scanning the smaller index ... then why not ?

just don't subject the table to lots of inserts/deletes (bad for bitmap indexes)

there are arguments that say that a BMI can be useful even up to relatively high selectivity in DW/DM

the mathematics of this are covered in papers and books out there

but they are all agreed that there are bad effects from inserts due to the amount of fine-work oracle must do to keep the BMI up to date

As you go along this road, please don't forget that normal indexes can be compressed and the results can be very pleasantly surprising if there is any duplication ...

"Tuning" <c_armanet_at_hotmail.com> wrote in message news:ah62q7$ksj$1_at_wanadoo.fr...
> 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)
>
> 1. For the first query, index vil_of_sal_fk :
>
> 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...
> > 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 - 06:05:00 CDT