Running reorg defrag

reorg defrag executes multiple reorganization transactions one right after the other, compared with traditional reorg, which reorganizes the entire table data in a single transaction. You can specify a time interval during which it reorganizes the data. This allows other processes to run concurrently without affecting reorganization.

Syntax:

reorg defrag table_name [partition {partition_list}]
   [with {time = hh:mm| resume | skip_compact_extents [= pct_value]}]

where:

defrag – reorganizes each partition list or partition in the table while allowing concurrent reads or writes on the data being reorganized.
partition – is the subset of the table that shares the same column definitions, constraints, and defaults as the table.
partition_list – is the list of partition names.
time – reorganizes the table or the list of partitions for the specified interval of time. hh is the number of hours and has no limit, and mm is the number of minutes 0–59.
resume – resumes table reorganization from the end of the last reorganized data page invoked from the previous reorg defrag. resume continues until the entire table or list of partitions is reorganized, processing only those partitions that are currently either unvisited or partially reorganized. Running time = hh:mm with resume indicates that you are running reorganization from the previous position of reorganization and running it only for the specified interval of time.
skip_compact_extents – skips compact extents. The compactness of an extent is measured as the percentage occupancy in that extent.
pct_value– is the compactness of an extent measured as the percentage occupancy in that extent with a value of 1–100.
Compactness = (Total space occupied in an extent / Total space in an extent) x 100

If skip_compact_extents is used, all the extents with compactness greater than or equal to the threshold occupancy percent value specified would be skipped for reorganization. If no threshold percent value is specified, the default percent value is 80%.

Examples:

In the following examples, 'partition list' is the list of data partitions specified in the command or if none specified, the list of all existing data partitions in the table.

Example 1 reorganizes the partition list, starting from the beginning of each partition and continuing until its end:
```
reorg defrag salesdetail [partition {seg1 [,seg2[, seg3]]}]
```
Example 2 reorganizes the partition list, starting from the beginning of each partition and continuing until the specified time interval is reached.
```
reorg defrag salesdetail [partition 
   {seg1 [,seg2[,seg3]]}] with time = 01:20 
```
Example 3 restarts reorganization for each partition in the partition list that is either unvisited or partially reorganized, from where the previous invocation of reorganization has stopped, and if no point for resuming is available, reorganization starts from the beginning of the partition. Reorganization continues until the end of each partition.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with resume
```
Example 4 reorganizes the partition list, starting from the beginning of the partition and continuing until its end, skipping the extents with more than the specified occupancy threshold. If a percentage is not specified, extents exceeding 80% occupied are considered compact, and skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with skip_compact_extents [ = <1-100>]
```
Example 5 reorganizes each partition in the partition list that is either unvisited or partially reorganized, starting from the point where the previous invocation of reorganization stopped. If no point is available for resuming, reorganization starts from the beginning of the partition. Reorganization continues until the specified time interval is reached.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with time = 01:20, resume
```
Example 6 reorganizes the partition list, starting from the beginning of the partition and continuing until the specified interval of time completes, skipping each extent that qualifies to be compact as per the pct_value specified for skip_compact_extents. If a percentage is not specified, extents exceeding 80% occupied are considered compact, and skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with time = 01:20, skip_compact_extents [ = <1-100>]
```
Example 7 reorganizes each partition in the partition list that is either unvisited or partially reorganized, starting from the point where the previous invocation of reorganization stopped. If no point is available for resuming, reorganization starts from the beginning of the partition. Reorganization continues until the end of each partition. It skips extents that qualify to be compact as per the pct_value provided for skip_compact_extents. If the pct_value is not provided, extents exceeding 80% occupied are considered compact and hence skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with resume, skip_compact_extents[ = <1-100>] 
```
Example 8 reorganizes each partition in the partition list that is either unvisited or partially reorganized, starting from the point where the previous invocation of reorganization has stopped. If no point is available for resuming, reorganization starts from the beginning of the partition. Reorganization continues until the specified time interval completes. It skips extents that qualify to be compact as per the pct_value specified for skip_compact_extents. If a percentage is not specified, extents exceeding 80% occupied are considered compact and hence skipped.
```
reorg defrag salesdetail [partition {seg1 [,seg2[,seg3]]}] 
   with time = 01:20, resume, skip_compact_extents [ = <1-100>]
```
For more information about reorg, see the Reference Manual: Commands.

For every 10% of a partition processed, this information appears:

Examined n allocation unit(s). Processed x pages out of y 
data pages. z% completed, resulting in p% space compaction.

At the end of processing each partition, the time elapsed in the current invocation is printed as:

Elapsed time 1m : 56s : 623ms.