Operating Condition
Syntax: SET RECYCLE ON/OFF
Default: OFF
If RECYCLE is ON, when adding new rows require a new block from file 2, a new routine is called which searches for a suitable unused block rather than always adding a new block to the end of the file
The criteria for such a block are:
•No other table uses it
•The block is further down the file than the current last block of the table
PROS
If a suitable block is found, the block will be allocated to the table requiring the additional space and File 2 will not grow as a result of this allocation. The main benefit of using RECYCLE is that the growth of File 2 will be minimized. Over time this can add up to significant savings on disk space and backup media.
CONS
Since a new routine is being called to search for a suitable block, there will be a slight performance penalty. The penalty will only be incurred when an INSERT requires a new block.
Considerations
For RECYCLE to be effective, all users should have the setting ON. SET RECYCLE is best used in the configuration file. RECYCLE will only have an impact when used in conjunction with PACK TABLE. Dead space in File 2 must first be freed up before it can be reused. RECYCLE will not be of benefit if your database does not end up with lots of deleted rows over time, providing the opportunity to recover dead space.
Conclusions
Periodic use of PACK TABLE tablename in conjunction with RECYCLE ON will retard File 2 growth and reduce fragmentation. Use of PACK INDEX FOR tablename will keep the index statistics fresh and query optimization results maximized. The need for planned downtime will be reduced.
