Composite Partitioning

Composite Partitioning

Composite partitions are hash-method subpartitions of a range-method-partitioned table. Good for historical data and striping. Provides benefits of regular range-partitioned tables such as better manageability as well as including the parallelism and data placement benefits of hash partitioning.
Composite-partitioned tables and indexes are logical structures. Their data is actually stored in the segments of their subpartitions.
You can define range-partitioned global indexes.
You can name the subpartitions and specify specific tablespaces to store them in.
Indexes on composite partitions are local and are stored BY DEFAULT in the same tablespace as the table subpartition. Can specify different storage parameters using the following command:
CREATE INDEX index_name ON table_name (col) LOCAL STORE IN (tsp_1, tsp_2, tsp_3);
Example of Composite Partition:
CREATE TABLE employee
(emp_no NUMBER,
emp_name VARCHAR(32),
salary NUMBER)
PARTITION BY RANGE (emp_no) SUBPARTITION BY HASH (emp_name)
SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
(PARTITION p1 VALUES LESS THAN (100) PCTFREE 40,
PARTITION p2 VALUES LESS THAN (200) STORE IN (ts2, ts4, ts6, ts8),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
(SUBPARTITION p3_s1 TABLESPACE ts4,
SUBPARTITION p3_s2 TABLESPACE ts5));
Must specify:
partition method (range)
partition key
partition descriptions and bounds
subpartition method (hash)
subpartition columns
number of subpartitions per partition
Mark a subpartition of a local index unusable as follows:
ALTER INDEX ...MODIFY SUBPARTITION ....UNUSABLE;
Rebuild the subpartition of a local index using the following command:
ALTER INDEX ... REBUILD SUBPARTITION ...
TABLESPACE tabspace10 PARALLEL (DEGREE 2);
Rename an index subpartition using the following command:
ALTER INDEX index_name RENAME SUBPARTITION current_name TO new_name;
Rename a table subpartition using the following command:
ALTER TABLE table_name RENAME SUBPARTITION current_name TO new_name;
Exchange partitions to convert a subpartition of a table into a non-partitioned table using the following command:
ALTER TABLE table_name EXCHANGE SUBPARTITION partition_name
WITH TABLE new_table_name INCLUDING INDEXES;
Add a subpartition using the following command:
ALTER TABLE table_name MODIFY PARTITION range_partition_name
ADD SUBPARTITION new_subpartition_name TABLESPACE tabspace10 ;
The new subpartition is populated with rows from the existing subpartitions for the partition specified, using the hash function.
Coalesce a subpartition using the following command:
ALTER TABLE table_name MODIFY PARTITION range_partition COALESE SUBPARTITION ;
Move a subpartition using the following command:
ALTER TABLE table_name MOVE SUBPARTITION sub_partition_name TABLESPACE new_tablespace;
When moving a subpartition, local index subpartitions corresponding to the subpartition are marked UNUSABLE unless they are empty.
Truncate a subpartition using the following command:
ALTER TABLE table_name TRUNCATE SUBPARTITION sub_partition_name DROP STORAGE;
Can verify the creation of composite partitions by querying the DBA_SEGMENTS data dictionary view.
Example:
SELECT segment_name, partition_name, segment_type
FROM dba_segments
WHERE segment_name = 'parent_table_name';
The ANALYZE TABLE command will analyze all subpartitions of a table even though the command only references the table.
Example: ANALYZE TABLE table_name COMPUTE STATISTICS ;
If you want to EXPORT only a sub-partition you can only use TABLE mode of export command and you must specify the table_name and subpartition.
Table-level export is when a table and all it’s dependent indexes, partitions and subpartitions are exported, when specifying just the table for export. This table-level export is supported in all export modes.

Partitioned Index-Organized Tables, Large Objects, and Object Types
General
The following attribute types can NOT be part of a partition key:
1. an object
2. a REF
3. part of a nested table
4. a VARRAY

LOB’s and Partitioned Tables
Partitioned tables can contain LOB columns.
A partition key CAN NOT contain a LOB column.
LOB data is always equipartitioned with the table it is part of. In other words the LOB data is partitioned on the same field as the parent table's partition keys.
There is an LOB data segment that corresponds to the data partition and a LOB index segment that corresponds to the index partition for every partition of a partitioned table that contains an LOB. These segments contain the LOB that belongs to each row in the associated partition. Note that LOB data and index data are not separate entities. The index is an integral part of the LOB column storage and is created and maintained by the system.
The information in the previous paragraph also holds true for composite partition tables as well.
LOB index partitions will always be stored in the same tablespace as the LOB data partitions they correspond. They are co-located with the data partitions.
You cannot directly specify any attributes for a LOB index or any of its partitions. Instead, they are taken from those of the LOB data partition and/or the tablespace the LOB lives in.
When using hash partitioning, tablespace is the only storage attribute that can be specified at the LOB partition level. The remaining storage attributes are set at the table level LOB column definition.
You can specify non-hash LOB storage characteristics at the partition or table level.
Rules for determining the tablespace of an LOB data partition:
The tablespace specified for the LOB data partition
Otherwise, the default tablespace specified at the table level for all LOB partitions of a given LOB column.
Otherwise, stored in the same tablespace as the table partition to which it corresponds.

To view the table level info and default attributes of LOB data partitions reference the DBA_PART_LOBS view.
To modify storage attributes for a partitioned LOB column use the ALTER TABLE table_name MODIFY PARTITION statement.
If you need to add a new partition for an LOB column on a table that is already partitioned the ALTER TABLE table_name ADD PARTITION command.

Partitioned Index-Organized Tables
Rules for partitioning an index-organized table:
- Can only use range partitioning
- The partition columns have to be a subset of the primary key columns
- You can partition secondary indexes, locally and globally
- The overflow data segments are always equipartitioned w/the table partitions
- You can specify storage attributes at the table or partition level on both the table data and the overflow data
Characteristics of an index-organized table with Overflow:
- Each partition has an index segment and an overflow data segment
- Overflow data segments are equipartitioned with the primary key index segments.
- Can specify default values for physical attributes at the table or partition level
- Attributes specified before the overflow keyword apply to the primary key index segment, and those specified after overflow keyword apply to the overflow data segment.
- You can only specify values for PCTTHRESHOLD and INCLUDING column at the table level.
- If an overflow tablespace location is not specified at the partition level, the tablespace specified at the table level is used. If not specified at the table level, overflow is stored in the tablespace of the corresponding partition index segment.
- SYS_IOT_TOP_Pn and SYS_IOT_OVER_Pn are the system-generated names for the index and overflow segment names, respectively.

Partitioned Object Tables
Composite indexes are supported on partitioned object tables.
You can use range, hash and composite partitioning on partitioned object tables.
You can use global indexes on range partitions of both composite and non-composite partitioned tables.

Partitioned Tables With Object Types
Tables containing object types can be partitioned.
Object attributes CANNOT be part of the partition key.
The object identifier(OID) can be user defined, but the partition key of an object table CANNOT explicitly use an OID.
Some or all of the columns of a user-defined OID can be used in the partition key.