This discussion is archived
1 2 3 11 Previous Next 163 Replies Latest reply: Oct 22, 2009 3:23 PM by Hoek RSS

Index rebuild

715292 Newbie
Currently Being Moderated
Hi Guys,

I have my database on Oracle 10gR2. Now my schema has several indexes and the table, on which indexes are created are updated frequently. So do you guys think that I should manually rebuild index over a period of time ( may be every weekend ) , so that indexes are correct and performance is improved or it will be taken care by oracle.

Thanks,
Vinod.

Edited by: vinodshah on Sep 25, 2009 5:54 PM
  • 1. Re: Index rebuild
    591186 Guru
    Currently Being Moderated
    Check out this threads:
    When to rebuild indexes
    [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112]



    -Anantha
  • 2. Re: Index rebuild
    314801 Journeyer
    Currently Being Moderated
    Hi,

    in opposite to earlier oracle releases rebuilding indexes in 10g is in most cases not neccessary and a waste of ressources.

    For more information check out this good presentation: http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf




    --
    Ronny Egner
    My Blog: http://ronnyegner.wordpress.com

    Edited by: Ronny Egner on Sep 25, 2009 2:32 PM
  • 3. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Vinod,
    indexes are created are updated frequently.
    BEWARE - It's a myth that heavily updated indexes never need rebuilding or coalescing.

    An index rebuild detector is included in the OEM segment advisor . . .

    Jonathan Lewis has these notes on fragmentation issues with high-update indexes:

    http://jonathanlewis.wordpress.com/2009/08/11/index-explosion-2/

    He notes "But the bottom line is this – if you’ve got hot spots in indexes that are subject to a lot of concurrent DML then you can find yourself wasting space unnecessarily in that area of the index."

    Note that he recommends doing a coalsece over a rebuild . . .
    So do you guys think that I should manually rebuild index over a period of time ( may be every weekend )
    Done properly, an index rebuild is 100% safe. I've done thousands of them, never had a problem.
    so that indexes are correct and performance is improved or it will be taken care by oracle.
    Index rebuilding only help the performance of index range scans and index full and fast-full scans.

    Standard index unique probes are not affected by rebuilding.

    See my notes here for more discussion of index rebuilding:

    http://www.dba-oracle.com/t_index_rebuilding_issues.htm

    Hope this helps . . .

    Donald K. Burleson
    Oracle Press author
    Author of "Oracle Tuning: The Definitive Reference"
    http://www.rampant-books.com/t_oracle_tuning_book.htm
    "Time flies like an arrow; Fruit flies like a banana".
  • 4. Re: Index rebuild
    6363 Guru
    Currently Being Moderated
    burleson wrote:
    Hi Vinod,
    indexes are created are updated frequently.
    BEWARE - It's a myth that heavily updated indexes never need rebuilding or coalescing.
    Agreed. It is also a myth that heavily updated indexes frequently require rebuilding.

    The fact is that heavily updated indexes almost never need rebuilding.

    http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf
  • 5. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    It is also a myth that heavily updated indexes frequently require rebuilding.
    Yeah, nothing is ever black-and-white. . . .
    heavily updated indexes almost never need rebuilding.
    See, that's black-and white!  It's also a rule of thumb, which has important exceptions!

    It depends on your client base.

    I suppose as a whole, "almost never" is OK as a rule-of-thumb, if you consider the zillions of people running tiny low-DML systems. . . .

    However, in the world of Fortune 500 Corporations, it is quite different.

    This is the world of high-speed OLTP and scientific apps, where indexes do indeed require "frequent" rebuilding or coalescing!

    I have a client with four freelist groups of 50 freelists and over 1,000 clericals, all hammering away, adding rows and deleting rows into a single table with 4 indexes.

    As joinathan notes, the fragmentation can be UGE, and frequent rebuild sare required, both for performance and storage.

    The same is true of scientific system like Clintrial, they need rebuilding, usually scheduled nightly . . .

    But overall, I agree that you have a good Rule of Thumb . . .
  • 6. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Ronny Egner wrote:

    in opposite to earlier oracle releases rebuilding indexes in 10g is in most cases not neccessary and a waste of ressources.
    Ronny,

    The internals of Oracle's B-tree indexes haven't really changed since Oracle 6 - and most index rebuilds have been unnecessary since then. The big change appeared with the coalesce command (Oracle 8, I think) which meant that many of the cases where a rebuild was actually necessary could be fixed with a coalesce instead of a rebuild.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 7. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    The internals of Oracle's B-tree indexes haven't really changed since Oracle 6
    Speaking of changes, what do you make of the new 11g data buffer algorithm, made exclusively for SSD blocks?

    http://dba-oracle.com/t_11g_flash_cache_changes_buffer_pool_management.htm

    "The Exadata Smart Flash Cache manages active data from regular disks in the Exadata cell – but it is not managed in a simple Least Recently Used (LRU) fashion.

    The Exadata Storage Server Software in cooperation with the Oracle Database keeps track of data access patterns and knows what and how to cache data and avoid polluting the cache.

    With flash cache, the database can write the body of a clean buffer to the flash cache, enabling reuse of its main memory buffer. The database keeps the buffer header in an LRU list in main memory to track the state and location of the buffer body in the flash cache. If this buffer is needed later, then the database can read it from the flash cache instead of from magnetic disk. "
  • 8. Re: Index rebuild
    6363 Guru
    Currently Being Moderated
    burleson wrote:
    It is also a myth that heavily updated indexes frequently require rebuilding.
    Yeah, nothing is ever black-and-white. . . .
    heavily updated indexes almost never need rebuilding.
    See, that's black-and white!  It's also a rule of thumb, which has important exceptions!
    Which is it, black and white or rule of thumb?

    >
    It depends on your client base.

    I suppose as a whole, "almost never" is OK as a rule-of-thumb, if you consider the zillions of people running tiny low-DML systems. . . .
    What I said was
    heavily updated indexes almost never need rebuilding.
    So this would exclude low DML systems however many of them are.

    >
    However, in the world of Fortune 500 Corporations, it is quite different.

    This is the world of high-speed OLTP and scientific apps, where indexes do indeed require "frequent" rebuilding or coalescing!
    So you say, but you never provide any evidence that this is the case despite a wealth of evidence showing that this is not the case, so let's just say I choose to not believe you.

    Re: Can DBA work at home?

    >
    The same is true of scientific system like Clintrial, they need rebuilding, usually scheduled nightly . . .
    You said this recently about SAP which seemed to be a mistake as many people are familiar with SAP, but when asked to give details of the index that needed rebuilding so your claims could be investigated and validated you didn't respond.

    Re: corrupted indexes

    I am sure if you can provide the requested details this whole misunderstanding whereby you see many more indexes that need rebuilding than anyone else can all be cleared up.

    Edited by: jkestely on Sep 25, 2009 4:46 PM
  • 9. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    whereby you see many more indexes that need rebuilding than anyone else
    That's a legitimate question.

    I work primarily with large real-world databases, systems with tens of thousands of users and hundreds of transactions per second.

    The folks publishing "proofs" that indexes rarely need rebuilding are using Oracle on a PC in a single-user scenario as justification . . .
  • 10. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >

    Jonathan Lewis has these notes on fragmentation issues with high-update indexes:
    http://jonathanlewis.wordpress.com/2009/08/11/index-explosion-2/

    He notes "But the bottom line is this – if you’ve got hot spots in indexes that are subject to a lot of concurrent DML then you can find yourself wasting space unnecessarily in that area of the index."

    *That's actually a quote from  [+*part 1 of the series*+|http://jonathanlewis.wordpress.com/2009/07/28/index-explosion/]. Note especially the wording "wasting space +*unnecessarily*"+ As I explain in part 2 of the series (the article you referenced) the space wastage is the result of a configuration error (combined with behaviour that may or may not be an Oracle bug, but has earned a bug note on Metalink).

    If anyone is interested, there's an index to the entire set of my "index ITL" postings [+*at this URL*+|http://jonathanlewis.wordpress.com/2009/09/19/index-itls/] > that walks through just about everything you might need to know about the impact of highly concurrent DML on index efficiency.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 11. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    you never provide any evidence that this is the case
    Just today we see this one, where the OP has scheduled a weekly index rebuild:

    Re: corrupted indexes


    *********************************************************************
    despite a wealth of evidence showing that this is not the case
    Just because someone show a case where indexes do not need to be rebuilt, that DOES NOT mean that positive cases don't exist!

    I can write a test case to "prove" that virtually any statement about Oracle is un-true.  It's easy.

    People who believe that a single negative test case proves something is wrong join the ranks of the "deniers", folks who cite "proof" that the moon landing never happened, and that 911 was a government conspiracy.
  • 12. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:

    I have a client with four freelist groups of 50 freelists and over 1,000 clericals, all hammering away, adding rows and deleting rows into a single table with 4 indexes.
    As joinathan notes, the fragmentation can be UGE, and frequent rebuild sare required, both for performance and storage.
    Do you mean you set up 4 freelist groups with 50 freelists on the table, or have you done this to the table and all four indexes ?

    Can you supply some statistical information showing why you thought your choice of freelists and freelist groups was appropriate ? For example:

    <ul>
    Number of insert statements against this table and average number of rows inserted per second
    Number of delete statements against this table and average number of rows deleted per second
    Net rate of increase of table size in rows per second (although we could work that out from the previous two).
    Current size of table in rows
    Number of CPU on the machine
    Do users delete only the rows that they have inserted, or are the inserts and deletes basically randomised.
    </ul>

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 13. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,

    My example is illustrative, I'm sure you have seen high impact tables like these before . . .
    Do you mean you set up 4 freelist groups with 50 freelists on the table
    The table and all four indexes has storage (freelist groups 4 freelists 50), total 200 freelists per object . . .
    Number of insert statements against this table and average number of rows inserted per second
    I don't recall, sorry, but it was in the realm of 50 DML's per second, but I could be wrong . . .
    inserts and deletes basically randomised.
    Yes and no.

    It's an order entry system, with clericals continuously entering and updating rows from the same table, including changing index key values.

    At night, batch jobs nuke large sections of completed orders.
    Number of CPU on the machine
    RAC, four node, 16 CPU's per node.
    Current size of table in rows
    Tens of millions, in a partitioned table, by date . . . .
    your choice of freelists and freelist groups was appropriate ?
    It was done empirically, of course, over time.

    As the system load grew, concurrent DML increased and the freelists were increased dynamically to accommodate the load to replace contention.

    We used a script like this to monitor the fragmentation and loading of the individual freelists, since free blocks cannot be shared between freelist groups:

    http://www.remote-dba.cc/oracle_tips_freelist_blocks.htm
    average number of rows deleted per second
    The deletes are done in batch.

    The interesting thing was that we had to parallelize the batch delete into four separate jobs, so that each of the four freelist groups would recieve the same number of freed blocks.

    If we ran tham all in one job, only one freelist group got the blocks, and the table extended, even though there were lots of free blocks, but only on one freelist group.

    ****************************************************************

    It would be really interesting if you could redo your "index explosion" tests with a real-world condition, simulating 20 updates per second for eight hours.

    Maybe then, it will become clear why some high-impact indexes require scheduled rebuilding/coalescing . . .
  • 14. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:

    The table and all four indexes has storage (freelist groups 4 freelists 50), total 200 freelists per object . . .
    Number of insert statements against this table and average number of rows inserted per second
    I don't recall, sorry, but it was in the realm of 50 DML's per second, but I could be wrong . . .
    It's an order entry system, with clericals continuously entering and updating rows from the same table, including changing index key values.
    You said you had 1,000 clericals (side note - in the UK "clerical" is an adjective, the people would be clerks) - so does 50 DMLs per second mean a clerk can enter an order in 20 seconds ... what a tedious job ... or does a single order handled by the clerk turn into several DML statements against this table ?
    At night, batch jobs nuke large sections of completed orders.
    If the data is partitioned by date (see below), and they're only producing a few million rows per week, you would have thought that someone could have figured out a strategy for dropping old partitions to get rid of old data. After all, there's usually a very strong correlation between age of order and state of order.

    Have they included the date in the primary key - or is their primary a global, or globally partitioned index ? It's not surprising that deleting a large volume of old data requires a coalesce if it's the typical sort of orders table with meaningless key - it's a classic case of the "trailing edge" or "FIFO" index.

    >
    RAC, four node, 16 CPU's per node.
    Now that's a good reason for using freelist groups 4. (Not that the system sounds big enough to need 4-node RAC).

    Current size of table in rows
    Tens of millions, in a partitioned table, by date . . . .
    Are the indexes local, global, or globally partitioned ?

    your choice of freelists and freelist groups was appropriate ?
    It was done empirically, of course, over time.
    A methods also known as "trial and error" - not necessarily a bad thing, of course.
    In your case setting freelists to 50 is obviiously an error, though not one that causes any problems.

    >
    We used a script like this to monitor the fragmentation and loading of the individual freelists, since free blocks cannot be shared between freelist groups:
    http://www.remote-dba.cc/oracle_tips_freelist_blocks.htm
    Are you sure that script does what you think it does ?

    average number of rows deleted per second
    The deletes are done in batch.

    The interesting thing was that we had to parallelize the batch delete into four separate jobs, so that each of the four freelist groups would recieve the same number of freed blocks.
    If we ran tham all in one job, only one freelist group got the blocks, and the table extended, even though there were lots of free blocks, but only on one freelist group.
    The problem is documented behaviour - running parallel deletes doesn't necessarily address it - you can get lucky - but it's better to engineer the code properly. But the procedure dbms_repair.rebuild_freelists appeared in 8i to address the issue anyway. The procedure is designed to spread free blocks evenly across multiple freelist groups. The last time I checked, though, it had a logic error that meant you sometimes had to run it twice to get it to distribute the free blocks completely evenly - the first pass left the freelist groups a little unbalanced.

    >
    ****************************************************************

    It would be really interesting if you could redo your "index explosion" tests with a real-world condition, simulating 20 updates per second for eight hours.
    Not a problem - even my laptop could cope with that quite easily.

    But do you have any reasonably argument about why a longer running test with a few more sessions but a lower concurrency level and a lower rate of inserts should behave any differently from the original test ? Do you want to make any prediction about what will happen ? Why would you find it interesting ?

    By the way, how does inserting data from multiple concurrent session into an indexed table not feature in the real world ?
    Maybe then, it will become clear why some high-impact indexes require scheduled rebuilding/coalescing . . .
    I thought people like Richard Foote, me, and a few others had already made it very clear why some indexes (forget the expression "high-impact" - it's irrelevant) can need some form of house-keeping operation.

    Of course we've also made it clear that most index rebuild operations are just a waste of resources. Think about the message of my "index explosiion" series - if you don't know why the problem happens you're just wasting resources working around it instead of addressing it.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
1 2 3 11 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points