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

|------------>

| From: |
|------------>

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |Karth Panchan <keyantech_at_gmail.com>                                                                                                               |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|


|------------>

| To:        |

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


|------------>

| Date:      |

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |2014.02.28 14:16                                                                                                                                  |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |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