Re: Suggestions for first exam ...

On Sat, 27 Sep 2003 13:56:39 GMT, "Richard Foote" <richard.foote_at_bigpond.com> wrote:

>Sybrand, for you to have 5G of space "wasted" in an index, you must have
>deleted 5G of data. Not only that, but for the space to be truly wasted, you
>must have no intention to re-inserting data again such that the deleted
>space can be re-used (as it can be, another common myth if one reads and
>believes John Weeg articles). That being the case, if you have 5G sitting in
>an index, with no subsequent insert likely to reuse that space soon, (hence
>meeting one of the two scenarios above) then yes, the index could and
>possible should be rebuilt (your index scans could possibly appreciate it
>for a start).

That is exactly the situation. We're talking about data with a time dimension here. In fact I don't know better it applies to any table where you have, say, the last 3 year online, you insert data for the most recent period, and the oldest data is deleted on closure of the most recent period. If you run analyze index validate structure, you will always observer a low volume of used_space as opposed to the total b-tree space. I've never said the b-tree was unbalanced. It is balanced, but obviously Oracle *makes* it balanced. The 5G involved was also 5G for a bunch of indexes. With customers in the Netherlands, one can easily negotiate for a year on hardware restructuring, as usually the order has to be signed off by several bean-counters. And no, I'm not responsible for the negiotating, I only make overtime when the customer runs out of space.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Sep 27 2003 - 12:05:02 CDT