Chatta subito con l'assistenza
Chat con il supporto

Space Manager with LiveReorg 9.1 - User Guide

Smart Start for Space Manager Collect Statistics Profile DML Activity Manage Storage Reorganize Objects with Reorg Manager Partition Tables with the Partitioning Wizard Run and Monitor Scripts Run Reports Options Troubleshooting Administration Functions Appendix About Us

How Space Manager Chooses a Posting Index

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:

  • All columns—The “all columns” method determines row uniqueness by using all columns in an original table to identify unique rows in its reorganized copy. Space Manager’s default behavior is to use “all columns” for tables that do not contain LOBs, LONGs, XMLTypes, or user-defined datatypes (object types, REFs, and VARRAYs). This method is used in conjunction with a non-selective index or no posting index. The non-selective index is used to speed up posting. First choice is a unique index with all columns nullable. Second choice is non-unique index without a unique constraint.
  • ROWID index —A ROWID posting index determines row uniqueness by using the ROWIDs of all rows in an original table to identify unique rows in its reorganized copy. Space Manager’s default behavior is to use a ROWID index for tables that contain LOBs, LONGs, XMLTypes, or user-defined datatypes (object types, REFs, and VARRAYs).

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.

Important Considerations for Posting Indexes

  • When a unique index is used as the posting index, be aware that posting may be slow if the leading column of the UNIQUE KEY constraint is nullable.
  • Indexes with disabled constraints are not considered in choosing a posting index.
  • Function-based indexes and indexes that contain an object type attribute cannot be used as a posting index. Object type attributes are a feature of user-defined datatypes.
  • If a table contains a LONG column larger than 32,760 bytes and does not have a PRIMARY KEY or non-nullable unique index, it can only be reorganized on a live basis if the “all columns” posting method and T-Lock switch are used. The table and all of its datatypes must be supported for FastCopy (or DBMS_DataPump).

Posting Index Types and Their Rankings

  1. 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.)

  2. 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

  3. 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:

    • Non-unique index
    • Unique constraint that is in ENABLE VALIDATE state and is enforced by the index
    • Unique constraint has a NOT NULL column in the ENABLE VALIDATE state

    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.)

  4. 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

  5. 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

  6. 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

  7. 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

 

Related Topics

Prompts to Validate Constraints

All Columns Posting Method

ROWID Posting Indexes

Set the Posting Parameter

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.

 

Related Topics

How Space Manager Chooses a Posting Index

All Columns Posting Method

ROWID Posting Indexes

Set the Posting Parameter

All Columns Posting Method

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:

  • “All columns” is used to ensure that rows are uniquely identified during posting of live transactions.
  • The non-selective posting index is used to speed up the posting process. If a table has multiple non-selective indexes, one that ranks highest on Space Manager’s priority list is used. Nonselective indexes are ranked from highest to lowest as follows: 1) unique index with all columns nullable, 2) non-unique index without a unique constraint, and 3) no index.

How the All Columns Method Works

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.

 

Related Topics

How Space Manager Chooses a Posting Index

Prompts to Validate Constraints

ROWID Posting Indexes

ROWID Posting Indexes

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.

How ROWID Indexes Work

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.

How ROWID Indexes Are Created and Dropped

When a ROWID index will be used for posting, it is automatically created during a live reorganization as follows:

  • A ROWID column is added to the copy table as the copy is created. The column is populated with the ROWIDs of all rows in the original table.
  • A ROWID index and UNIQUE constraint are created on the ROWID column. The constraint is used to guarantee uniqueness of rows.

ROWID indexes are not permanent. They are created in the LiveReorg tablespace and dropped as follows:

  • For tables—When a ROWID is used for a table, the ROWID index is dropped during the switch. Its ROWID column is set to UNUSED at that point and dropped at the end of live reorganization or partitioning script, after all tables have been reorganized.
  • For partitions and subpartitions—When a ROWID index is used for a partition or subpartition, both the ROWID index and its ROWID column are dropped during the switch.

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.

ROWID Indexes and Partitions

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

 

Related Topics

How Space Manager Chooses a Posting Index

Prompts to Validate Constraints

All Columns Posting Method

Set the Posting Parameter

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione