Re: Rebuild Index?
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 28 Feb 2014 14:54:37 +0200
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@xxxxxxxxx>
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
