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: Tuning <c_armanet_at_hotmail.com>
Date: Thu, 18 Jul 2002 15:28:48 +0200
Message-ID: <ah6fp2$t60$1@wanadoo.fr>


ENT_OF_SAL_FK is about 9152 blocks (8K per block) in normal, and 960 in bitmap,
and VIL_OF_SAL_FK about 8272 blocks in normal, and 160 in bitmap.

Do you know good internet sites which papers about mathematics on bitmap indexes?

Thank Telemachus!

--
Celine Armanet - Division ORACLE
EPICENTRE - 529 Route des Vernes 74370 PRINGY
carmanet_at_epicentre.fr
Tel. : +33 04 50 09 7000
"Telemachus" <telemachus_at_ulysseswillreturn.net> a écrit dans le message
news: _FyZ8.3102$zX3.2235_at_news.indigo.ie...

> 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 - 08:28:48 CDT

Original text of this message

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