• Extremely large tables containing some sort of temporal column (like a transaction date) can easily be partitioned by month (for example) for the purpose of not having to reindex old data over and over again. With such partitioning, all of this might be less of a problem than you thought. For example, it would almost guarantee that the BTREE level of the indexes would never grow large enough to have to worry about fragmentation there (as Gail pointed out).

    As a side bar and depending on when updates stop happening (if they're updated at all) to a month of data in a partitioned table (or view), I'll typically do a full rebuild (doesn't take much time because it's relatively small compared to the rest of the table) of all the indexes for that month and they almost never become fragged enough (because of no updates) to even consider after that. The final unqualified full rebuild is just to save as much disk space as possible.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)