2

Could someone please explain what the main differences are between reorg-rebuilds and index re-creation in Sybase ASE?

  • This is a great right up regarding indexing and reorgs. – Michael Gardner Feb 23 '13 at 16:29
  • 1
    @Shamika: if you want to post this type of information, please make a real question and answer pair (there's a checkbox in the "Ask Question" form that allows you to post the question and the answer at the same time). – Mat Feb 23 '13 at 17:20
1

Reorg rebuild vs drop/create index

  1. reorg does not perform sort.
  2. reorg uses the index to retrieve rows while rebuilding the table.
  3. reorg rebuils is minimally logged(should be faster than creating clustered index).

Reorg usage

  1. All reorgs for indexes can only run on DOL tables.
  2. Reorg for tables can run on APL and DOL.
  3. Cannot run on corrupt or suspect index.
  4. Cannot run on text or image chain.
  5. Cannot run within a transaction.

Rebuilding indexes using reorg (reorg rebuild tablename indexname)

  1. Not necessary to set 'select into'.
  2. Less free space required.
  3. Dump tran can be done after rebuilding index using reorg.
  4. Only available for DOL tables.
  5. Works in small transaction; de-allocates pages once copied.
  6. Only the index pages are rebuilt, data pages untouched, not locked.
  7. Only a small number of index pages are locked at any given time.
  8. Estimate time to complete : approx 2-3 times of 'select count(*) from tablename(indexname)'.
  9. If commannd interupted, index is consistent.
  10. If command reissued, starts afresh but does not rebuild those parts of index that already has the desired space usage and page clustering.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.