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: Herman de Boer <h.de.boer_at_itcg.nl>
Date: Thu, 18 Jul 2002 11:44:59 GMT
Message-ID: <ah6a3o$j2$1@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 - 06:44:59 CDT

Original text of this message

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