Could someone please explain what the main differences are between reorg-rebuilds and index re-creation in Sybase ASE?
Reorg rebuild vs drop/create index
- reorg does not perform sort.
- reorg uses the index to retrieve rows while rebuilding the table.
- reorg rebuils is minimally logged(should be faster than creating clustered index).
- All reorgs for indexes can only run on DOL tables.
- Reorg for tables can run on APL and DOL.
- Cannot run on corrupt or suspect index.
- Cannot run on text or image chain.
- Cannot run within a transaction.
Rebuilding indexes using reorg (reorg rebuild tablename indexname)
- Not necessary to set 'select into'.
- Less free space required.
- Dump tran can be done after rebuilding index using reorg.
- Only available for DOL tables.
- Works in small transaction; de-allocates pages once copied.
- Only the index pages are rebuilt, data pages untouched, not locked.
- Only a small number of index pages are locked at any given time.
- Estimate time to complete : approx 2-3 times of 'select count(*) from tablename(indexname)'.
- If commannd interupted, index is consistent.
- If command reissued, starts afresh but does not rebuild those parts of index that already has the desired space usage and page clustering.