Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

1 Answer

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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