Table Partitioning

The different partitioning types
The only partitioning mode available in version 8.0 was Range Partitioning
Version 8.1.5 brought two new partitioning modes: Hash, Composite
Index only tables can also be partitioned.


Restrictions:

Clustered tables cannot be partitioned
A LONG or LONG RAW column in a table prevents it from being partitioned. BLOB type columns, on the other hand, may be partitioned (cf. chapter V).
Index Only Tables (IOT) can only be RANGE partitioned.
Multi-column partition keys are limited to 16.

The following types of column cannot be part of a partition key:
LEVEL or ROWID pseudo-types
Columns of the following types:
Nested table
Varray
Object type
Ref
Rowid
Different types of LOB (BLOB,CLOB,NCLOB,BFILE).

Range type partitioning

 Table and index partitions are based on a list of columns allowing to store each occurrence in a given partition.

CREATE TABLE emp_range(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
sal NUMBER(7,2))
PARTITION BY RANGE(EMPNO)
(
partition emp_p1 VALUES LESS THAN (50),
partition emp_p2 VALUES LESS THAN (100),
partition emp_p3 VALUES LESS THAN (150),
partition emp_p4 VALUES LESS THAN (MAXVALUE));

Each partition is defined with an upper boundary. The storage location of each occurrence is then found be comparing the partitioning key of the occurrence with this upper boundary. This upper boundary in non-inclusive. That means that the key of each occurrence must be strictly inferior to this limit for the record to be stored in this partition.

The storage options are set a table level and are directly inherited by the partition level if they are not redefined by each partition.

The only possible functions in the 'VALUE LESS THAN (value1, value2 ..., valueN)'
clause are the TO_DATE and RPAD functions.
The NULL value can be associated to a partitioning key column and is considered to be superior to any other possible value on this column except the MAXVALUE column. This is a constraint. It imposes that the upper limit of the top level partition be set to MAXVALUE else the occurrences containing a NULL value on this column would not be able to fit in this last partition and generate an ORA-14400 error.

Hash type partitioning

 This type of partitioning allows a better mastering of the distribution of the data in the different partitions. These partitions are defined with the help of a hashing function offered by Oracle. This function is then applied to a list of columns.

CREATE TABLE emp_hpart(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
sal NUMBER(7,2))
STORAGE (INITIAL 50k NEXT 50k)
PARTITION BY HASH(sal)
(PARTITION H1 TABLESPACE data01,
PARTITION H2 TABLESPACE data02,
PARTITION H3 TABLESPACE data03
PARTITION H4 TABLESPACE data04);

This partitioning method is recommended in the following cases:

- Impossible to have a criteria for the distribution of data
- Difficult to anticipate the quantity of data for a given partition
- Hard to balance the load in each partition

This partitioning method requires that the number of partitions allocated be a power of 2 in order to ensure a uniform distribution of data in the different partitions.

Specific storage clauses cannot be specified for each partition. These clauses are inherited from the tablespace in which the partitions reside.

The administration of these HASH type partitions is similar to that of the RANGE ones except for DROP, SPLIT and MERGE. The ADD and COALESCE commands are used to add and drop partitions.

Composite type partitioning

 This method combines the two sorting methods described previously. The first level being based on the RANGE method and the second, finer grained, is based on a HASHing function. The benefit obtained is the following: The first level sorts the data on a logical basis while the second balances its distribution among each one of the partitions.

CREATE TABLE emp_composite(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
sal NUMBER(6))
STORAGE (INITIAL 12k NEXT 12k)
PARTITION BY RANGE(empno)
SUBPARTITION BY HASH(sal) SUBPARTITIONS 4
STORE IN (DATA01, DATA02, DATA03, DATA04)
(PARTITION p1 VALUES LESS THAN (50),
PARTITION p2 VALUES LESS THAN (100),
PARTITION p3 VALUES LESS THAN (150),
PARTITION p4 VALUES LESS THAN (MAXVALUE));

This query presents a RANGE type first level partitioning based on a NUMBER type column (EMPNO). The second level, HASH type partitioning, also based on a NUMBER type column (SAL) partitions each of these partitions into 4 sub-partitions.

At the RANGE level the partitioning is a logical sort . The data is then physically stored in the sub-partitions(HASH). The storage at this level can be visualised in the DBA_TAB_SUBPARTITIONS view.

select partition_name, subpartition_name, position, tablespace_name
order by 1,2,3,4;

Version 8.1.5 offers new views to administer this level of granularity.
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
DBA_SUBPART_KEY_COLUMNS
DBA_IND_SUBPARTITIONS
DBA_LOB_SUBPARTITIONS
DBA_TAB_SUBPARTITIONS

COMPOSITE partitioning preserves the advantages of the two previous methods:

- Ease of administration tied to the RANGE type
- Finer granularity level in the storage of data
- Possibility of setting local indexes at subpartition level, combined with global indexes at RANGE level.
- The HASH mode enables PDML operations on a parallel mode.

Partitioning Index Only Tables

 Index Only Tables can now be partitioned as of version 8.1.5.
Reminder: IOT have a BTREE index structure, the difference being that the leaves of this index contain the rows of the table. The rows of the table are sorted on the primary key with is essential to the creation of each IOT. It is possible to leave an overflow zone when creating an IOT. It is called 'overflow' and contains the part of the row that does not fit in the primary key and that has to be stored in a particular area. The aim of this additional segment being to limit the size of the rows stored in each IOT block.

IOT partitioning is only possible under certain conditions:
- The partitioning must be of RANGE type.
- The partitioning key(s) must be a subset of the primary key.
- The overflow zones must be equi-partitioned with IOT partitions.

Oracle imposes that the partitioning key be a subset of the primary key or included in the latter which allows unique constraint checking amid a partition. Moreover if this partitioning key is prefixed, then the queries based on the partitioning predicate will directly retrieve the rows stored on the primary key.

CREATE TABLE emp_iot(
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
sal NUMBER(7,2))
ORGANIZATION INDEX INCLUDING ename OVERFLOW
PARTITION BY RANGE(empno)
(
partition emp_p1 VALUES LESS THAN (50) TABLESPACE data01,
partition emp_p2 VALUES LESS THAN (100) TABLESPACE data02,
partition emp_p3 VALUES LESS THAN (150) TABLESPACE data03,
partition emp_p4 VALUES LESS THAN (MAXVALUE) tablespace data04);

Each IOT partitions generates 2 partitions: a data partition and an overflow partition. This overflow partition will contain the data associated to the rows beyond the ename column including that very column.

This organization can be visualised with the following dictionary views:
DBA_TABLES :
TABLE_NAME IOT_NAME IOT_TYPE PARTITIONED -------------------- ---------- ------------ ----------- EMP_IOT IOT YES SYS_IOT_OVER_12770 EMP_IOT IOT_OVERFLOW YES DBA_PART_TABLES :
TABLE_NAME PARTITIONED PARTITION_COUNT DEF_TABLESPACE_NAME ------------------ ----------- --------------- ---------------------------- SYS_IOT_OVER_12770 RANGE 4 USERS DBA_INDEXES :
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES PARTITIONED ------------------ ----------- ----------- --------- ----------- SYS_IOT_TOP_12770 IOT - TOP EMP_IOT UNIQUE YES DBA_IND_PARTITIONS :
INDEX_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME ----------------- -------------- ---------- --------------- SYS_IOT_TOP_12770 EMP_P1 50 DATA01 SYS_IOT_TOP_12770 EMP_P2 100 DATA02 SYS_IOT_TOP_12770 EMP_P3 150 DATA03 SYS_IOT_TOP_12770 EMP_P4 MAXVALUE DATA04 DBA_TAB_PARTITIONS :
TABLE_NAME PARTITION_POSITION PARTITION_NAME TABLESPACE_NAME -------------------- ------------------ --------------- --------------- SYS_IOT_OVER_12770 1 EMP_P1 USERS SYS_IOT_OVER_12770 2 EMP_P2 USERS SYS_IOT_OVER_12770 3 EMP_P3 USERS SYS_IOT_OVER_12770 4 EMP_P4 USERS
In the DBA_TAB_PARTITIONS the data segments tied to the overflow are stored in the user who created the EMP_IOT table's default tablespace. That is because there is no tablespace associated to the overflow segment and the table has no default tablespace specified. When there is a default tablespace for the table then the overflow segments are created in that tablespace.

Additional indexes may be specified on other columns of the IOT:
local prefixed, local non prefixed, local prefixed or global prefixed.