Oracle Partitioning


The Concepts of Partitioning

The concept of partitions is that of the splitting of very large tables and indexes into separate sections. Partitioning will speed up database access on individual partitions and allow parallel execution on multiple partitions, ie. simultaneous access to multiple data sets. Backup and recovery is also aided by partitioning allowing backup and recovery of separate partitions. Management and defragmentation of individual partitions is easier than large tables. Individual partitions can be taken offline for maintenenance. Very large databases (VLDBs) with high concurrent activity rates can have significant performance increases using partitioning. It should be noted that small-scale partitioning will see little or no increase in performance in using partitioning.

Partitioned Views

Oracle7 allowed the use of partitioned views. Parititioned views allowed the overlaying of multiple tables of the identical structure where the chaning of data into the underlying tables was determined by the use of column constraints. Oracle8 partitioned tables are much more effective and more efficient than Oracle7 partitioned views.

Object Partitioning

Object partitioning implies the partitioning of both tables and indexes. Note that clusters and indexes associated with clusters cannot be partitioned. Partitions can be based on multiple datafiles in multiple tablespaces and thus on separate hard drives. An object can have upto 64000 partitions. There is new SQL syntax to take advantage of potential parallelism of partitioning.

The Benefits of Partitioned Tables

Equipartitioned and Non-Equipartitioned Objects

Equipartitioned objects have identical logical partitioning attributes. In simple terms an equipartitioned object is an object which is divided into partitions based on equally divided index values. For instance, dividing data into four partitions by dates containing the four quarters in a year is an equipartitioned object, ie. it is logically divided equally. A non-equipartitioned object is not divided equally. For instance, a non-equipartitioned object could have table partitions divided into quarters but be indexed based on amounts of data within each quarter and thus not be divided consitently with the tables. Equipartitioned objects are much more easily manageable than non-equipartitioned objects. An local equipartitioned index is partitioned on the same columns as that of the underlying partitioned table, it has the same number of partitions or subpartitions and has the same partition bounds of the underlying table.

Table Partitioning

CREATE TABLE transactionsTable
(
	type			BOOLEAN		NOT NULL, --set to (C)ustomer or (S)upplier
	customer_supplier_id	INTEGER		NOT NULL,
	transaction_id		INTEGER		NOT NULL,
	description		VARCHAR2(32)	DEFAULT NULL,
	amount			NUMBER(10,2)	DEFAULT 0,
	transaction_date	DATE		DEFAULT NULL,
	
) PCTFREE 20 PCTUSED 50 STORAGE (INITIAL 1M NEXT 1M)
	PARTITION BY RANGE (transaction_date)
	(
		PARTITION partitionQ1 VALUES LESS THAN (TO_DATE('04/01/2000','MM/DD/YYYY'))
			PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ1,
		PARTITION partitionQ2 VALUES LESS THAN (TO_DATE('07/01/2000','MM/DD/YYYY'))
			PCTFREE 10 PCTUSD 60 TABLESPACE tablespaceQ2,
		PARTITION partitionQ3 VALUES LESS THAN (TO_DATE('10/01/2000','MM/DD/YYYY'))
			PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ3 STORAGE (INITIAL 2M NEXT 2M),
		PARTITION partitionQ4 VALUES LESS THAN (TO_DATE('01/01/2001','MM/DD/YYYY'))
			PCTFREE 20 PCTUSED 50 TABLESPACE tablespaceQ3 STORAGE (INITIAL 5M NEXT 5M),
		PARTITION partitionNextYear VALUES LESS THAN (MAXVALUE)
			PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceNextYear
	)
);

Partition storage attributes are inherited from the partition in general if not specified.

The Partition Key

In the example above the partition key is defined in the PARTITION BY RANGE (transaction_date) clause, ie. the transaction_date column. Partition keys cannot contain LEVEL, ROWID or MLSLABEL pseudo-columns or any column of ROWID datatype.

Note the partition bounds in the example shown above. All partitions contain what is called a non-inclusive upper bound (VALUES LESS THAN). In other values values are always included into a partition if their index value is less than the current partition range limit. Parititions are related to each other in terms of their VALUES LESS THAN clause based on their sequential order within the CREATE TABLE statement. Note the MAXVALUE clause in the above example which allows for the storage of values after the fourth quarter of the year 2000.

Multiple Partition Keys

Note the addition of a second column to the PARTITION BY RANGE clause in the example below. The second column is used to subdivide partitions between customers and suppliers, ie. sales and purchase transactions respectively.

PARTITION BY RANGE (transaction_date, type)
(
	PARTITION partitionQ1 VALUES LESS THAN (TO_DATE('04/01/2000','MM/DD/YYYY'), 'S')
		PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ1,
	PARTITION partitionQ1 VALUES LESS THAN (TO_DATE('04/01/2000','MM/DD/YYYY'), 'C')
		PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ1,

	PARTITION partitionQ2 VALUES LESS THAN (TO_DATE('07/01/2000','MM/DD/YYYY'), 'S')
		PCTFREE 10 PCTUSD 60 TABLESPACE tablespaceQ2,
	PARTITION partitionQ2 VALUES LESS THAN (TO_DATE('07/01/2000','MM/DD/YYYY'), 'C')
		PCTFREE 10 PCTUSD 60 TABLESPACE tablespaceQ2,

	PARTITION partitionQ3 VALUES LESS THAN (TO_DATE('10/01/2000','MM/DD/YYYY'), 'S')
		PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ3 STORAGE (INITIAL 2M NEXT 2M),
	PARTITION partitionQ3 VALUES LESS THAN (TO_DATE('10/01/2000','MM/DD/YYYY'), 'C')
		PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ3 STORAGE (INITIAL 2M NEXT 2M),

	PARTITION partitionQ4 VALUES LESS THAN (TO_DATE('01/01/2001','MM/DD/YYYY'), 'S')
		PCTFREE 20 PCTUSED 50 TABLESPACE tablespaceQ4 STORAGE (INITIAL 5M NEXT 5M),
	PARTITION partitionQ4 VALUES LESS THAN (TO_DATE('01/01/2001','MM/DD/YYYY'), 'C')
		PCTFREE 20 PCTUSED 50 TABLESPACE tablespaceQ4 STORAGE (INITIAL 5M NEXT 5M),

	PARTITION partitionNextYear VALUES LESS THAN (MAXVALUE, 'S')
		PCTFREE 10 PCTUSED 60 TABLESPACE quarterFourNextYear,
	PARTITION partitionNextYear VALUES LESS THAN (MAXVALUE, 'C')
		PCTFREE 10 PCTUSED 60 TABLESPACE quarterFourNextYear
)

Table Partitioning Restrictions

Partitioning Indexes

Oracle has four types of indexes.

  1. Non-partitioned.
  2. Global prefixed.
  3. Local prefixed.
  4. Local non-prefixed.

An index is prefixed if the left-most column of the index is the same as the left-most column of the partitioned table. Unique (a single column matching index column to table partition index) prefixed indexes allow the database to access a single partition. This behaviour is much the same as the matching of an SQl DML statement WHERE clause with an index. The closer the match of index to that of partition key the more efficient the search. Thus the WHERE clause can match the partitioned index and the optimiser will cause a single partition to be searched if the WHERE clause values are unique. Note that index cluster and bitmap indexes cannot be partitioned.

Global Indexes

Index partitions which are not identical to table partitions they represent are effectively global indexes. Thus a global index is a non-equipartitioned index with a partitioned table. All global indexes are prefixed. Rebuild global indexes with the ALTER INDEX REBUILD PARTITION statement.

CREATE INDEX transactionsIndex ON transactionsTable (transaction_date) GLOBAL
	PARTITION BY RANGE (transaction_date)
	(
		PARTITION indexPartitionQ1 VALUES LESS THAN (TO_DATE('04/01/2000','MM/DD/YYYY'))
			TABLESPACE indexTablespaceQ1 STORAGE (INITIAL 10K, NEXT 10K),
		PARTITION indexPartitionQ2 VALUES LESS THAN (TO_DATE('07/01/2000','MM/DD/YYYY'))
			TABLESPACE indexTablespaceQ2 STORAGE (INITIAL 10K, NEXT 10K),
		PARTITION indexPartitionQ3 VALUES LESS THAN (TO_DATE('10/01/2000','MM/DD/YYYY'))
			TABLESPACE indexTablespaceQ3 STORAGE (INITIAL 500K NEXT 500K),
		PARTITION indexPartitionQ4 VALUES LESS THAN (TO_DATE('01/01/2001','MM/DD/YYYY'))
			TABLESPACE indexTablespaceQ3 STORAGE (INITIAL 100K NEXT 100K),
		PARTITION indexPartitionNextYear VALUES LESS THAN (MAXVALUE)
			TABLESPACE indexTablespaceNextYear STORAGE (INITIAL 10K, NEXT 10K)
	);

Local Indexes

Local prefixed indexes are generally better than global indexes since they require less maintenance and give better performance. A local index automatically inherits both the same number of partitions as the referenced partition table and the partition names. A local non-prefixed index can be created for columns which are not partition key members. Invalidated local indexes ca be rebuilt using the ALTER TABLE MODIFY PARTITION REBUILD UNUSABLE LOCAL INDEXES statement. As shown below the local partition index inherits table partition attributes such that no specific definition for the local index is required.

CREATE INDEX transactionLocalIndex1 ON transactionTable (transaction_date, type) LOCAL;

Below is a local prefixed index.

CREATE INDEX transactionsLocalIndex2 ON transactionTable (transaction_date) LOCAL
(
	PARTITION indexSupplierTransactionsQ1 TABLESPACE indexTablespaceQ1,
	PARTITION indexCustomerTransactionsQ1 TABLESPACE indexTablespaceQ1,

	PARTITION indexSupplierTransactionsQ2 TABLESPACE indexTablespaceQ2,
	PARTITION indexCustomerTransactionsQ2 TABLESPACE indexTablespaceQ2,

	PARTITION indexSupplierTransactionsQ3 TABLESPACE indexTablespaceQ3,
	PARTITION indexCustomerTransactionsQ3 TABLESPACE indexTablespaceQ3,

	PARTITION indexSupplierTransactionsQ4 TABLESPACE indexTablespaceQ4,
	PARTITION indexCustomerTransactionsQ4 TABLESPACE indexTablespaceQ4,

	PARTITION indexSupplierTransactionsNextYear TABLESPACE quarterFourNextYear,
	PARTITION indexCustomerTransactionsNextYear TABLESPACE quarterFourNextYear
);

Below is an example of an local non-prefixed index.

CREATE INDEX transactionsLocalIndex2 ON transactionTable (type) LOCAL
(
	PARTITION indexSupplierTransactionsQ1 TABLESPACE indexTablespaceQ1,
	PARTITION indexCustomerTransactionsQ1 TABLESPACE indexTablespaceQ1,

	PARTITION indexSupplierTransactionsQ2 TABLESPACE indexTablespaceQ2,
	PARTITION indexCustomerTransactionsQ2 TABLESPACE indexTablespaceQ2,

	PARTITION indexSupplierTransactionsQ3 TABLESPACE indexTablespaceQ3,
	PARTITION indexCustomerTransactionsQ3 TABLESPACE indexTablespaceQ3,

	PARTITION indexSupplierTransactionsQ4 TABLESPACE indexTablespaceQ4,
	PARTITION indexCustomerTransactionsQ4 TABLESPACE indexTablespaceQ4,

	PARTITION indexSupplierTransactionsNextYear TABLESPACE quarterFourNextYear,
	PARTITION indexCustomerTransactionsNextYear TABLESPACE quarterFourNextYear
);

Partitioning Methods

Tables can be partitioned as range partitions, hash partitions or as composite partitions.

Partitioning by Range

Range partitioning tends to distribute data evenly across a range of column values.

CREATE TABLE transactionsTable
(
	type			BOOLEAN		NOT NULL, --set to (C)ustomer or (S)upplier
	customer_supplier_id	INTEGER		NOT NULL,
	transaction_id		INTEGER		NOT NULL,
	description		VARCHAR2(32)	DEFAULT NULL,
	amount			NUMBER(10,2)	DEFAULT 0,
	transaction_date	DATE		DEFAULT NULL,
	
) PCTFREE 20 PCTUSED 50 STORAGE (INITIAL 1M NEXT 1M)
	PARTITION BY RANGE (transaction_date)
	(
		PARTITION partitionQ1 VALUES LESS THAN (TO_DATE('04/01/2000','MM/DD/YYYY'))
			PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ1,
		PARTITION partitionQ2 VALUES LESS THAN (TO_DATE('07/01/2000','MM/DD/YYYY'))
			PCTFREE 10 PCTUSD 60 TABLESPACE tablespaceQ2,
		PARTITION partitionQ3 VALUES LESS THAN (TO_DATE('10/01/2000','MM/DD/YYYY'))
			PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceQ3 STORAGE (INITIAL 2M NEXT 2M),
		PARTITION partitionQ4 VALUES LESS THAN (TO_DATE('01/01/2001','MM/DD/YYYY'))
			PCTFREE 20 PCTUSED 50 TABLESPACE tablespaceQ3 STORAGE (INITIAL 5M NEXT 5M),
		PARTITION partitionNextYear VALUES LESS THAN (MAXVALUE)
			PCTFREE 10 PCTUSED 60 TABLESPACE tablespaceNextYear
	)
);

Hash Partitioning

Hash partitioing allows for partitioning for performance as opposed to even distribution. Rows are mapped into partitions based on a hash value of the partitioning key. Note that a hashing algorithm is much more efficient when the hash calculation is based on an integer rather than a string value.

CREATE TABLE customers
(
	 id INTEGER NOT NULL
	,name VARCHAR2(32)
)
PARTITION BY HASH (id) PARTITIONS 4 STORE IN (customer1, customer2, customer3, customer4);

Composite Partitioning

Composite partitioning partitions data by range and within each of those range partitions, subpartitions are created using the hash method. Range partitioning eases manageability and hash partitioning takes advantage of paralellism.

CREATE TABLE customers
(
	 id INTEGER NOT NULL
	,name VARCHAR2(32)
	,state CHAR(2)
	,balance NUMBER(12,2)
)
PARTITION BY RANGE (balance) SUBPARTITION BY HASH (state)
SUBPARTITIONS 4 STORE IN (state1, state2, state3, state4)
(
	PARTITION customerPartition1 VALUES LESS THAN (5000)
	PARTITION customerPartition2 VALUES LESS THAN (10000)
	PARTITION customerPartition3 VALUES LESS THAN (100000)
	PARTITION customerPartition4 VALUES LESS THAN (MAXVALUE)
);

Using Partitions

Partition Direct SQL Access

Specific partitions can be accessed directly from SQL as shown below. Note that the any constraining values in the WHERE clause should match the appropriate partition for records to be returned.

SELECT * FROM transactionsTable PARTITION (partitionQ1) WHERE ...;

Analysing Partitioned Objects

Partitions use cost-based and never rule-based optimisation. Therefore statistics must be collected when using partitions since cost-based optimisation is based on the use of those collected statistics. Ststistics can be collected as shown below.

ANALYZE TABLE transactionsTable COMPUTE/ESTIMATE STATISTICS;
ANALYZE TABLE transactionsTable PARTITION (partitionQ1) ESTIMATE STATISTICS SAMPLE 50 PERCENT;

New Partitioned Objects

A new partition cannot be added to the middle of the partition order of a partition. A new partition can be added only to the high end of a table partition order or a partition can be split. Any changes of this nature invalidate indexes which will have to be rebuilt. Index rebuilds are also required as a result of truncating or dropping a partition. Partitions can also be merged together.

Unusable State Indexes

Indexes on partition tables can become unusable for a number of reasons. Unusable indexes require rebuilding. These causes could be utilities, moving table partitions between tablespaces, truncating, splitting or dropping partitions.

Partition Row Migration

Migration of rows to new partitions based on key value changes can be controlled by application of the ENABLE/DISABLE ROW MOVEMENT clause in the CREATE and ALTER TABLE statements.

Partitioned View to Partitioned Table Conversions

This process can be achieved by creating the partitioned table and exchanging the tables underlying the partition view as shown below.

ALTER TABLE parititionView EXCHANGE PARTITION partitionQ1 WITH tableQ1;
ALTER TABLE parititionView EXCHANGE PARTITION partitionQ2 WITH tableQ2;
ALTER TABLE parititionView EXCHANGE PARTITION partitionQ3 WITH tableQ3;
ALTER TABLE parititionView EXCHANGE PARTITION partitionQ4 WITH tableQ4;
ALTER TABLE parititionView EXCHANGE PARTITION partitionNextYear WITH tableNextYear;