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 management

Re: Index management

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 11 May 2004 09:28:03 +1000
Message-ID: <40a00ff7$0$24919$afc38c87@news.optusnet.com.au>


Brian Peasland wrote:

> I would disagree with you here. The empty leaf nodes on the left-hand
> edge of the index cannot receive new inserts because those inserts will
> always be on the right hand side!

Excuse me??!

Indexes don't have "sides". They have blocks. And those blocks are wherever Oracle happens to source them. And if you delete all your rows from the table with sequence number 0 to 10,000 you have just emptied a lot of blocks. And those blocks are available for the next insert. Even if the next insert is of sequence number 5,000,000.

This is *very* basic stuff.

>I'll draw a crude diagram to
> illustrate my point.

I wouldn't bother.

Really. It's not difficult. If you delete employee 456, and employee 982, then of course employee 5,000,001 can't use those slots.

But if you delete all employees from 0 to 10,000 then employee 5,000,001 will quite happily use an earlier empty block, because it is now COMPLETELY EMPTY AND THUS HAS NO POSITIONAL SIGNIFICANCE IN THE INDEX.
> Given enough time, you can have a tree structure that starts to look
> similar to the following:
>
>
> |
> ---------------------------------------
> | |
> -------------------- ---------------
> | | | |
> ---------------- ---------------- ----------------
> -----------
> | | | | | | | | | | | | | |
> |
> 1009 1010 1011 1012 1013 1014
> 1015

No, you can't Brian.

I have no intention of doing ASCII artwork, but no you can't.

A block ceases to have any positional significance in the index tree structure when it no longer has any leaf entries in it at all.

And an index such as you draw here with a nice lot of empty nodes between the left-hand edge and the 1009 can therefore accomodate the next entry for 1016 in the LEFT-HAND-MOST leaf node.

Because left- and right-handness have no physical significance.

> The entire left side of the tree is unnecessarily contributing to the
> height of the index.

But it's usable space.

> This same concept gets even worse for something like invoices. The
> invoice numbers are generated by a sequence. All new invoices will get
> added to the right side of the tree.

>What makes this worse is that
> companies tend to remove the oldest invoices all at once.

Which is GOOD NEWS. Because it means vast swathes of their index are emptied AND ARE THUS RE-USABLE.

Even with a monotonically incrementing sequence number for the invoice number.

I can't believe you don't know this. You're just having fun, aren't you?

  For instance,
> the company may decide to remove all of last year's invoices (or older)
> from the table. In that case, there won't be a sparse look to the tree
> like I indicated in my 3rd diagram.
>
>
> Looking at these examples, how is it that those empty blocks can now
> receive the next inserts from your monotonically incrementing sequence
> number"?

Oh for gawd's sake. Please find out how indexes work.

The clue is in the words "empty blocks". What do you think PCTUSED of zero means for an index block, exactly?

Mike Ault's stunning amount of ignorance on the subject is just about bearable. But I refuse to add any more to my plate, especially when you should know better.

HJR Received on Mon May 10 2004 - 18:28:03 CDT

Original text of this message

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