What does DBA_SEGMENTS report for the index segment sizing (in bytes/MB ? )
for the different types ?
"Tuning" <c_armanet_at_hotmail.com> wrote in message
news:ah6ccj$1ph$1_at_wanadoo.fr...
> Amount of buffer_gets is 0 for both queries, both indexes! (from
v$slqarea)
>
> ENT_OF_SAL_FK : clustering_factor = 32633, leaf_blocks = 9118, when
normal
> clustering_factor = 240270, leaf_blocks
=
> 955, when bitmap
>
> VIL_OF_SAL_FK : clustering_factor = 28062, leaf_blocks = 8239, when normal
> clustering_factor = 234, leaf_blocks
=
> 145, when bitmap
>
> So, VIL_OF_SAL_FK should be more efficient, doesn't it?
>
> Thanks to both of you, Telemachus and Herman, for your interest :-)
> --
> 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:
> ah6a3o$j2$1_at_news1.xs4all.nl...
> > Hello Tuning,
> >
> > i think that the table salarie is physically well clustered for
> > enterprise_id, but bad for ville_id. This leads to a good compression
> > for the first one, and a worse for the latter.
> >
> > The clustering_factor of index_stats (view filled after analyze index
> > .. validate structure) must show this.
> >
> > Are both bitmap indexes on single columns?
> > Can you post the amount of logical reads, for both queries?
> >
> >
> > Kind Regards,
> >
> > Herman de Boer
> > sr consultant
> > IT Consultancy Group bv
> >
> > Tuning wrote:
> >
> > >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.
> >
> >
>
>
Received on Thu Jul 18 2002 - 07:39:22 CDT
