- To Partition or Not to Partition
- Oracle Partitioning Options
- Summary
Oracle Partitioning Options
Partitions must contain fields only from the object and may contain one or more fields. More than one field would create a multicolumn partition key. Partitions can share tablespaces or have one partition per tablespace. Tablespaces still have the same space attributes, backup/recovery, and so on, as nonpartition tablespaces. It's a good idea to use the MAXVALUE on the final partition to eliminate the chance of getting the error message 1440: Inserted partition key is beyond highest legal partition key.
Partition tables cannot contain LONG, LONG RAW, or LARGE OBJECTS (LOB).
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_EMP_40);
This example of the familiar EMP table has a partition key on DEPTNO, putting DEPTNO values greater than 30 in partition P40. Notice the naming conventions. Partitions must have a name, but, like the tablespace names, partition names don't have to follow any particular pattern. The names in this example give some indication of what data is stored in the partition; this is helpful for end users and/or developers accessing data by partition only.
The following query and DML (data manipulation language, for example, UPDATE and DELETE) examples demonstrate how easily individual partitions can be accessed.
select ENAME,SAL from EMP partition (P30);
update EMP partition (P20)
set SAL = SAL * 10;
This next example 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.
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 administer. They can be moved between tablespaces; divided into additional partitions; truncated, added, or dropped; and they can be individually queried, updated, inserted, and deleted from. The following SQL syntax performs these basic administrative functions:
ALTER TABLE ADD PARTITION ALTER TABLE DROP PARTITION ALTER TABLE MOVE PARTITION ALTER TABLE SPLIT PARTITION ALTER TABLE TRUNCATE PARITITION ALTER TABLE EXCHANGE PARITITION
Index Partitioning Options
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 equipartitioned; 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 types of indexes:
-
Prefixed indexes are those in which the indexed columns have the same key as the table partition key.
-
Nonprefixed indexes are not based on the table partition key.
-
Local indexes are equipartitioned indexes; that is, they have the same number of partitions and key divisions as the table.
-
Global indexes are not equipartitioned; they can have a different number of partitions from that of the table and can contain different key data.
The following example creates a local index. Notice that there are no range values, and the number of partitions is the same as defined in the EMP example in the preceding section, using the same partitioning key. Local indexes pick up the partitioning values from the underlying table.
create index IDX_EMP on EMP (DEPTNO) local partition by range(DEPTNO) (partition Pi10 tablespace TS_INX_EMP_10, partition Pi20 tablespace TS_INX_EMP_20, partition Pi30 tablespace TS_INX_EMP_30, partition Pi40 tablespace TS_INX_EMP_40);
Figure 1 shows the relationship between the partitioned index and the partitioned table. The index column and partition key are same as the partition key in the above EMP table.
){kind=link}
Prefixed partition table/index relationship.
Following is an example of a local prefixed index from the EMP table above. Notice that the index column is the same as that of the EMP partition key (prefixed) and there are the same number of partitions (local):
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);
Figure 2 shows the relationship between a nonprefixed partition table and index.
){kind=link}
Nonprefixed partition table/index relationship.
Finally, here's an example of a nonprefixed global index. Notice that the index column and partition key are different from the above EMP table:
create index IDX2_EMP on EMP (EMPNO) global 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);
Notice that the nonprefixed nature, combined with a different partitioning scheme from the underlying table, causes a considerable amount of additional I/O to process the index.