Re: rebuilding indexes - sure to cause a ruckus

Hi!

Yes Yong I agree with you, that rebuilding may be beneficial in some cases, especially some tables/indexes become either logically or physically read only (btw, I was not speaking about coalescing in my post, it's a different story anyway).

But the point I wanted to make is that single query's speed (LIO amount) right after rebuilding index doesn't usually give us enough information to determine whether overall system speed will go better in long term. When you have rebuilt an index, it becomes more compact, causing more recursive operations for block splits and perhaps index height changes in the future. Also, in heavily loaded 24x7 environments with no real low-peak time, the additional CPU, IO and brief exclusive lock usage may cause more harm than it gives benefit. And often this benefit is only short term in regular OLTP systems.

But in some cases, as when you've deleted a number of rows from your table or done a lot of updates and you never expect these keys to be back in the index, a rebuild can be justified.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, December 05, 2003 10:29 PM

> Tanel,
>
> I think you're saying a query almost always runs faster right after the
index
> rebuild and there's no point in finding the criterion whether to rebuild
an
> index. (What is "42"?)
>
> Some time ago I posted a message somewhere else showing a case where
rebuilding
> or coalescing an index may be benefitial. A data warehouse is found to
have
> some data errors. Deletes and updates are done. Then the database goes to
> mostly read-only again, and will last for a month or quarter. Then
shrinking
> frequently used B*Tree indexes is a good idea. Now I'd like to add one
more
> criterion as a result of reading Jonathan Lewis' dbazine article and email
with
> him (errors are mine): the index is full scanned, or if range scanned or
unique
> scanned, the index selectivity has to be fairly low (but not too low for
the
> index to be ignored by CBO).
>
> In a typical working environment, a data warehouse does have plenty of
> relatively quiet period. I worked on a monthly data load project at an
> insurance company. I remember we rebuilt a partitioned IOT (one partition
at a
> time) and fast full index scan (certain partitions) did run faster.
>
> There're some errors in Don Burleson's dbazine article (e.g. pct_used in
> dba_indexes) and Mike Hordila's Oramag article (structurally unbalanced
index).
> But one thing alluded to in there is important: study Oracle performance
> problems as scientific research. You said setting _wait_for_sync to false
> improves performance. That's a fact. We can only explain and analyze it
but not
> deny it. Similarly, when Mike says queries run 10 to 50% faster after
index
> rebuild, we can't deny unless we find his measurement is wrong. Wouldn't
it be
> nice if Oracle researchers write articles with sections like Abstract -
> Experimental - Results - Discussion in that order?
>
> Yong Huang
>
> Tanel Poder wrote:
>
> There's no point of arguing about whether a query ran faster right after
you
> rebuilt your index. Nor there is no point in finding some ultimate
algorithm
> for finding the point of index rebuilding, we all know the answer - it's
> "42".
>
> Instead, a long stress test has to be done, e.g. running 10 millions of
> continous transactions and queries (simulating real life). Do one 10M
> without rebuilding indexes in the meantime, measure total execution time,
IO
> amount, CPU usage, segment sizes etc.
>
> Then restore your database back to starting point and do the same test
again
> with regular index rebuilds during the operations (online or taking
"users"
> offline, depending on environment type). And then measure the same
> statistics, especially total execution time. Note, that statistics and
time
> also for rebuilding indexes should be accounted in totals, because in real
> life they don't just disappear somewhere as in some simple-minded tests.
>
> Tanel.
>
> __________________________________
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
> http://companion.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Yong Huang
> INET: yong321_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).