Retrofitting Partitioning into an Existing Application: 2. What kinds of partitioning can you do?

This post is part of a series about the partitioning of database objects.

• Introduction
• What kind of partitioning can you do?
• Scripting and Archiving
• Examples from Real Life
1. General Ledger reporting: Typical example of partitioning for data warehouse-style queries
2. Payroll: Avoiding the need for read-consistency in a typical transaction processing system.
3. Workflow: Separate active and inactive rows, and partial indexing.
• Conclusion

1-Dimensional Partitioning

Oracle supports three forms of partitioning:

• Range: a non-inclusive upper limit is defined for each partition.  Any row where the partition key value is higher than this limit is placed in a subsequent partition.  Implicitly the minimum value is the upper limit of the preceding partition.
• List: specific values are placed in specific partitions.
• Hash: the value of the partitioning key is passed to a hash function.  The output of the hash function determines the partition.

Interval partitioning is a form of range partitioning where Oracle calculates the partition boundaries mathematically, so you don't have to.   Therefore, it only works with numeric, date and timestamp fields.

Partitioning Type	DDL	USER_TAB_PARTITIONS
Range	CREATE TABLE t_r (a NUMBER , b NUMBER ,CONSTRAINT t_r_pk PRIMARY KEY(a) ) PARTITION BY RANGE (b) (PARTITION VALUES LESS THAN (10) ,PARTITION VALUES LESS THAN (20) ,PARTITION VALUES LESS THAN (MAXVALUE));	Table Part Partition High       Num Name   Pos Name      Value     Rows ----- ---- --------- -------- ----- T_R      1 SYS_P539        10  1000          2 SYS_P540        20  1000
List	CREATE TABLE t_l (a NUMBER, b NUMBER ,CONSTRAINT t_l_pk PRIMARY KEY(a) ) PARTITION BY LIST (b) (PARTITION VALUES (1,2,3) ,PARTITION VALUES (4,5,6) ,PARTITION VALUES (DEFAULT));	Table Part Partition High       Num Name   Pos Name      Value     Rows ----- ---- --------- -------- ----- T_L      1 SYS_P542  1, 2, 3    300          2 SYS_P543  4, 5, 6    300          3 SYS_P544  DEFAULT   9400
Hash	CREATE TABLE t_h (a NUMBER, b NUMBER ,CONSTRAINT t_h_pk PRIMARY KEY(a) ) PARTITION BY HASH (b) PARTITIONS 4;	Table Part Partition High       Num Name   Pos Name      Value     Rows ----- ---- --------- -------- ----- T_H      1 SYS_P545            2000          2 SYS_P546            2900          3 SYS_P547            2400          4 SYS_P548            2700
Interval	CREATE TABLE t_i (a NUMBER, b NUMBER ,CONSTRAINT t_i_pk PRIMARY KEY(a) ) PARTITION BY RANGE (b) INTERVAL (10) (PARTITION VALUES LESS THAN (10));	Table Part Partition High       Num Name   Pos Name      Value     Rows ----- ---- --------- -------- ----- T_I      1 SYS_P549        10  1000          2 SYS_P550        20  1000          3 SYS_P551        30  1000          4 SYS_P552        40  1000          5 SYS_P553        50  1000          6 SYS_P554        60  1000          7 SYS_P555        70  1000          8 SYS_P556        80  1000          9 SYS_P557        90  1000         10 SYS_P558       100  1000

2-Dimensional (Composite) Partitioning

Oracle can partition in independently on two columns (or groups of columns).  This is called composite partitioning. It is easy to think of this as partitioning in two dimensions.  Again, this diagram is taken from Oracle`s documentation.

Composite partitioning can mix any form of partitioning with any form of partitioning, except that you cannot interval subpartition.

Partition DDL		Sub-partitioning type
		Range	List	Hash	Interval
Partitioning Type	Range	CREATE TABLE t_rr (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_rr_pk PRIMARY KEY(a)) PARTITION BY RANGE (b) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE (SUBPARTITION s_10 VALUES LESS THAN (10) ,SUBPARTITION s_20 VALUES LESS THAN (20) ,SUBPARTITION s_mx VALUES LESS THAN                                                          (MAXVALUE)) (PARTITION VALUES LESS THAN (10) ,PARTITION VALUES LESS THAN (20) ,PARTITION VALUES LESS THAN (MAXVALUE));	CREATE TABLE t_rl (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_rl_pk PRIMARY KEY(a)) PARTITION BY RANGE (b) SUBPARTITION BY LIST (c) SUBPARTITION TEMPLATE (SUBPARTITION s_1 VALUES (1,2,3) ,SUBPARTITION s_2 VALUES (4,5,6) ,SUBPARTITION s_mx VALUES                                             (DEFAULT)) (PARTITION VALUES LESS THAN (10) ,PARTITION VALUES LESS THAN (20) ,PARTITION VALUES LESS THAN                                          (MAXVALUE));	CREATE TABLE t_rh (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_rh_pk PRIMARY KEY(a)) PARTITION BY RANGE (b) SUBPARTITION BY HASH (c) SUBPARTITIONS 4 (PARTITION VALUES LESS THAN (10) ,PARTITION VALUES LESS THAN (20) ,PARTITION VALUES LESS THAN                                           (MAXVALUE) );	ORA-14179: An unsupported partitioning method was specified in this context.
	List	CREATE TABLE t_lr (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_lr_pk PRIMARY KEY(a)) PARTITION BY LIST (b) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE (SUBPARTITION s_10 VALUES LESS THAN (10) ,SUBPARTITION s_20 VALUES LESS THAN (20) ,SUBPARTITION s_mx VALUES LESS THAN                                                         (MAXVALUE)) (PARTITION VALUES (1,2,3) ,PARTITION VALUES (4,5,6) ,PARTITION VALUES (DEFAULT));	CREATE TABLE t_ll (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_ll_pk PRIMARY KEY(a)) PARTITION BY LIST (b) SUBPARTITION BY LIST (c) SUBPARTITION TEMPLATE (SUBPARTITION s_1 VALUES (1,2,3) ,SUBPARTITION s_2 VALUES (4,5,6) ,SUBPARTITION s_mx VALUES                                         (DEFAULT)) (PARTITION VALUES (1,2,3) ,PARTITION VALUES (4,5,6) ,PARTITION VALUES (DEFAULT));	CREATE TABLE t_lh (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_lh_pk PRIMARY KEY(a)) PARTITION BY LIST (b) SUBPARTITION BY HASH (c) SUBPARTITIONS 4 (PARTITION VALUES (1,2,3) ,PARTITION VALUES (4,5,6) ,PARTITION VALUES (DEFAULT));
	Hash	CREATE TABLE t_hr (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_hr_pk PRIMARY KEY(a)) PARTITION BY HASH (b) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE (SUBPARTITION s_10 VALUES LESS THAN (10) ,SUBPARTITION s_20 VALUES LESS THAN (20) ,SUBPARTITION s_mx VALUES LESS THAN                                                         (MAXVALUE)) PARTITIONS 4;	CREATE TABLE t_hl (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_hl_pk PRIMARY KEY(a)) PARTITION BY HASH (b) SUBPARTITION BY LIST (c) SUBPARTITION TEMPLATE (SUBPARTITION s_1 VALUES (1,2,3) ,SUBPARTITION s_2 VALUES (4,5,6) ,SUBPARTITION s_mx VALUES                                         (DEFAULT)) PARTITIONS 4;	CREATE TABLE t_hh (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_hh_pk PRIMARY KEY(a)) PARTITION BY HASH (b) SUBPARTITION BY HASH (c) SUBPARTITIONS 4 PARTITIONS 4;
	Interval	CREATE TABLE t_ir (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_ir_pk PRIMARY KEY(a)) PARTITION BY RANGE (b) INTERVAL (10) SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE (SUBPARTITION s_10 VALUES LESS THAN (10) ,SUBPARTITION s_20 VALUES LESS THAN (20) ,SUBPARTITION s_mx VALUES LESS THAN                                                          (MAXVALUE)) (PARTITION VALUES LESS THAN (10));	CREATE TABLE t_il (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_il_pk PRIMARY KEY(a)) PARTITION BY RANGE (b) INTERVAL (10) SUBPARTITION BY LIST (c) SUBPARTITION TEMPLATE (SUBPARTITION s_1 VALUES (1,2,3) ,SUBPARTITION s_2 VALUES (4,5,6) ,SUBPARTITION s_mx VALUES                                           (DEFAULT)) (PARTITION VALUES LESS THAN (10));	CREATE TABLE t_ih (a NUMBER, b NUMBER, c NUMBER ,CONSTRAINT t_ih_pk PRIMARY KEY(a)) PARTITION BY RANGE (b) INTERVAL (10) SUBPARTITION BY HASH (c) SUBPARTITIONS 4 (PARTITION VALUES LESS THAN (10));

Sub-partition templates simplify the DDL, otherwise, you have to specify the sub-partitions for each partition.  As you do not specify all the partitions when interval partitioning, you effectively have to use templates to sub-partition interval partitions.  Otherwise, the automatically added partitions will not be sub-partitioned.  

In some cases, involving hash partitioning, the database is sensitive to the order of partition and sub-partitions clauses in the DDL.

Partitions are given system-generated names unless names are specified.  Explicitly specified interval partitions have to be explicitly named.  Subpartition names in subpartition templates are only used when the partition is explicitly named, otherwise, the subpartition has an entirely system-generated name. 

It can be helpful to explicitly specify partition and sub-partition names.  It has no impact on performance, but can help administration, e.g. reporting space usage by partition.  It can also be helpful later partitions and dropped, split or merged archiving or ILM.