Skip to Content
Author's profile photo Former Member

reorg rebuild table versus reorg rebuild table index

Reorg rebuild Tablename Indexname allows the table to still be used  while the reorg is running.

Reorg rebuild tablename (without the index) causes the table to be blocked.

Reorg Compact is a good way of dealing with forwarded rows while the table is still actively being used.

Reorg Index maintenance and Reorg Compact maintenance are great ways of dealing with huge tables that cannot afford downtime.

I have not used the time options for the reorg command.

I believe it is better to use the “Reorg Compact” followed by “Reorg Rebuild Tablename Indexname”…

because the reverse would disturb cleanly built indexes.

February 20, 2014

If you are really in a hurry to fix slow reads (caused by forwarded rows) then the “forwarded_rows” option of reorg may offer some relieve if there is room on the original page.

Reorg Forwarded_rows Tablename

Keep reading this blog as time goes by to see how I improve my table performance.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Few more differences between reorg rebuild table and indexes.

      Reorg rebuild on table :

      1. Reorg for tables can run on APL and DOL.

      2. Cannot run on corrupt or suspect index.

      3. Cannot run on text or image chain.

      4. Cannot run within a transaction.

      Rebuilding indexes using reorg :

      1. reorgs on indexes can only run on DOL tables.

      2. Not necessary to set 'select into'.

      3. Less free space required.

      4. Dump tran can be done after rebuilding index using reorg.

      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. If commannd interupted, index is consistent.

      9. If command reissued, starts afresh but does not rebuild those parts of index that already has the desired space usage and page clustering.

      Author's profile photo Jeff Tallman
      Jeff Tallman

      If you are in 15.7, you can turn on full logging (sp_dboption) and then you don't have to worry about select/into....and then you can do dump you get reorg rebuild....with online and now reorg defrag.  One of the reasons I point this out is that you have to be careful with reorg compact, reorg forwarded_rows, etc.....if you read the notes, once it hits a forwarded row, some of the older reorgs would try for an exclusive table lock for that transaction (likely covering an extent).  The next transaction, it would grab a shared table lock...ugh....until the next forwarded row ...and back to exclusive table lock.   I think we all know that much of the reorg time is spent in PIO, but I wouldn't be surprised if attempts to run reorg reclaim_space or reorg fowarded_rows also would get delayed due to blocking.   Hence, I like the newer features of reorg rebuild with online and reorg defrag....