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
Select the first column to use for the table’s partitioning key.
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.
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).
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.
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.
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
Select a table or global index in the Object list.
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 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.
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.
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.
Table Scan Options. Select a table scan option to use for the Key Values and Sizing method/methods you specified.
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.
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.
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.
Click Next to proceed to the next page. See Specify Partitioning Key Values for next steps.
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:
The Partition Key Values page of the Partitioning Wizard is used to specify the key-column values used for determining each partition.
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.
The key-column values define the boundaries that determine how rows are assigned to partitions and subpartitions.
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
Select a range-partitioned table or global index in the Object list.
For the first range partition, specify a high value to use to define the partition boundary.
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.
See Key Value Grids for Range-Partitioned Objects for more information about object and partition grids.
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.
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.
To manually specify List partitioning key values
Select a list partition or subpartition in the Object list.
Specify a list of values for the partition or subpartition
See Key Value Lists for List Partitioned Objects for more information about object and partition lists.
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.
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.