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:10:51 +0200
Message-ID: <ah6enc$5tj$1@wanadoo.fr>


I re-built the VIL_OF_SAL_FK in bitmap, re-analyze, and now it's seams to be good
(at least as I expected it would be!) : cost in explain plan for the first query fell to 160!!
I didn't understand why this changed, but I keep searching...

Thanks againnnn ;-)

--
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:10:51 CDT

Original text of this message

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