What Is Not Supported for the Partitioning Wizard
The following Oracle features are not supported for the Partitioning Wizard.
| Category | Oracle Feature Not Supported |
|---|---|
| Tables and Indexes |
Indexes without their tables To reorganize an index online independently of its table, perform a standard reorg with Rebuild ONLINE |
|
Temporary tables | |
|
Tables with dimensions | |
|
Advanced Queuing tables | |
|
Replication tables; objects that are part of a replication group | |
|
External tables | |
|
Domain indexes (including interMedia text indexes and ConText indexes) | |
|
Tables owned by SYS or SYSTEM or with names starting with QUEST_% | |
| IOTs and Clusters |
IOTs and IOT overflows |
|
Clusters, clustered tables, cluster indexes, indexes on clustered tables | |
| Materialized Views |
Materialized views |
|
Materialized view logs | |
| LONG Data Types |
LONG and LONG RAW data types |
| User Defined Data Types |
Object tables (data types for tables) |
|
Nested tables | |
| Various Data Types |
URI data types |
|
XMLType columns and tables | |
| Miscellaneous |
Oracle Label Security |
|
Tables with security policies enabled |
Notes:
- Destination tables cannot be system or reference partitioned.
- Columns with user defined datatypes (UDTs) cannot be used in new partitioning keys. They are preserved in existing partitioning keys as long as you do not change partitioning method or number of partitions. This applies to all supported UDTs. Also, index used as posting index must not contain an object type attribute in its column list.
Key Concepts for the Partitioning Wizard
This section describes key concepts for partitioning. Refer to this section as you use the Partitioning Wizard.
Partitioning Wizard BasicsBefore you make changes to an object, select it in the Object list.
When an object is selected in the Object list, all options apply to that object. The last object selected in one window remains selected when you proceed to the next window.
Any settings specified for an object are applied when you select another object. You can change settings multiple times before generating a partitioning script. Changes do not go into effect in your database until the script is run.
A partition is a section of a table or index that has been divided into smaller parts that can be managed individually. Table and local index partitions can be divided into subpartitions.
To divide an object into partitions or subpartitions, you specify a partitioning scheme. This consists of partitioning method, number of partitions, number of subpartitions (optional), and a partitioning key. The key designates the columns and column values to use in defining the boundaries of partitions and subpartitions. Rows are assigned to a partition or subpartition if their values fall within the boundaries for that partition or subpartition.
Each partition is stored in its own segment and can have its own physical attributes such as TABLESPACE and PCTFREE. However, all partitions in a table or index share logical attributes such as column definitions and constraints.
When a table is subpartitioned, data is stored in the segments of the subpartitions. The partitions themselves are logical structures only. All subpartitions share the same logical attributes. They inherit most of the physical attributes of their parent partition or parent object. Only the TABLESPACE attribute can be specified for subpartitions. Locating partitions or subpartitions in different tablespaces on different storage devices promotes optimal I/O performance.
Range partitioning divides a table or index based on ranges of sequential values such as dates. Each partition contains one range of values. Range values increase in order from first partition to last partition. The first partition contains the lowest range of values. The second partition contains a higher range of values. The last partition contains the highest range of values.
The boundaries for ranges are defined with a partitioning key. This determines the highest column values (upper boundary) each partition can contain. MAXVALUE. can be used for columns in the last partition. This setting allows a partition to include rows with values that are too high for other partitions. It ensures that there is a partition for every row in a table or index.
Range partitioning allows ordered and related sets of data to be grouped together. When a table is range partitioned, its partitions can be subpartitioned with the hash or the list partitioning methods.
To range partition a table or global index, you specify:
- Partitioning method—Range.
- Number of partitions—One or multiple partitions.
- Partitioning key columns—One to 16 table or index columns. These should:
- Ensure that data is distributed evenly among partitions.
- Define an ordered list. For example, to partition by date, the first column might be a year column, the second column might be a month, and the third column might be a day column.
- Partitioning key values—One high value per partitioning key column for each partition. High values for at least one column should be in increasing order from first partition to last partition. For example, if a partitioning key uses a month column, key values for that column should be higher for each successive partition.
Hash partitioning divides an object based on a “round robin” distribution of rows. Oracle evenly distributes rows among partitions or subpartitions using a hash of values in partitioning key columns. You do not need to specify partitioning key values as you do with range and list partitioning.
Hash partitioning is useful when you do not know data values well enough to specify range boundaries or list values that would provide evenly-sized partitions.
Hash partitioning and subpartitioning is available for tables in databases that are Oracle 11 or later. Hash partitioning is also available for global indexes in databases that are Oracle11 or later.
To hash partition a table, you specify:
- Partitioning method—Hash.
- Number of partitions—One or multiple partitions. An even number helps to ensure that rows are distributed evenly among partitions.
- Partitioning key columns—One to 16 table or index columns.
- Partitioning key values—None.
List partitioning divides an object based on lists of discrete values. Rows are assigned to a list partition or subpartition if their values are on its list. This type of partitioning allows unordered and unrelated sets of data to be grouped together. It gives you precise control over how rows are assigned to partitions.
List partitions cannot be subpartitioned. However, range partitions can have list subpartitions.
To list partition a table, you specify:
- Partitioning method—List.
- Number of partitions—One or multiple partitions.
- Partitioning key columns—One table column.
-
Partitioning key values—A list of discrete values for each partition or DEFAULT.
Note One list partition can use DEFAULT as its value instead of a list of values. The DEFAULT partition is used for rows that do not contain values that match lists for other partitions.
Range-hash partitioning combines range partitions and hash subpartitions in a two-level hierarchy. First, a table is divided into range partitions based on ranges of of values. Then, each range partition is divided into hash subpartitions based on a hash of values in subpartitioning key columns.
When a table is range-hash partitioned, data is stored in the segments of the subpartitions. The partitions themselves are logical structures. All subpartitions share the same attributes. They inherit most from their parent partition or parent table. Only the TABLESPACE attribute can be specified for subpartitions.
To use composite range-hash partitioning, you specify:
|
For the table |
|
|
For partitions |
|
Range-list partitioning combines range partitions and list subpartitions in a two-level hierarchy. First, a table is divided into partitions based on ranges of values. Then, each range partition is divided into list subpartitions based on lists of discrete values. There is one list per subpartition. You might use range partitions to group data by sales quarter and list subpartitions to group it by certain states for each quarter.
When a table is range-list partitioned, data is stored in the segments of the subpartitions. The partitions themselves are logical structures. All subpartitions share the same attributes. They inherit most from their parent partition or parent table. Only the TABLESPACE attribute can be specified for subpartitions.
To use composite range-list partitioning, you specify:
|
For the table |
|
|
For partitions |
|
Partitioning keys determine which partition or subpartition rows are assigned to. Each partitioning key consists of partitioning key columns and column values. These are used as follows:
- Partitioning key columns tell Oracle which table or index columns to match rows against. The partitioning key for range- and hash-partitioned tables uses up to 16 columns. The partitioning key for list-partitioned tables uses one column. The partitioning key for ranged-partitioned tables uses an ordered list.
- Partitioning key values are the column values that define the boundaries for partitions and subpartitions:
- Range partitioning keys use high values as boundaries. A row is assigned to a range partition if its value is lower than the boundary for that partition. For each partition, you specify one high value per partitioning key column. The high values for at least one column should be in increasing order from first to the last partition.
- List partitioning keys use lists of discrete values from a single key column as boundaries. A row is assigned to a list partition or subpartition if it contains a value on the list for that partition or subpartition. Lists should not contain duplicate values.
- Hash partitioning keys do not require user-specified values. Instead, Oracle uses a hash function to determine values for key columns.
As you define a partitioning scheme, you specify one set of partitioning key columns per parent object. If a table is being subpartitioned, you also specify a set of subpartitioning key columns. You then specify partitioning key values for each partition or subpartition.
A local index is partitioned in the same way as its table. It has the same number of partitions and subpartitions and uses the same partitioning key. Each index partition or subpartition corresponds to a certain table partition or subpartition.
Local indexes are automatically maintained along with their tables. When you specify Local as the locality for an index, the Partitioning Wizard applies the table’s partitioning scheme to the index. When the table’s partitioning scheme is changed, the index is automatically changed in the same way. For example, if you add a partition to the table, a corresponding partition is added to the index.
You cannot modify partitioning for a local index independently of its table. You can, however, modify the names of its partitions and subpartitions. You can also modify physical attributes for the index.
Note When a local index is unique, the columns used for its table’s partitioning key must be one or more of the columns that define the index. If a table has multiple unique indexes, the columns used for the partitioning key must be common to all of the indexes.
A global index is partitioned independently of its table. The index can be partitioned or nonpartitioned no matter how its table is structured. If both are partitioned, the partitioning scheme for the index can be different from that of the table. Index partitions do not correspond to certain table partitions.
For global indexes in databases that are 11 or later, the partitioning method can be range or hash. Columns in the partitioning key must be ordered in the same ways as columns in the index. If range partitioning is used, the high value for the last partition must be MAXVALUE. The Partitioning Wizard enters this value automatically and prevents use of another value.
Note Global indexes can only be partitioned if their first column can be used in a partitioning key. Columns in the partitioning key must in the same order as columns in the index.
View Partitioning Schemes
Tabs in the Partitioning Wizard are designed to let you easily review partitioning schemes as you define them:
-
Objects to be Reorganized—This tab identifies which parts of a partitioning scheme have been defined for each object. If an object is already partitioned, only changes are reflected in the Objects and Partitions tab. For example, if you change partitioning key values for a range partitioned object, a Y for “yes” is displayed in the Values column. But the Actions and Keys columns are blank.
Note: The Objects to be Reorganized tab is displayed in all windows where options can be selected for individual objects. This tab is not displayed in windows where options are used globally for all objects on the list.
-
Space Usage Summary—This tab allows you to check space availability in target tablespaces.
A summary displays on the Schedule Script Execution page to allow you to review the reorg method, a list of objects included in the reorganization, and the switch types to be used.
Specify Partitioning Method
Use the Partitioning Method page of the Partitioning Wizard to specify partitioning method, index locality, and number of partitions and subpartitions. You can specify a default number of subpartitions for an object or specify a number for each partition. Please note that LOBs are automatically partitioned in the same way as their tables.
For an overview of the Partitioning Wizard, see Smart Start for the Partitioning Wizard.
If an object is already partitioned, the Partitioning Method page displays its current partitioning method, number of partitions, index locality, and segment names. You can change partitioning method, unpartition an object, convert to composite partitioning, add or remove partitions or subpartitions, change index locality, and change names.
After you change method settings, the Partitioning Wizard tries to preserve partitioning key settings, if possible. The wizard displays a message when new settings are invalid or cause other settings to become invalid. You can adjust invalid settings based on information in the message.
Tip: You can save Partitioning Wizard settings as a Reorg Plan to edit and reuse later. See Save Reorg Manager Settings for more information.
To specify the partitioning method
- Launch the Partitioning Wizard. See Launch Partitioning Wizard for more information.
- Select a table in the Objects to Reorganize list.
- Select a partitioning method for the table from the Partition by field.
-
Enter number of partitions in the Number of partitions field.
A table can contain one partition or multiple partitions. When hash is the partitioning method, enter an even number to ensure that rows are distributed evenly. If you do not want to add subpartitions to the table, click Apply.
Tip: To convert a partitioned object to a nonpartitioned object, enter 0 as the partition count.
-
Select Create subpartitions to subpartition the table. From the Subpartition by field, select a partitioning method for all partitions.
-
If you want all partitions in a table to contain the same number of subpartitions, specify a default number in the Default number of subpartitions field. Then click Apply. Skip this step and proceed to the next if you want to customize the number of subpartitions per partition.
-
If you want to vary the number of subpartitions per partition, select the Partition-level subpartitioning checkbox. Specify the number of subpartitions each partition should contain in the # Subpartitions column. Then click Apply.
Note: If most partitions should contain the default number of subpartitions, click Reset subpartitions to default to populate the # Subpartitions column with the default number. Then modify the number of subpartitions for individual partitions.
-
If a table has indexes, specify locality for each index. When locality is global, you can also specify the number of partitions and partitioning method.
Select an index in the list and then select one of the following options:
- Local—Select Local to partition an index in the same way as its table, with the same number of partitions and subpartitions, and the same partitioning key columns and values. If Local is the default setting, partitions and subpartitions for all indexes on a table are added to the Object list when you click Apply for the table.
-
Global—Select Global to partition an index independently of its table. Then enter a number in the Number of partitions field. (Enter 0 if an index should be non-partitioned.) If your database is Oracle 11 or later, also select a partitioning method from the Partition by field. Click Apply to apply all settings.
Note: If you change partitioning for an index but do not change partitioning for its table, a message asks if you want to reorganize the table along with the index. Click Yes to include the table in the partitioning script. If you click No, you must make changes to the table in a later partitioning-related window. If no changes are made, the table and its indexes are excluded from the script.
-
If you want to change names for all new and existing partitions and subpartitions in an object, click Change Default Names. (You can change names for individual segments in the Partition Names window.) The Default Partition Names dialog displays. Enter names as follows:
- Prefix for default partition names—Enter a name for partitions. (P is the wizard’s default.) This is used for all partitions in a table and its local indexes, including existing partitions. It is used for all partitions in a global index. A sequential partition number is appended to the name of each partition to guarantee that it is unique.
- Prefix for default subpartition names—Enter a name for subpartitions. (SP is the wizard’s default.) This is used for all subpartitions in a table and its local indexes, including existing subpartitions. A sequential subpartition number is added at the end of each name; the parent partition name is added at the beginning.
- Include base object name in default partition names—Select this checkbox to include the name of the parent table or parent index in the names of new partitions and subpartitions. The parent name is added at the beginning of each partition or subpartition name.
Click OK to close the Default Partition Names dialog. Then click Apply to apply new names.
Note: Names are stored in uppercase characters if you do not enter them with double quotes. Each name can have up to 30 characters. That maximum is shared by all parts of a name, including numbers and parent names. If all parts add up to more than 30 characters, certain parts are truncated. However, sequential numbers are always included.
- Click Next to proceed to the next page. See Specify Partitioning Key Columns for next steps.
Tip: You can specify default values for some partitioning options in Tools | Options | Partitioning Wizard | Defaults. See Partitioning Wizard Defaults.