RE: Rebuild Index?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 28 Feb 2014 13:04:17 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE091E_at_exmbx05.thus.corp>

>> "I know this effect was discarded as a myth but then why this:"

No, that case was repeatedly identified as one of the special cases that needed to be recognised and understood.

You'll also notice that the word "coalesce" appears in that document title, not "rebuild" - that's because the people who understood how indexes worked also understood why the FIFO was a special case and how best to address it ... and said so, many times. (Actually, "shrink space compact" seems to be more efficient than "coalesce" in recent versions - but the whole AQ thing introduces some funny effects around the edges anyway.)

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Laimutis.Nedzinskas_at_seb.lt [Laimutis.Nedzinskas_at_seb.lt] Sent: 28 February 2014 12:54
To: oracle-l_at_freelists.org
Subject: Re: Rebuild Index?

index rebuild is part of oracle queues houskeeping.

under some circumstances (example, table as FIFO queue, ever growing ID/timestamp, inserts of new records, delete of old records) indexes tend to expand to unrealistic sizes. I know this effect was discarded as a myth but then why this:

"Procedure to manually Coalesce all the IOTs/indexes Associated with Advanced Queueing tables to maintain Enqueue/Dequeue performance, reduce QMON CPU usage and Redo generation (Doc ID 271855.1)"

Bottom line:
a particular b-tree index implementation might not be intended for queues. If you take time to read cs articles about b-tree implementations youl'll see there is nothing trivial about b-tree performance in concurrent environments. Oracle definetely made some practical assumptions: who deletes data in the database ? It's a very rear condition that data deletions are allowed. On the other hand, index update is almost delete and then insert. But update is rather random, normally. You might expect (half)empty index blocks will be reused. FIFO insert/delete is another beast.

Brgds, Laimis N

---

Please consider the environment before printing this e-mail

[Inactive hide details for Karth Panchan ---2014.02.28 14:16:12---Everyone We have table with lot of insert/update/delete every]Karth Panchan ---2014.02.28 14:16:12---Everyone We have table with lot of insert/update/delete every day. High OLTP application in 11g R2

From:

Karth Panchan <keyantech_at_gmail.com>

To:

"oracle-l_at_freelists.org" <oracle-l_at_freelists.org>

Date:

2014.02.28 14:16

Subject:

Rebuild Index?

---

Everyone

We have table with lot of insert/update/delete every day. High OLTP application in 11g R2 on Linux.

To gain performance my teammate recommend Rebuild index.

His assumption Index size should be less than table size. Other wise we need to rebuild index.

Is that correct? Want get your thoughts.

I am skeptic after Reading Asktom and Jonathan Lewis blog. Both of them claim need good metrics and it is last option.

Thanks
Karth

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









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

Received on Fri Feb 28 2014 - 14:04:17 CET