In most cases, Space Manager automatically chooses the index to use as the posting index. It looks at all indexes available for a table and selects the index that ranks highest on the list under “Posting index types and their rankings”.
Index types are ranked in order of most selective to least selective. Space Manager’s first choice among selective indexes is the PRIMARY KEY. Its second choice is a unique index. Its third choice is a non-unique index with a unique constraint.
If a selective index is not available, Space Manager compensates by using one of two special methods for guaranteeing uniqueness of rows. These are the “all columns” posting method and the ROWID posting index:
If you want to change Space Manager’s default use of “all columns” and ROWID indexes, you can do this by setting a parameter for QSA. See Comparison Chart for more information.
Tip To see what type of posting index will be used for a table, check its LW_START_REORG command in a live reorganization or partitioning script.
First choice — PRIMARY KEY
First choice for the posting index is a PRIMARY KEY in the ENABLE VALIDATE state. If a PRIMARY KEY is in the ENABLE NOVALIDATE state and a table has no other suitable posting indexes, a message prompts you to validate the constraint in Reorg Manager or the Partitioning Wizard. The PRIMARY KEY must be in the validate state to guarantee that rows are uniquely identified during posting. See Prompts to Validate Constraints for more information.
PK or PKNV = identifier in LW_START_REORG
PKNV is displayed when a PRIMARY KEY will be temporarily set to NOVALIDATE to speed up the switch. It is reset to VALIDATE after the switch is made. This happens when the online switch is used or when partitions and subpartitions are reorganized individually. (PKNV posting indexes are not used for partitioning scripts.)
Second choice — Unique index with NOT NULL column
Second choice for the posting index is a unique index with at least one NOT NULL column. Its NOT NULL constraint must be in the ENABLE VALIDATE state. If the index has a unique constraint, this does not need to be validated. If a table has multiple unique indexes that meet requirements, Space Manager uses the first unique index returned by the query it runs.
UK = identifier in LW_START_REORG
Third choice — Non-unique index with a unique constraint
Third choice for the posting index is a non-unique index with a unique constraint that is in the ENABLE VALIDATE state and has at least one NOT NULL column. These requirements are listed below:
If the UNIQUE KEY constraint is in the ENABLE NOVALIDATE state and a table has no other suitable posting indexes, a message prompts you to validate the constraint in Reorg Manager or the Partitioning Wizard. See Prompts to Validate Constraints for more information. The constraint must be in the validate state in order to guarantee that rows are uniquely identified during posting of live transactions.
UC or UCNV = identifier in LW_START_REORG
UCNV is displayed when a unique constraint will temporarily be set to NOVALIDATE to speed up the switch. It is reset to VALIDATE after the switch is made. This happens when the online switch is used or when partitions and subpartitions are reorganized individually. (UCNV posting indexes are not used for partitioning scripts.)
Fourth choice — Unique index with all columns nullable
Fourth choice for the posting index is a unique index in which all of the columns allow null values. This type of index is nonselective and may not contain all of its table’s rows. Normally, that would allow the table to contain duplicate rows. However, when this type of index is used as the posting index, the “all columns” posting method is also to guarantee uniqueness of rows during posting.
UI = identifier in LW_START_REORG
Fifth choice — Non-unique index without a unique constraint
Fifth choice for a posting index can be either:
A non-unique index without a unique constraint.
or
A non-unique index with a unique constraint that does not have at least one NOT NULL column.
This type of index is nonselective. To guarantee uniqueness of rows, the “all columns” posting method is used along with it.
IX = identifier in LW_START_REORG
Sixth choice — No index
Sixth choice is to post without an index. This choice is made for tables that do not have any indexes. To guarantee uniqueness of rows, the “all columns” posting method is used when a posting index is not available.
NONE = identifier in LW_START_REORG
Last choice — ROWID index
Last choice is a ROWID index. This uses the ROWIDs of all rows in the original table to guarantee uniqueness of rows. A ROWID index is created during script execution for temporary use. It is dropped during or after the switch, depending on whether a table or (sub)partition is being reorganized. See ROWID Posting Indexes for more information. The reason a ROWID index is the last choice is because of the DDL it requires.
RID = identifier in LW_START_REORG
Prompts to Validate Constraints
Normally, Space Manager automatically chooses the posting index to use for a table. However, in some cases, the Reorg Manager or the Partitioning Wizard prompts you to make decisions regarding the posting index. This happens when the only candidate for the posting index has a PRIMARY KEY constraint or a UNIQUE KEY constraint in the ENABLE NOVALIDATE state (choices one and three under “Rankings for posting index types”).
The prompt occurs in a message raised by the Reorg Manager or the Partitioning Wizard. The message prompts you to validate a constraint so that it can be used for posting. If you do not want to validate a constraint, you can exclude its table from a script, use the “all columns” posting method, or use a ROWID posting index. See Respond to Checks for a Posting Index for more information.
How Space Manager Chooses a Posting Index
The “all columns” posting method is automatically used for tables that do not have a selective index and do not contain LOBs, LONGs, XMLTypes, or user-defined datatypes (object types, REFs, and VARRAYs). It is used along with a non-selective posting index (or no posting index) as follows:
The “all columns” method determines row uniqueness by using all columns in an original table to compare live transactions to rows in its reorganized copy.
The only columns this method does not use in comparing rows are columns with LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs).
If the only difference between two rows occurs in one of these columns, the “all columns” method does not detect the difference. Because this could result in duplicate rows in the reorganized table, Space Manager’s default behavior is to prevent use of “all columns” for tables that contain these columns and do not have a selective index. ROWID indexes are used instead.
If you want to allow use of “all columns” for tables with LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs), you can do this by setting a Quest Server Agent parameter. You should only do this if you are certain that rows can be identified uniquely based on columns that do not contain these datatypes. See Set the Posting Parameter for more information.
How Space Manager Chooses a Posting Index
Prompts to Validate Constraints
ROWID indexes are automatically used for tables that do not have a selective index and contain LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs). This type of posting index is only used for other tables if you select it in a message prompting you to validate constraints. See Prompts to Validate Constraints for more information.
ROWID indexes determine row uniqueness by using the ROWIDs of all rows in an original table to compare live transactions to rows in its reorganized copy.
The ROWID posting method is faster than the “all columns” method and never allows duplicate rows for tables with LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs). However, it does not support FastCopy/DBMS_DataPump or the online switch and it requires that a column and index be created and dropped during reorganization. This DDL can affect reorganization performance in various ways. See Comparison Chart for more information.
If you want to use the “all columns” method instead of ROWID posting indexes for tables with LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs), you can do this by setting a Quest Server Agent parameter. You should only do this if you are certain that rows can be identified uniquely based on columns that do not contain these datatypes. See Set the Posting Parameter for more information.
When a ROWID index will be used for posting, it is automatically created during a live reorganization as follows:
ROWID indexes are not permanent. They are created in the LiveReorg tablespace and dropped as follows:
After the ROWID column and index are dropped, dependencies may be invalid. (However, they are automatically recompiled the first time they are referenced.) Under rare circumstances, dropping the column may take a long time and affect application performance. Also, dropping the column can sometimes fail with a “Snapshot too old” error.
Note The LiveReorg tablespace is where temporary LiveReorg objects are created. It is specified with QSA’s LW_TABLESPACE parameter.
By default, ROWID indexes are only used for entire partitioned tables and nonpartitioned tables. They are not used for individual partitions or subpartitions. This is because when they are used for the latter objects, they require more time during the switch. The additional time is due to how ROWID indexes are dropped for these objects.
If you want to be able to use ROWID indexes for partitions and subpartitions, you need to set a Space Manager parameter. To do this, insert a row in the QUEST_SPC_PARAMETER table, where:
NAME = Reorganization\Method\UseRowidForPartitions
VALUE = 1
How Space Manager Chooses a Posting Index
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
