RE: Index rebuilding

Richard,

        As I said earlier, we can agree to disagree. Until I have evidence to the contrary we can leave it at that.=20

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Richard Foote [mailto:richard.foote_at_bigpond.com]=20 Sent: Tuesday, November 16, 2004 9:32 AM To: oracle-l_at_freelists.org
Subject: Re: Index rebuilding

Hi Dick

Comments embedded.

>
> By Unbalanced I mean an index having more leaf blocks on either
> the right or left side than the other. This therefore leads to more
> blocks being accessed as one heads in one direction than the other. I
> see this fairly regularly with tables that have millions of rows of
data
> that are constantly increasing in row count in one particular
direction.
> Basically it's an index on date where each new record added has
sysdate
> as it's value.

The above is not what I consider to be an unbalanced index. However, again=20
look at my presentation and you'll see the above is simply not true. Even=20
indexes with monotonically increasing values remain balanced, even by your=20
definition of unbalanced. The root block happily points to all it's branch=20
blocks (if they exist) and sits "in the middle" so to speak of the index

structure. The branch blocks point to their branch/leaf blocks and sit in=20
the middle of their respective blocks. There is no more or less blocks to=20
the left or right of the index per se. If an index accesses more leaf=20 blocks, it's because it's interested in the range of index entries they=20 contain, pure and simple. If entries are deleted and Oracle is forced to

read *many* empty/almost empty leaf blocks then that could present an issue=20
as discussed in the presentation.

> It appears that Oracle has some kind of 60-40 rule built
> into it such that if an index is less than 40% out of balance (as I've
> stated above) then it will do little if anything at the current moment
&
> wait till it gets more free time before correcting the problem.

This again is simply not true. There is no such rule. What on earth would=20
Oracle do on the fly, how would Oracle determine that such a problem exists,=20
what is the problem ? Read my presentation again, see how index entries are=20
added, see how space is re-claimed, see how there is no such problem.

> That squares with Oracle's write less philosophy & I can agree with
that.
> The problem comes where the table in question has hundreds of millions
> of rows of data at which 40% can become a rather large number. What
> brings me to believe this is two things:

What is this 40% out of balance metric ? How can an index be 40% out of=20 balance ? This is all simply not true. Do you have one shred of evidence to=20
support such a claim ?

This 100s of millions of rows argument. If you have millions of rows but you=20
commonly access only a handful, what does it matter if an index were=20 unbalanced by your definition ? You'll still likely only access a leaf block=20
or two, will that really translate to a poor response time.? And if you=20 access 100,000s of rows, your index reads will likely be dwarfed by the=20 100,000s of data/table blocks you'll need to access. Again, the presentation=20
tries to explain that the cost of reading the index is generally relatively=20
small when compared to the cost associated with reading the parent table,=20
even with massive tables. Therefore reducing the costs of the index reads=20
may only have a trivial impact on the overall response times.

>
> 1) the explain plan for data that is "history" (greater than 60
> days old) is different from one that accesses current data where the
> data is within say the last 10 days. In the first case an "index
range
> scan" is used where as in the former it will defer to a "full table
> scan". Now that is a bit fishy, but it gets even more interesting.
OK,
> so the optimizer likes full table scans, then take the table away &
see
> what happens. Easy since the data table & index are in separate
> tablespaces, take the data tablespaces off line for a bit. Again a
> simple "select test_date, count(*)" will suffice. Well If I'm looking
> for historical data the explain plan uses the "Index range scan", but
> current data utilizes a "fast full index scan". ODD at best.
Possibly
> the histograms hold a clue? Well yes they do, they are distinctly
> unbalanced (think of a scale or balance beam here) with the buckets
for
> the current data being heavier than the those for historical data.
> Maybe your presentation covers this condition, if so I missed it.
BTW:
> give the database a quite weekend, like our Labor Day weekend 3 nice
> quite days of just sitting there doing nothing, and things change to a
> more equal representation. Namely an index range scan is used for
> historical and recent data. Therefore I believe that Oracle tolerates
> an unbalanced index, until it has either exceeded some internal limit
or
> there is sufficient time to clear the problem.

The only thing missing from the above story is a "Once upon a time in an

Oracle database far far away ..." at the start followed by " ... and the

indexes lived happily ever after." at the end :) I would dearly love to see=20
how Oracle on a public holiday suddenly rebalances it's indexes ...

The access path of a query is determined by the various costs associated

with the various execution plans that the optimizer considers, pure and=20 simple. And yes, histograms can play a part in why a plan may differ for the=20
same query referencing different data sets. But suggesting that somehow=20 Oracle tolerates an "unbalanced" index until some quiet period when it'll=20
wave a magic wand and fix things up is, how can I say it, wrong.

>
> 2) the second thing that leads me to this belief is a pile of
> dumps of index data, similar although not identical to yours, that
I've
> done over time to try and figure the above out. Again it appears from
> the dumps, sorry I can't post them their big enough to give our local
> mail server a fit, that index blocks can be added to one side while
the
> height of the index does not increase.

Well yes, if you mean that Oracle only inserts into the "right-most" leaf=20
node, then yes, as my presentation describes, only the right most index node=20
splits via 90-10 splits. But the index still remains balanced ...

>
> As for real evidence of that I believe I can come up with that
> in a couple of weeks when we have maintenance on the server in mind
> scheduled. Computers don't understand holidays you know, even if I
do.

I would be very very interested to see evidence of this "phenomenon". We

must have pretty lazy Oracle indexes here in Australia because during public=20
holidays our indexes do damn all and must sit there smoking cigars and=20 playing cards all day. Perhaps it's a union thing ?

>
> On your third point, I'll have a serious look again at your
> presentation on block reuse. In Oracle 6 and 7 it was totally
possible
> & actually did happen, that blocks of index data in a highly
> transactional index (lots of inserts, update, delete) would empty out
> and the dbms would branch around them leaving them as "leaf blocks"
that
> were not counted and not free. Basically they were zombies. You
could
> see it happening as an index that had rather steady growth over time
all
> of a sudden started growing at a logarithmic rate. I was rather
"upset"
> with OTS's explanation that "this is Impossible" that I went through a
> lot of work similar to yours to prove them wrong. Finally in 7 I was
> able to get OTS to admit that the possibility did exist and that they
> had finally verified my test case. Lets just say it was one royal
pain
> in the $%%. I have not seen anything like this since Oracle 8.0
> therefore I believe the problem was addressed & fixed. That may well
be
> the basis for your contention & I'll yield on the subject.
>

My "studies" only began with 7.2 as I'm only a young little thing (btw, Pete=20
Sharman will back me up here :) Most of the examples in the presentation

were performed on both AIX Unix and Windows 2000 on both 8.1.7.4 and=20 9.2.0.4. so I admit that I didn't consider ancient bugs and inconsistencies.=20
I however see little point in clouding today's waters with how things may=20
have worked 10 years ago. Hopefully these "zombies" are now long dead (sorry=20
couldn't help it). I'll have a "serious look" at things if you can provide=20
evidence to the contrary to what's in the presentation on space reuse on any=20
currently supported version of Oracle.

If you can produce any actual evidence to support any of these theories of=20
yours, I would very much be interested to see it. Until then, I'll just=20 remain quietly sceptical ...

Cheers

Richard=20

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l