Database table partition is physically save table data in different tablespaces that are located in different hardware disks. Database table partition thus reduced I/O operation when database operates on this table.

Database application developers, on the other hand, will not change their application codes that access partitioned table. Developers use partitioned table just as any other tables because table partition is about physically saving data in different locations but not about logical table design.

1. Types of partitioning

There are three types of partitioning in Oracle. These are range partitioning, hash partitioning, and composite partitioning. Next, using table partitions, we define their meaning.

1.1 Range Partitioning

Range partitioning distributes data on the basis of ranges of column values. These columns are partitioning columns.

Let??s use an example of data mart

The fact table is sales_fact. Because fact table is large in database, we use range partitioning to separate data into different tablespaces.

List 1. Range partitioning.
CREATE TABLE SALES_FACT (
	time_id		DATE NOT NULL,
	customer_id	NUMBER,
	product_id	VARCHAR(16) NOT NULL,
	channel_id	VARCHAR(16) NOT NULL,
	full_price	NUMBER,
	discount_amount	NUMBER,
	commission	NUMBER,
	net_proceeds	NUMBER
)
	STORAGE (INITIAL 200M NEXT 100M PCTINCREASE 0)
	PARTITION BY RANGE (time_id)
	(PARTITION p2005Quart1
		VALUES LESS THAN (TO_DATE('01-APR-2005', 'DD-MON-YYYY'))
		TABLESPACE sales1,
	PARTITION p2005Quart2 
		VALUES LESS THAN (TO_DATE('01-JUL-2005', 'DD-MON-YYYY'))
		TABLESPACE sales2,
	PARTITION p2005Quart3
		VALUES LESS THAN (TO_DATE('01-OCT-2005', 'DD-MON-YYYY'))
		TABLESPACE sales3,
	PARTITION p2005Quart4
		VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY'))
		TABLESPACE sales4
	);

The SALES_FACT table has been partitioned using time_id, so the data in the table will be distributed in 4 different tablespaces.

1.2 Hash Partitioning

It is no always possible to use range partition for a database table, so Oracle has hash partitioning.

List 2. Hash Partitioning.
CREATE TABLE PRODUCT (
	product_id	VARCHAR(16) NOT NULL,
	name		VARCHAR(16),
	type		VARCHAR(16),
	occation	VARCHAR(12),
	expected_display_life	VARCHAR(16),
	season		VARCHAR(8),
	price_range	VARCHAR(16),
	region		VARCHAR(16),
	date_available_from	DATE,
	date_available_to	DATE,
	description
)
	STORAGE (INITIAL 100M NEXT 100M)
	PARTITION BY HASH(product_id)
	(PARTITION p1 TABLESPACE product1, 
	 PARTITION p2 TABLESPACE product2,
	 PARTITION p3 TABLESPACE product3, 
	 PARTITION p4 TABLESPACE product4);

1.3. Composite Partitioning

Table SALES_FACT can use composite partitioning as well. Composite partitioning uses range partitioning first, then it sub-partitioned by the use of hash partitioning.

List 3 shows the composite partitioning of table SALES_FACT.

List 3. Composite Partitioning
CREATE TABLE SALES_FACT (
	time_id		DATE NOT NULL,
	customer_id	NUMBER,
	product_id	VARCHAR(16) NOT NULL,
	channel_id	VARCHAR(16) NOT NULL,
	full_price	NUMBER,
	discount_amount	NUMBER,
	commission	NUMBER,
	net_proceeds	NUMBER
)
	STORAGE (INITIAL 200M NEXT 100M PCTINCREASE 0)
	PARTITION BY RANGE (time_id)
	SUBPARTITION BY HASH(product_id)
	SUBPARTITIONS 4 STORE IN 
		(sales1, sales2, sales3, sales4)
	(PARTITION p2005Quart1
		VALUES LESS THAN (TO_DATE('01-APR-2005', 'DD-MON-YYYY')),
	PARTITION p2005Quart2 
		VALUES LESS THAN (TO_DATE('01-JUL-2005', 'DD-MON-YYYY')),
	PARTITION p2005Quart3
		VALUES LESS THAN (TO_DATE('01-OCT-2005', 'DD-MON-YYYY')),
	PARTITION p2005Quart4
		VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY'))
	);

2. Using Index Partitioning

Oracle supports two types of index partitioning, local index and global index.

A local index is equipartitioned with its related table. The index has the same partitions and partition keys as its related table.

A global index may or may not be partitioned. And if it is partitioned, it is not equipartitioned with related table.

2.1 Local Indexes

Local Indexes can be added by statement using keyword LOCAL

CREATE INDEX sales_idx ON SALES_FACT(time_id) LOCAL;

When you use this statement creating indexes, index partitions are stored in the tablespace as in corresponding tables.

If you want specify tablespace for local index, you can use

CREATE INDEX sales_idx ON SALES_FACT(time_id) LOCAL
	(PARTITION sidx1 TABLESPACE salesidx1,
	 PARTITION sidx2 TABLESPACE salesidx2,
	 PARTITION sidx3 TABLESPACE salesidx3,
	 PARTITION sidx4 TABLESPACE salesidx4
);

2.2 Global Indexes

We can use following to create global indexes. A global index can be partitioned only by range partitioning.

CREATE UNIQUE INDEX product_idx ON PRODUCT (product_id) GLOBAL
	PARTITION BY RANGE (product_id)
	(PARTITION prod_idx1 VALUES LESS THEN (10000) 
		TABLESPACE prodidx1,
	 PARTITION prod_idx2 VALUES LESS THEN (MAXVALUE) 
		TABLESPACE prodidx2
);

Global indexes are used for enforcing uniqueness across all partitions and for improving performance.

The views for partitioning information are:

DBA_PART_TABLES
DBA_TAB_PARTITIONS
DBA_PART_KEY_COLUMNS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_PART_INDEXES
DBA_TAB_SUBPARTITIONS
DBA_SUBPART_KEY_COLUMNS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
USER_INDEXES
USER_ IND_PARTITIONS

In general, traditional Oracle DBAs like to use sqlplus to query these views.

However, if you like to make life easier and do your 8 hr job in 4 hrs, you may use some GUI tools for inspection of partitioning tables and indexes.

I introduce you free software Tlinksoft that can displays these views clearly.

You may download this software at http://www.cppunit.org/download/

The tutorial is available also by clicking http://www.cppunit.org/Tutorial.pdf

In summary, GUI software can show any Oracle views clearly without typing a single sentence, you may sort any columns in the views as well. Best of all, it is free now.

3. Some Useful Commands

Now we list some useful SQL relating to partitioning.

Add a range partition, we use

ALTER TABLE SALES_FACT ADD PARTITION P2005_qtr1
	VALUE LESS THAN (??01-APR-2005??,??DD-MON-YYYY??))
	TABLESPACE sales1;

Add a hash partition, we use

ADD TABLE product ADD PARTITION prod1 
	TABLESPACE product1; 

Move a partition

ALTER TABLE product MOVE PARTITION prod1 TABLESPACE product2;

Drop a partition

ALTER TABLE SALES_FACT DROP PARTITION P2005_qtr1;

Rename a partition

ALTER TABLE SALES_FACT RENAME PARTITION P2005_qtr1 TO myP2005_qtr1;

4. Summary

We have introduced how to partition large tables in Oracle database. We also introduced how to use GUI tools to manage and monitor database partitions.