Difference between reorg-rebuild and index re-create in Sybase ASE

Question

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

Answer 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.