Home > Articles > Data > Oracle

  • Print
  • + Share This

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.

Figure 1

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.

Figure 2

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.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.