Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Unanswered: cluster Index Space

    Hi All,
    When we create the cluster index, we need free space in database approximately equal to 120 % of table size.
    For what purpose this space is required when we create the cluster index ?

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    In an APL table, the clustered index and the data are tied one to the other. Rebuilding the index involves rebuilding the data. In order for the create index to be rolled back, ASE sorts the data and builds the whole new thing in new pages, while keeping the old structure intact. This requires doubling the space plus some extra pages to build the index tree. Only at the end ASE makes the new structure active and gets rid of the old. Had an error ocurred just one millisecond before, ASE would have rolled back the new page allocations (not much space since the moved rows weren't logged, only alloc pages and this kind of special pages) and falled back to the previous state.

    Personally, this is one of the ASE features I've liked the most: you never lose the data if an error or crash happens while the reorg script is running. You may lose the index and have to create it again, but never the data. For DOL tables, REORG REBUILD is even more solid, as nothing at all is lost in the event of a failure.

    Regads,
    Mariano Corral Herranz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •