Reorganize /Reorg
Go Up to Available object actions by DBMS
Reorganize or Reorg options are offered against the following DBMS platform/object types:
Contents
- 1 Reorganizing IBM DB2 for Linux, Unix, and Windows Objects
- 2 Reorganize (SQL Server indexes, primary keys, and unique keys)
- 3 Reorganizing Oracle Objects
- 4 Reorganize (SQL Server Full-text Catalogs)
- 5 Reorganize (Sybase ASE Indexes and Tables)
- 6 Reorganizing Sybase ASE Tables
- 7 Incremental Reorganizations
- 8 Reorganize Sybase ASE Indexes
- 9 Reorg Index (DB2 z/OS)
- 10 Reorg (DB2 z/OS Tablespaces)
- 11 Reorganize Tablespace Wizard - Panel 1
- 12 Reorganize Tablespace Wizard - Panel 2
- 13 Reorganize Tablespace Wizard - Panel 3
- 14 Deadline Expression Builder
- 15 Reorganize Tablespace Wizard - Panel 4
- 16 Reorganize Tablespace Wizard - Panel 5
- 17 Reorganize Tablespace Wizard - Panel 5
- 18 Reorganize Tablespace Wizard - Panel 6
- 19 Reorganize Tablespace Wizard - Panel 7
- 20 Reorganize Tablespace Wizard - Panel 6
- 21 Reorganize Tablespace Wizard - Statistics Tables
- 22 Reorganize Tablespace Wizard - Statistics Indexes
- 23 Reorganize Tablespace Wizard - Panel 7
- 24 Reorganize Tablespace Wizard - Panel 7
- 25 Reorganize Tablespace Wizard - Panel 8
- 26 Reorganize Tablespace Wizard - Panel 9
- 27 Condition Dialog Box
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 |
|
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
- 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.
- 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
- Initiate a Reorganize action against a table. For more information see Initiating an object operation.
- 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:
- Initiate a Reorganize action against one or more full-text catalogs. For more information, see Initiating an object operation.
- 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
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
- Initiate a Set Default action against a tablespace. For more information see Initiating an object operation.
- 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.