Maintenance Tasks – REORG

Reorg [Reorganizes]

The Reorg command reorganizes the use of table space and improves performance. It is garbage collector process which will run only on DOL(Data Only Locking) tables.

It performs the following tasks :

• Takes on exclusive table lock
• Copies data from Old to New pages
• De-allocates old data pages
• Rebuilds clustered and non-clustered indexes against new data pages
• Commits all open transactions
• Releases locks on system table

Types of reorg are :

• Reorg rebuild
• Reorg Forwarded_rows
• Reorg reclaim_space
• Reorg Compact

Reorg rebuild

• undos row forwarding and reclaims unused page space

Syntax : regorg rebuild tablename

Example :

• Before we run reorg rebuild the dboption “select into bulkcopy should be enabled ”
• There should be additional disk space equal to the size of the table and its indexes
• Change the locking scheme of a table to run the reorg rebuilt

Reorg Forwarded_rows

• will undo row forwarding

Syntax : reorg forwarde_rows<table name>

Example :

Reorg reclaim_space

• Try's to reclaim the space
• Reclaims unused space left on the page as a result of deletion and row shortening update

Syntax : reorg reclaim_space <tablename> <index no> with resume time = no of minutes

Here index no is optional

Example :

Reorg Compact

• It will do both reclaim the space and undo the row forwarding

Syntax : reorg compact <table name>

Example :

• The utility used to check what reorg is used is optdiag
• If clustered ratio>0.9 then we need to run reorg rebuild