The example in Listing 24.5 of the familiar EMP table has a partition key on DEPTNO, putting DEPTNO values greater than 30 in partition P40. Note the naming conventions. Partitions must have a name but, like the tablespace names, they do not have to follow any particular pattern. The names here give some meaning to what data is actually being stored in the partition. This will be helpful for end users and/or developers accessing data by partition only.

Listing 24.5. Partition table EMP example.

create table EMP (

 EMPNO               NUMBER(4) NOT NULL,

 ENAME               VARCHAR2(10),

 JOB                     VARCHAR2(9),

 MGR                   NUMBER(4),

 HIREDATE         DATE,

 SAL                     NUMBER(7,2),

 COMM                NUMBER(7,2),

 DEPTNO            NUMBER(2) NOT NULL)

partition by range(DEPTNO)

(partition P10 values less than(11) tablespace TS_EMP_10,

 partition P20 values less than(21) tablespace TS_EMP_20,

 partition P30 values less than(31) tablespace TS_EMP_30,

 partition P40 values less than (MAXVALUE) tablespace TS_EMP40);

The following code (query and DML examples) demonstrates how individual partitions can easily be accessed.

select ENAME,SAL from EMP partition (P30);

update EMP partition (P20)

    set SAL = SAL * 10;

This next example, Listing 24.6, utilizes a multicolumn partition key. The value here is that once the data in a certain partition is no longer needed, the whole partition can simply be dropped.

Listing 24.6. Multicolumn partition key example.

create table  ORDERS  (

 ORDID               NUMBER (4) NOT NULL,

 ORDERDATE           DATE,

 COMMPLAN            VARCHAR2 (1),

 CUSTID              NUMBER (6) NOT NULL,

 SHIPDATE_MM         NUMBER (2) NOT NULL,

 SHIPDATE_YY         NUMBER (2) NOT NULL,

 TOTAL               NUMBER (8,2)

partition by range (SHIPDATE_YY,SHIPDATE_MM)

partition Q197 values less than(97,04) tablespace TS_Q197,

partition Q297 values less than(97,07) tablespace TS_Q297,

.

.

.

Partitions are easy to administrate. Partitions can be moved between tablespaces, they can be divided into additional partitions, they can be truncated, added, or dropped, and they can be individually queried, updated, inserted, and deleted from. Listing 24.7 displays some new SQL syntax that perform’s these basic administrative functions.

Listing 24.7. Partitioning SQL administrative examples.

ALTER TABLE ADD PARTITION

ALTER TABLE DROP PARTITION

ALTER TABLE MOVE PARTITION

ALTER TABLE SPLIT PARTITION

ALTER TABLE TRUNCATE PARITITION

ALTER TABLE EXCHANGE PARITITION

Indexes don’t necessarily have to be partitioned. DBAs and developers have a range of options based on their availability, performance, and/or management needs.

Indexes can be equi-partitioned; that is, they can be partitioned with the same number of partitions as the underlying table. These indexes are well suited for primary key/foreign key indexes. There are four other types of indexes: prefixed, nonprefixed, local, and global.

Listing 24.8 would create an equi-partitioned index for the EMP table created previously.

Listing 24.8. Equi-partitioned index.

create index IDX_EMP on EMP (DEPTNO)

local

partition by range(DEPTNO)

(partition Pi10 values less than(11) tablespace TS_INX_EMP_10,

 partition Pi20 values less than(21) tablespace TS_INX_EMP_20,

 partition Pi30 values less than(31) tablespace TS_INX_EMP_30,

partition Pi40 values less than (MAXVALUE) tablespace TS_INX_EMP_40); 

Prefixed indexes are those where the indexed columns are the exact same key as the table partition key. Nonprefixed indexes are not based on the table partition key. Local indexes are equi-partitioned indexes, that is, they have the same number of partitions and key divisions as the table. Global indexes are not equi-partitioned; they can have a different number of partitions from that of the table and contain different key data.

Listing 24.9 is an example of a local prefixed index from the EMP table. Notice the index column is the same as that of the EMP partition key (prefixed) and there are the same number of partitions (local).

Listing 24.9. Local prefixed partitioned index.

create index IDX_EMP on EMP (DEPTNO)

local

partition by range(DEPTNO)

(partition Pi10 values less than(11) tablespace TS_INX_EMP_10,

 partition Pi20 values less than(21) tablespace TS_INX_EMP_20,

 partition Pi30 values less than(31) tablespace TS_INX_EMP_30,

partition Pi40 values less than (MAXVALUE) tablespace

TS_INX_EMP_40); 

Listing 24.10 is an example of a nonprefixed local index. Notice the index column and partition key are different from the previous EMP table.

Listing 24.10. Local nonprefixed partitioned index.

create index IDX2_EMP on EMP (EMPNO)

local

partition by range(EMPNO)

(partition P1 values less than(11) tablespace TS2_INX_EMP_10,

 partition P2 values less than(MAXVALUE) tablespace TS2_INX_EMP_20); 

The flowchart, shown in Figure 24.4, should help you decide which partition index combination is best for particular application needs.

Figure 24.4.  Partitioning Index Decision Tree.