Actually if you rebuild the index with the compress option version i is very
good. The compress option (assuming 8.1.6 at least) will compress repeated
values in a key. This can have great space savings and thus speed up the
usefullness of the index.
Jim
"David Franit" <dfranit_at_hotmail.com> wrote in message
news:e3b48e1f.0110301354.97a7a16_at_posting.google.com...
> Hello,
>
>
> In Oracle/SAP systems many of the default indexes are setup such
> that the rows with low selectivity form the first columns of a composite
> index. Frequently,this matches the primary key. For example,
>
> Table ABC has 2,000,000 rows.
>
> Column #X has 3 distinct values.
> Column #Y has 30 distinct values.
> Column #Z has 10000 distinct values.
>
> (i) A default index on table ABC might contain column X, followed by
column Y
> and column Z.
>
> (ii) If somebody were to create a new index, they would like create one
> with column Z followed by column Y, and, probably, leave out column X.
>
> To what extent will the index given in case in case (ii) be preferable
> to that of case #(i)?
>
> Assume that the main select statement on table ABC is
> governed by ' WHERE X = .. and Y = ... and Z = ...' and that the
> statement is one of the most heavily used SELECT statements in the
> system. Also, assume poor clustering and that we cannot change index
> #(i), but that we can create index #(ii).
>
> I realize that that index #(ii) is preferable to that of #(i), but I
> am not sure whether or not it will greatly improve the performance
> of the SELECT statement.
>
> Any information or data based on actual experience would be greatly
> appreciated.
>
>
>
>
> Thanks,
> D. Franit
Received on Tue Oct 30 2001 - 18:41:49 CST
