Space Manager with LiveReorg 9.0 - User Guide

Specify Partitioning Key Columns

Use the Partitioning Keys page of the Partitioning Wizard to specify the columns to use for partitioning keys.

If an object is already partitioned, the grid displays its current partitioning key columns. You can select different columns as needed. You must select different columns when you change partitioning method for an object. In this case, partitioning key grids are blank. You may need to select different columns when you change locality from global to local for a unique index. This must be done if the table’s current key does not use one or more of the columns that define the index.

Lists in partitioning key grids include all columns in a table or index that are eligible to serve as partitioning columns. If a column is not supported by Oracle for use in partitioning keys, the column is not displayed.

Tip: Columns are used in the order displayed in a partitioning key grid. In some cases, the columns in a key must be in the order used for columns in an index. To see how column order is defined for an index, open it in Object Properties and view the Columns tab.

To specify partitioning key columns

1. Select a table in the Object list. If you select a partition, the key columns for that partition are used as a key column for its table.

2. Select the first column to use for the table’s partitioning key.

   • If the table is list partitioned or you want to use only one column in the key, then proceed to Step 6.

   • If the table is range or hash partitioned and you want to add more columns, then proceed to Step 4.

     Note: If a table is range partitioned, columns should be in an ordered list. If a table has unique local index, columns in the table key must be part of the index definition. If a table has multiple unique indexes, columns in the key must be common to all the indexes.

3. (Range partitioning only) If the table is range partitioned and you want to use an interval, enter or select an interval value in the Interval field. This will enable interval partitioning. Oracle will automatically select the range based on the interval and range value you specify.
   1. If you enter a value, the format must be consistent with the partitioning column's data type.

4. Select additional columns for the table’s partitioning key, if necessary. Select a column from the drop-down list immediately below the last column selected. Repeat this step for each column you want to add to the key (up to 16 columns for range and hash partitioned tables).

5. If a table is subpartitioned, select columns for the table’s subpartitioning key.

6. Select partitioning key columns for global indexes

   If a table has partitioned global indexes, the first one is selected after you apply key columns to the table. (Nonpartitioned global indexes and local indexes are skipped.)

   Note: Columns in the key for a global index must be in the same order as columns that define the index.

7. Repeat Steps 2-6 for additional tables and global partitioned indexes on the Object list.

8. If you want to make changes after key columns have been applied, select an object in the Object list and make one or more changes.

   • To add a column, click below the last column selected.
   • To remove a column, select the column and press the DELETE key.
9. Click Next to proceed to the next page. See Specify Partitioning Parameters for next steps.

Specify Partitioning Parameters

Use the Partitioning Parameters page to set partitioning options for individual objects. These options let you automate various aspects of partitioning. For example, they let you automatically set partitioning key values and select target tablespaces.

Partitioning options are organized in three panels. A panel is displayed for an object if the options it contains can be used for that object.

Initially, most options in this page use default settings from the Partitioning Wizard pages in the Options dialog (Tools | Options). You can override defaults as needed. The settings selected for a partitioned object apply to all of its partitions and subpartitions.

To set partitioning options for an object

1. Select a table or global index in the Object list.

2. Key Values and Sizing. Select the Automatically set upper bounds / list values... option if you want the Partitioning Wizard to automatically set partitioning key values so that rows are distributed equally and partitions are sized evenly.

   How the “automatically set” option is labeled depends on the partitioning method for an object:

   • Automatically set upper bounds for partitions to evenly distribute rows among partitions is displayed when range is the partitioning method. In this case, the option tells the wizard to automatically set the high values to use as the upper boundary for range partitions.

   • Automatically set list values for list partitions to evenly distribute rows among partitions is displayed when list is the partitioning method. In this case, the option tells the wizard to automatically specify the list values to use for list partitions and subpartitions.

     Note: Partitioning key values can be set automatically for objects that contain at least one row per partition or subpartition.

3. Select the Estimate partition sizes based on row count option if you want the Partitioning Wizard to automatically calculate partition sizes based on expected row count.

   Note: The estimate sizes option should not be used when the option for automatically setting partitioning key values is selected. When both are selected, space allocation is estimated twice.

4. Select the Validate partitioning key values against existing data option if you want the Partitioning Wizard to automatically check partitioning key values against existing data.

   This ensures that a table and its local indexes contain partitions or subpartitions for all rows when MAXVALUE or DEFAULT is not used as a partitioning value.

   To validate partitioning key values, the Partitioning Wizard checks them against all rows in a table. (This may take a long time for large tables.) When the wizard finds rows that cannot be assigned to any partition or subpartition, a message displays in the Partition Values window. Use information in the message to adjust partitioning key values.

   Note: The validate option is ignored for tables when MAXVALUE or DEFAULT is used as a partitioning value.

5. Table Scan Options. Select a table scan option to use for the Key Values and Sizing method/methods you specified.

   • Scan entire table if less than N KB—Select this option to sample a table or determine whether to sample or scan based on its size. Also specify a threshold size and sample size.
     • Threshold size—Enter a threshold size in kilobytes in the field beside Scan entire table if less than N KB. The table is sampled if its size is equal to or larger than this. The entire table is scanned if its size is smaller than this. To sample a table regardless of size, enter 0.
     • Sample size—Enter the percentage of table rows to sample in the Otherwise sample table at field. The percentage can have two decimal points, for example, 50.25%.
   • Scan entire table—Select this option to scan the entire table, regardless of size.

   Table scan options determine whether tables are scanned or sampled when the Partitioning Wizard sets key values or estimates sizes. You can choose to scan entire tables, sample a percentage of table rows, or dynamically determine whether to scan or sample based on table size.

   A full table scan may take a long time, depending on table size. Sampling a table takes less time and may be the best choice for very large tables. Although all rows are not read, they are evenly sampled so that results accurately represent a table.

6. Use tablespace options to set a global target-tablespace strategy for a partitioned table and its local indexes or for a partitioned global index. You can override global tablespaces in the Customize Object Allocations page.

   1. Select a target tablespace option as follows:
      • Use the parent object’s default tablespace for (sub)partitions—Select this option to use a parent object’s default tablespace for all its partitions or subpartitions, new and existing. If you want existing partitions or subpartitions to remain in their current tablespace, select that tablespace in the Customize Object Allocations page.
      • Use the tablespaces used by current (sub)partitions in a round-robin distribution—Select this option to use current tablespaces for an object’s partitions or subpartitions. If an object is being partitioned, its current tablespace is used for all of its partitions and subpartitions. If an object is already partitioned, the current tablespaces of its existing partitions or subpartitions are used. Segments are distributed among the tablespaces in an alternating fashion. Each tablespace is used in turn for the next partition or subpartition in a script.

      • Use the following tablespaces for (sub)partitions in a round-robin distribution—Select this option to specify the tablespaces to use for an object’s partitions and subpartitions. You can select multiple tablespaces from the Tablespace Name grid. The tablespaces you specify are used for both new and existing partitions and subpartitions. Segments are distributed among the tablespaces in an alternating fashion. Each tablespace is used in turn for the next partition or subpartition in a script. Tablespaces are used in the order displayed in the grid. To change tablespace order, drag and drop tablespace rows to new positions in the grid.

        Tip: To create a tablespace, type a name for it in the grid, press Enter, and click Yes. When Tablespace Properties displays, specify properties and click Generate Script. A script to create the tablespace runs automatically.

   2. Set the option for local indexes if you want to locate each local index partition or subpartition in the tablespace used for its associated table partition or subpartition. The option is labeled Put local index (sub)partitions in the tablespaces of their table (sub)partitions. Local index segments are kept with their table segments regardless of the target tablespace option selected. When the local index option is not selected, local index segments are assigned to tablespaces based on the target tablespace option selected. (This option is not displayed when a global index is selected in the Object list.)

7. Click Next to proceed to the next page. See Specify Partitioning Key Values for next steps.

Tip: You can specify default values for some Partitioning Wizard options in Tools | Options | Partitioning Wizard. See the Partitioning Estimates and Partitioning Key Options pages.

Best Practices for the Partitioning Wizard

If you select different target tablespaces in the Partitioning Wizard, be aware of how the new tablespace fits in with the strategy selected in the Partitioning Parameters page:

• Strategy for locating local index segments with table segments—This strategy automatically locates local index partitions or subpartitions in the tablespace for their table partitions or subpartitions. If you select a different tablespace for an index segment, it will not be located with its table segment.
• Strategy for using a parent object’s default tablespace—This strategy automatically uses the default tablespace of a parent table or index for all of its partitions or subpartitions, both new and existing. If you want existing partitions or subpartitions to remain in their current tablespace, select that tablespace in the Customize Object Allocations page.
• Strategy for using current tablespaces—This strategy automatically uses current tablespaces for an object’s partitions or subpartitions. These are the tablespaces where the parent object or existing partitions or subpartitions are currently located. Segments are distributed among the tablespaces in an alternating fashion. Each tablespace is used in turn for the next partition or subpartition in a script. This strategy is designed to distribute segments for optimal I/O balancing.  If you select a different tablespace for a segment, it should be one that will promote I/O balancing.
• Strategy for using selected tablespaces—This strategy automatically uses one or more tablespaces you specify for an object’s partitions and subpartitions. Segments are distributed among the tablespaces in an alternating fashion. Each tablespace is used in turn for the next partition or subpartition in a script. strategy is designed to distribute segments for optimal I/O balancing.  If you select a different tablespace for a segment, it should be one that will promote I/O balancing.

Specify Partitioning Key Values

Specify Partitioning Key Values

The Partition Key Values page of the Partitioning Wizard is used to specify the key-column values used for determining each partition.

About the Partition Key Values Page

The page title, Range Partition Key Values or List Partition Key Values, is determined by the partitioning method you selected.

If you selected the Automatically set... option in the Partitioning Parameters page, the window displays values set by the Partitioning Wizard. If you did not select this option, use this page to manually specify partitioning key values.

Values grids and lists identify partitioning key columns and their datatypes. This lets you see the types of values that can be specified as partitioning boundaries. If you chose to automatically set partitioning values, the Partitioning Wizard’s values are displayed in the grid or list for an object.

If an object is already partitioned, you can change partitioning key values as needed. You must readjust key values if you modify the specified key columns or the number of partitions.

Before specifying key-column values, you may want to review Best Practices for Partitioning Key Values.

About Partitioning Key Values

The key-column values define the boundaries that determine how rows are assigned to partitions and subpartitions.

• The boundary for a range partition determines the highest partitioning key column value its rows can contain. The boundary is not inclusive. The partition will include all values less than (but not equal to) the value you specify.
• The boundary for a list partition or subpartition consists of the list of values its rows must contain.

Manually Specify Key Values

If you selected the Validate partitioning key values option, the Partitioning Wizard checks values against all existing data in a table to make sure there is a partition or subpartition for every row.

Based on message information, you can adjust values so that all data fit in a partitioned object. This lets you avoid the error that occurs when rows cannot be assigned:

“ORA-14400: Inserted partition key does not map to any partition”.

Note If you selected the Estimate partition sizes based on row count option, be aware that key values you specify may change the partition sizes automatically estimated by the Partitioning Wizard.

To manually specify Range partitioning key values

1. Select a range-partitioned table or global index in the Object list.

2. For the first range partition, specify a high value to use to define the partition boundary.

   1. Click in the field for the first partitioning key column and enter a value for that column. The partition will include values less than (but not equal to) the value you specify.

   2. If the partitioning key uses more than one column, press Tab to move the cursor to the second column in the key. Enter a boundary value for the second column. Repeat for each column in the key.

   See Key Value Grids for Range-Partitioned Objects for more information about object and partition grids.

3. Specify boundary values for the remaining range partitions.

   MAXVALUE is automatically entered in the last partition and indicates that the highest value in the column is used as the boundary value for that partition.

4. Use the key values grid for the first partition to check high values. Modify values, if needed. Repeat this step for each partition in a range-partitioned object.

5. After specifying all partition key values, click Next to proceed to the next page. See Specify Partition Names for next steps.

To manually specify List partitioning key values

1. Select a list partition or subpartition in the Object list.

2. Specify a list of values for the partition or subpartition

   1. Click Get existing values to populate the Existing Values list with existing column values. For a partition, all values in the column are retrieved. For a subpartition, column values for rows that belong to the parent partition are retrieved.
   2. Select the values to include in the partition or subpartition. To do this, use the UP and DOWN arrows to move values to the Partitioning Key Values list for that partition.
   3. Adjust the list, if needed. To delete a value, select it and press the DELETE key. To refresh the Existing Values list, click Get existing values.

   See Key Value Lists for List Partitioned Objects for more information about object and partition lists.

3. Repeat for each partition or subpartition in a list-partitioned object.

   DEFAULT is automatically entered as the value for the last partition. This indicates that any values not entered in the previous partitions are included in the last partition.

4. After specifying all partition key values, click Next to proceed to the next page. See Specify Partition Names for next steps.

Note: If tables have constraints that can be validated for use in posting live transactions, a message prompts you to validate the constraints when you attempt to proceed from the Partition Key Values window. See Respond to Checks for a Posting Index for more information.