| Oracle Coalesce vs Index Rebuild [message #487687] |
Thu, 30 December 2010 01:11  |
paradoxkhan
Messages: 92
Registered: April 2008
Location: Pakistan
|
Member |
|
|
Hello,
We have large tables 60-70 GB having 120 million records. We have
to perform index rebuild frequently which takes significant time to complete and effects database performance too.
Please tell me how we can use index Coalesce? what are its benefits, coalesce results in performance gain?
Thanks
Yasir Aftab
|
|
|
|
|
|
|
|
| Re: Oracle Coalesce vs Index Rebuild [message #487701 is a reply to message #487695] |
Thu, 30 December 2010 03:03  |
paradoxkhan
Messages: 92
Registered: April 2008
Location: Pakistan
|
Member |
|
|
We rebuild indexes because daily about 3 millions of records are deleted from that particular table and weekly 6 millions records are inserted.
So please explain in this scenario index rebuild is required or not? Seconds thing after index rebuild Table stat are required to collect?
Thanks,
Yasir
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Coalesce vs Index Rebuild [message #487921 is a reply to message #487708] |
Mon, 03 January 2011 06:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If the indexes are on columns where the values deleted are similarly distributed to the values inserted, then you will find the inserted values eat up the space left by the deleted ones. These indexes do not need to be rebuilt.
That leaves indexes where the inserted vales are distributed differently to the deleted ones. This can happen with IDs allocated from a SEQUENCE (values always increasing) or with dates (also always increasing). For indexes that are always accessed with EQUALS clauses (often the case with IDs allocated from a sequence), you can create the index as a REVERSE KEY index. This will have the effect of homogenising the distribution of values. i.e. The inserted rows will eat up the space left by the deleted values.
That leaves DATE-based indexes: they are frequently accessed with range clauses like > / < / BETWEEN. You cannot make these REVERSE KEY because the range predicates will stop working. It is reasonable to rebuild or coalesce these indexes: BUT ONLY TO RECOUP SPACE, NOT FOR PERFORMANCE. When you delete a lot of old date values, it will leave those blocks in the index empty until the next rebuild/coalesce. It would be slow to read those old blocks and find nothing, but that will only happen if you scan on old values (or full scan) which you probably hardly ever do. If you scan on new values more often, you will be hitting the full blocks and will not get any performance hit from the empty blocks.
So find those indexes used in RANGE predicates that have ever-increasing values (like DATES) and coalesce them every couple of months to get space back.
Ross Leishman
|
|
|
|
|
|
| Re: Oracle Coalesce vs Index Rebuild [message #487953 is a reply to message #487923] |
Mon, 03 January 2011 15:54 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Michel, I was not aware that empty index blocks could be reused for values outside the range for which they were originally populated. I was of the understanding that they would lie empty waiting for new values in the same range.
I don't recall why I have this belief, so I concede that I could be very much mistaken.
I could probably search it or even benchmark it myself, but if you have a link to the manual handy that descibes the process, I would be appreciative.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
