The following Oracle features are not supported for the Partitioning Wizard.
|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
Tables with dimensions
Advanced Queuing tables
Replication tables; objects that are part of a replication group
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 view logs
|LONG Data Types
LONG and LONG RAW data types
|User Defined Data Types
Object tables (data types for tables)
|Various Data Types
URI data types
XMLType columns and tables
Oracle Label Security
Tables with security policies enabled
This section describes key concepts for partitioning. Refer to this section as you use the Partitioning Wizard.
Before 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:
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:
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 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
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
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:
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.
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.
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
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:
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:
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.
Tip: You can specify default values for some partitioning options in Tools | Options | Partitioning Wizard | Defaults. See Partitioning Wizard Defaults.