SAP BASIS Consultant wrote:
It depends.
The current consensus is that the cost outweighs the benefits.
Hello,
>
>
Last year, on an 8i/AIX database, I remember reducing the size of some
indexes (Default B-Tree) by some 20% or more, and improving query
times fairly significantly, although the indexes, generally, had few
deleted leaf nodes (Some 3% or fewer), and an index height of 3. Thus,
theoretically, they were good indexes (My stats. were very recent),
but they still benefited from a rebuild.
>
Is there any information (For 8i or 9i) as to the extent to which
indexes can *generally* be reduced in size, given the deleted leaf
node/leaf node ratio, height, PCTFREE and/or other settings....
>
Yes, I know, that such information will not be applicable to every
situation, etc.., but I am curious as to whether anybody has studied
the issue in detail.
>
>
Last year, on an 8i/AIX database, I remember reducing the size of some
indexes (Default B-Tree) by some 20% or more, and improving query
times fairly significantly, although the indexes, generally, had few
deleted leaf nodes (Some 3% or fewer), and an index height of 3. Thus,
theoretically, they were good indexes (My stats. were very recent),
but they still benefited from a rebuild.
>
Is there any information (For 8i or 9i) as to the extent to which
indexes can *generally* be reduced in size, given the deleted leaf
node/leaf node ratio, height, PCTFREE and/or other settings....
>
Yes, I know, that such information will not be applicable to every
situation, etc.., but I am curious as to whether anybody has studied
the issue in detail.
The current consensus is that the cost outweighs the benefits.