Re: Suggestions for first exam ...

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 27 Sep 2003 10:32:31 +1000
Message-ID: <3f74db61$0$28040$afc38c87@news.optusnet.com.au>

Sybrand Bakker wrote:

> On Fri, 26 Sep 2003 17:41:47 -0400, "Thomas T" <T_at_T> wrote:
>

>>And from what I learned here, rebuilding an index
>>will just make the database work harder to attain it's natural "size".  So
>>any space regained is temporary until the database irons itself back out.

>
> Not true. In fact the impact of coalesce is minimal. However, an index
> will automatically skew, especially of you only insert data to only 1
> end of the index. Deleted rows or not, Rebuild is the only option to
> get the tree rebuild, balanced, and reduced in size. And yes, it will
> start to grow again, but that is the result of introducing skewed
> data.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

There really is a lot of either (a) nonsense or (b) loose talk made about indexes.

You do *not* need to rebuild an index to 'get the tree ... balanced', because Oracle's indexes are *always* balanced. In real time, every time.

An index that only receives entries at one end of it is *never* "skewed", if by that you mean un-balanced, but merely has a contention issue (OK, a massive potential contention issue).Not true. In fact the impact of coalesce is minimal. However, an index
will automatically skew, especially of you only insert data to only 1 end of the index. Deleted rows or not, Rebuild is the only option to get the tree rebuild, balanced, and reduced in size. And yes, it will start to grow again, but that is the result of introducing skewed

As to the original poster, the OCP questions are often nothing if not ambiguous. Putting it a bit simplistically: If you have 10 half-empty leaf nodes, a rebuild will give you a 5-leaf node index. And a coalesce will give you 5 full leaf nodes and 5 completely empty leaf nodes. So which one has 'regained space' for you?

Well, both of them in fact. Your 5 empty leaf nodes after a coalesce can now be used for the insertion of new leaf entries, whereas before you might have had to acquire an eleventh node to accomodate them. That's 'reclaimed space' in my book.

But the index is still 10 leaf nodes big, so have you freed up space that some *other* segment can make use of? No, obviously not. Only a rebuild can do that. But the question as you quote it is a terrible indictment of someone's ability to write clear English. As far as I'm concerned, the coalesce really does 'regain space *for an index*'. It doesn't free it up for *something else*, that's all.

Guess why plenty of people's opinion of OCP is, er, shall we say, 'quite low'?

Regards
HJR Received on Fri Sep 26 2003 - 19:32:31 CDT