Reorganize /Reorg

Go Up to Available object actions by DBMS

Reorganize or Reorg options are offered against the following DBMS platform/object types:

• Reorganizing IBM DB2 for Linux, Unix, and Windows Objects
• Reorganizing Oracle Objects
• Reorganize (SQL Server indexes, primary keys, and unique keys)
• Reorganize (SQL Server Full-text Catalogs)
• Reorganize (Sybase ASE Indexes and Tables)
• Reorg Index (DB2 z/OS)

Reorganizing IBM DB2 for Linux, Unix, and Windows Objects

Reorganize Dialog Box (One Table)

The table below describes the options and functionality on the Reorganize dialog.

Step	Description
Action Options	Choose a Reorg Type of table or all indexes of the table. Optionally, choose an Index Schema and specify an Index name. Enable or disable Inplace Reorg and if you enable, choose an Inplace reorg mode of Start, Stop, Pause, or Resume. Select an Access mode to control read and write access. Select an Index reorg mode to clean up empty pages, delete after cleaning up empty pages, or convert to a type 2 index. If you did not enable Inplace Reorg, select a Tablespace. Select a Long Tablespace. Enable or disable the following options: Index scan, Reorg long field and LOB data, Reset Dictionary, No truncate table, and Reorganize all partitions.
Partitions	If you did not enable Reorganize all partitions, select the partitions to reorganize.
Preview	Preview the DDL code generated from your choices. For more information, see Preview..

Reorganize Dialog Box (Multiple Tables)

The table below describes the options and functionality on the Reorganize dialog.

Option	Description
Temporary Tablespace	Associates a temporary tablespace with the table's tablespace. You can select another tablespace from the list.

Reorganize (SQL Server indexes, primary keys, and unique keys)

The Reorganize Indexes dialog lets you reorganize an entire index, primary key, or unique key or a single partition of that object. It also lets you specify a LOB_COMPACTION option.

Note: You cannot reorganize an index (primary key, or unique key) that has an Allow Page Locks property set to FALSE. For information on setting index properties, see Indexes Editor (SQL Server).

To reorganize an index

1. Initiate a Reorganize action against an index, primary key, or unique key. For more information see Initiating an object operation.

   The Reorganize Index wizard opens.

2. Use the following table as a guide to understanding and modifying settings in the wizard:

Step	Settings and tasks
Action options	To reorganize a single partition, specify a Partition number and optionally, specify Lob Compaction used to create the REORGANIZE WITH clause. To reorganize the entire index, primary key, or unique key, DO NOT provide a Partition Number, and optionally, specify Lob Compaction used to create the REORGANIZE WITH clause.
Dependencies	Review the referring and referred objects that will be automatically resolved when you execute this operation. For more information, see Dependencies.
Preview	Preview the DDL generated for the operation. For more information, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Reorganizing Oracle Objects

The Reorganize dialog lets you reduce query processing time against tables.

To Reorganize an Oracle Table

1. Initiate a Reorganize action against a table. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the Reoganize dialog:

Panel	Group	Setting and description
Action Options	Reorganize method	Online - If selected, reorganization is carried out online, using DBMS_REDEFINITION procedures. DBMS_REDEFINITION is Oracle’s online table reorganization package and is available for Oracle 9i. During an online reorganization, the table can still be accessed using DML statements like SELECT and UPDATE. If this check box is unselected, reorganization is carried out using a series of simple ALTER TABLE statements. This method is intended for offline usage.
	Tablespace	If you want to move the table(s) to a new tablespace, select the new tablespace from this list.
	Data Block Storage	Type ALTER TABLE property values for the Percent Free (PCTFREE property), Percent Used (PCTUSED property), Initial Transactions (INITTRANS property), and Max Transactions (MAXTRANS property).
	Extents	Type an ALTER TABLE MOVE STORAGE property value for the Initial Extent (INITIAL) property). Type ALTER TABLE STORAGE property values for the Next Extent (NEXT property), Percent Increase (PCTINCREASE property), Minimum Extents (NEXT property), and Maximum Extents (MAXEXTENTS property).
	Freelists	Use the Free Lists and BufferPool (DEFAULT, KEEP, RECYCLE) settings to provide values for the FREELISTS and BUFFER_POOL components of a STORAGE clause. Use the Free List Groups setting to provide a value for the FREELIST GROUPS component of a STORAGE clause
	Parallel Query	Use the Parallel Degree setting to provide a value for the DEGREE component of a PARALLEL clause. This specifies the number of servers used in processing operations.
	Physical	Logging - When disabled, a NOLOGGING clause is added to the ALTER TABLE statement.
Dependencies	For details on using this tab, see Dependencies.
Preview	For details on using this tab, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Reorganize (SQL Server Full-text Catalogs)

This action builds and submits a basic ALTER FULLTEXT CATALOG catalogname REORGANIZE statement, with no additional options or arguments. This lets you merge smaller indexes into a larger, master index.

To reorganize a full-text catalog:

1. Initiate a Reorganize action against one or more full-text catalogs. For more information, see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the dialog:

Step	Settings and tasks
Action Options	Displays the catalogs you selected.
Dependencies	Lets you review the objects potentially impacted by this action. For more information, see Dependencies.
Preview	Preview the DDL generated for the operation. For more information, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Reorganize (Sybase ASE Indexes and Tables)

The Reorganize dialog lets you reduce query processing time against tables. This functionality is available for both tables and indexes.

For more information, see

• Reorganizing Sybase ASE Tables
• Reorganize Sybase ASE Indexes

Reorganizing Sybase ASE Tables

This action lets you build and submit one of four REORG subcommands: FORWARDED_ROWS, RECLAIM_SPACE, COMPACT, or REBUILD. REORG optimizes the use of table space and improves performance.

The Reorganize Table dialog lets you reduce the query processing time against a table by reorganizing the table to ensure that space is properly allocated to it. For lengthy reorganization processes, this dialog box also lets you execute a process in increments, lets you resume an incomplete process, and lets you specify the duration of each increment. For more information, see Incremental Reorganizations.

Tip: Frequent update activity on a table can cause data rows to migrate and to chain over multiple data pages. Chained or forwarded rows can degrade performance because more physical reads are required to access a row of data. Consequently, you should monitor chained rows regularly to spot performance bottlenecks before they become severe. In addition, altering physical storage parameters can lead to fragmentation of space on your data pages, which also results in reduced performance levels.

You should consider reorganizing a table if you are experiencing slow performance due to:

• A large number of chained or forwarded rows on your data pages
• A large amount of fragmentation in your data pages

Note: You can reorganize tables in Sybase ASE versions 12 and 12.5.

The table below describes the options and functionality on the Reorganize Table dialog:

Option	Description
Compact	Lets you reclaim space and undo row forwarding. Minimizes interference with other activities by using multiple small transactions of brief duration. Each transaction is limited to eight pages of reorg processing. These three commands also provide resume and time options that allow you to set a time limit on how long a reorg runs and to resume a reorg from the point at which the previous reorg stopped, making it possible to use a series of partial reorganizations at off-peak times to reorg a large table. For information on result options, see Incremental Reorganizations.
Reclaim Space	Lets you reclaim unused space resulting from deletions and row-shortening updates on a page. Minimizes interference with other activities by using multiple small transactions of brief duration. Each transaction is limited to eight pages of reorg processing. These three commands also provide resume and time options that allow you to set a time limit on how long a reorg runs and to resume a reorg from the point at which the previous reorg stopped, making it possible to use a series of partial reorganizations at off-peak times to reorg a large table. For information on result options, see Incremental Reorganizations.
Rebuild	Lets you undo row forwarding and reclaim unused page space. It also rewrites all rows to comply with the target table's clustered index, writes rows to data pages to comply with space management setting changes (via sp_chgattribute), and drops and re-creates all the target table's (or tables') indexes. Reorg rebuild holds an exclusive table lock for its entire duration. On a large table this can be a significant amount of time. However, reorg rebuild accomplishes everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table's current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap. In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.
Undo Row Forwarding	Lets you undo row forwarding, a process that occurs when an update increases a row's length in a data-only-locked table such that the row is too large to fit on its original page.
Options	Start at the point where a previous reorg left off - Select to resume a previously initiated but incomplete partial reorganization. Then specify the duration for which you want the resumed reorganization to continue before stopping again. This box is disabled for the rebuild command.

Incremental Reorganizations

If target tables are too long to reorganize in one session, you can reorganize them in increments over multiple sessions by specifying a maximum duration for each session. After tables are reorganized for the specified duration, the operation stops until you resume it again from the Options box of the Reorganize Table dialog. The Options box lets you specify to resume a previously initiated but incomplete partial reorganization. It also lets you specify the duration for which you want a resumed reorganization to continue before stopping again. The Option box is disabled for the rebuild command.

Note: The duration you specify refers to elapsed time, not CPU time

In the option box, if you select the check box without specifying a duration, the reorg executes at the point where the previous reorg stopped and continues to the end of the target tables. If you clear the check box and specify a duration, the reorg starts at the beginning of the target tables and continues for the specified number of minutes. If you select the check box and specify a duration, the reorg runs from the point where it last left off, and continues for the specified number of minutes.

Note: If you reorganize a table using one command (Compact, Reclaim Space, or Undo Forwarding) for a specified duration, you cannot resume the process from its resume point using a different command. For example, you cannot compact a table for an hour, and then reclaim space on the remainder of the table. A resumed reorganization process must utilize the same command from start to finish. Selecting a different command begins a new reorganization process.

Caution: While this option lets you reorganize a large table in multiple manageable pieces, any updates to the table between reorganization runs can cause pages to be skipped or processed more than once.

Reorganize Sybase ASE Indexes

The Reorganize Index dialog lets you reduce the query processing time against a table by running a reorg rebuild command on the target index.

This operation:

• Undoes row forwarding and reclaim unused page space
• Rewrites all rows in the table to comply with the table's clustered index
• Writes rows to data pages to comply with space management setting changes (via sp_chgattribute)
• Drops and re-creates the table's indexes

Reorg rebuild holds an exclusive table lock for its entire duration. On a large table this can be a significant amount of time. However, reorg rebuild accomplishes everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table's current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap. In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.

Reorg Index (DB2 z/OS)

This action lets you build and submit a REORG INDEX utility call.

Note: Before using this action consult IBM documentation for details on REORG INDEX utility options. For online access to DB2 documentation, see Accessing Third Party Documentation.

To Reorganize an Index

1. Initiate a Set Default action against a tablespace. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in this wizard. Note that some settings are only available based on other selections.

Setting	Description
Select Indexes	On opening, the list displays the index you chose to reorganize. Optionally, you can add more indexes to the list that are to be reorganized by clicking Add. Adding indexes using the Index Selector dialog is a two-step process. Use the Database Like, Index Creator, Index Like, and Match Case controls to provide a search criteria pattern and then click Query to display qualified indexes in the Index Selector list. Then select one or more indexes from the list, click Add, and close the dialog. To remove a selected index from the list to be reorganized, select the index in the list and click Delete.
Do you want the utility to be reusable	Select this check box to make the utility restartable.
Share Level	Lets you select share level that allows users to view but not modify the table data: REFERENCE, CHANGE, or None.
Deadline	Lets you specify a deadline for the switch phase of reorganization to start. If it is estimated that the switch phase will not start by the deadline, the reorganization terminates. None - Specifies that there is no deadline for the read-only iteration of log processing. Timestamp - Specifies the deadline for the switch phase to start processing. labeled-duration-expression - Click the ... button to open a dialog that lets you build a custom CURRENT_TIMESTAMP or CURRENT_DATE value.
Drain Specification	Lets you specify that DB2 drains the write claim class after the delay. The number of log records, and thus the estimated time, for a future iteration of log processing will be 0.
Fast Switch	Keeps the data set name unchanged and updates the catalog to reference the newly reorganized data set.
Maxro (only available with a Share Level of CHANGE)	Lets you DEFER or specify the maximum amount of time for the last iteration (read-only access iteration) of log processing.
Drain (only available with a Share Level of CHANGE)	Lets you specify drain behavior (WRITERS or ALL) at the end of the log phase after the MAXRO threshold is passed and when the last log iteration is to be applied:
Long Log (only available with a Share Level of CHANGE)	Lets you provide a LONGLOG value of CONTINUE, DRAIN, or TERM
Delay (only available with a Share Level of CHANGE)	Lets you specify the minimum interval between the time REORG sends the LONGLOG message to the console and the time REORG performs the action specified by the Long Log setting.
Timeout (only available with a Share Level of CHANGE)	Lets you specify a timeout option of ABEND or TERM.
Leaf dist limit	Lets you specify the value that is to be compared to the LEAFDIST value for the specified partitions of the index in SYSIBM.SYSINDEXPART. If any LEAFDIST value exceeds the specified Leaf dist limit, REORG is performed or, if you specify REPORTONLY, recommended.
Report	If selected, REORG is only recommended.
Unload	Specify a continue or terminate value of CONTINUE, ONLY, or PAUSE.
Do you want to specify the stats option	Specifies that statistics are to be collected and either reported or stored in the DB2 catalog.
Do you want to output message to SYSPRINT	Specifies that a set of messages is to be generated to report the collected statistics and output to SYSPRINT.
Do you want to specify the correlation stats option	Lets you specify a correlation-stats-spec for statistics reporting.
Do you want to force aggregation or rollup processing to be done event though some parts do not contain data	Specifies whether statistics are to be aggregated or rolled up when RUNSTATS is executed.
Work DDN	Lets you specify a DD name or a TEMPLATE name from a previous TEMPLATE control statement.
Would you like to preformat	Lets you specify that remaining pages are to be preformatted up to the high-allocated RBA in the index space.

3. When ready, click Finish.

Deadline Expression Builder

The table below describes the options and functionality on the Deadline Expression Builder dialog

Option	Description
Current Date	Lets you select today as the basis of the deadline, click + or -, type the number of years, months, days, hours, minutes, seconds and microseconds.
Current Timestamp	Lets you select the current timestamp as the basis of the deadline, click + or -, type the number of years, months, days, hours, minutes, seconds and microseconds.

Condition Dialog Box

The Condition dialog lets you type free form condition text.

Note: The correctness of the condition text is not tested.