Re: Index definition

  • 1.  Re: Index definition

    Posted 08-05-2009 12:23 PM
    The major issue you may face here is an explosion of orphans, not a split
    issue per se.

    Orphan chains happens when the insertion point is at the beginning of an
    index (as with descending date indexes) or, in your case, where you may have
    a lot of duplicates in a duplicates first condition.

    Without going into the mechanics, these sorts of things result in creating a
    cascading series of orphans, as you keep splitting blocks that already
    contain orphans, pushing the block that actually points to the orphaned
    record way way down the SR8 chain from the SR8 the orphan points back to.
    This can cause performance issues, esp during an ERASE operation, as all the
    intermediate SR8s in the orphan chain have to have their orphan counts

    In your case, since this is a compound index, it may not be that big a deal.
    Run an index report and look at your orphan counts. In any event, you would
    have less exposure with a sequence that tends to insert at the END of things
    already there, in your case that would be DUPLICATES LAST.

    Don Casey
    Principal Consultant
    Run Right, LLC