Oracle 11g Improves Partitioning

Bert Scalzo — Tue, 21 Aug 2007 12:21:00 GMT

Oracle 11g has a plethora of great new features. One area that has improved with each of the last four releases has been partitioning, i.e. the method to break larger tables into smaller, more manageable and efficient sub-tables. While it was originally touted as a boon for just the data warehousing world, partitioning nonetheless works well in many situations. With Oracle continuing to improve upon them – partitioning is now a powerful asset in any DBA’s tool belt. So let’s examine some of the new table partitioning techniques introduced with Oracle 11g.

First, Oracle has nicely “rounded out” the composite partitioning alternatives by adding list-hash, list-list, list-range and range-range partitioning. These new methods are pretty straight forward for anyone who has already been using partitioning. However nice these new composite options may be – this was only an incremental or evolutionary change.

Second, Oracle has introduced “Interval Partitioning” – a very useful and powerful option for DBAs whose data is continuously growing over a range. Before 11g, the DBA had to keep ahead of the data growth curve by manually adding partitions for the new range of values. So for example if I was partitioning by week, I had to remember to manually add the next weeks partition before some application tried to insert values within that range. Otherwise you’d get the Oracle error ORA-14400: inserted partition key does not map to any partition. Here’s an example of that happening:

CREATE TABLE TABLE1
(
  C1 INTEGER                        NOT NULL PRIMARY KEY,
  C2 VARCHAR2(1 BYTE)        NOT NULL,
  C3 VARCHAR2(1 BYTE)        NOT NULL,
C4 VARCHAR2(1 BYTE)        NOT NULL
)
PARTITION BY RANGE (C1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200),
PARTITION P3 VALUES LESS THAN (300)
);

insert into table1 values (100,'a','b','c');
insert into table1 values (101,'a','b','c');
insert into table1 values (200,'a','b','c');
insert into table1 values (201,'a','b','c');
insert into table1 values (300,'a','b','c'); ç this statement causes the error

You can sort of think of this like the old “unable to allocate extent” error message for tablespaces where the data file size was not allowed to automatically extend and, much like that extent allocation failure message, ORA-14400 should be a message that never should really occur (assuming the DBA is keeping current with their data growth needs).

So here’s the same table implemented using interval partitioning.

CREATE TABLE TABLE2
(
C1 INTEGER                            NOT NULL PRIMARY KEY,
C2 VARCHAR2(1 BYTE)        NOT NULL,
C3 VARCHAR2(1 BYTE)        NOT NULL,
C4 VARCHAR2(1 BYTE)        NOT NULL
)
PARTITION BY RANGE (C1)
INTERVAL (100)
(
PARTITION P1 VALUES LESS THAN (100)
);

insert into table2 values (100,'a','b','c');
insert into table2 values (101,'a','b','c');
insert into table2 values (200,'a','b','c');
insert into table2 values (201,'a','b','c');
insert into table2 values (300,'a','b','c');

We don’t get any errors with this implementation – even though we did not create but the first partition for values less than 100. In this case, Oracle simply creates the necessary partitions for you on the fly. Here’s what partitions the above two table examples yield:

TABLE_NAME         PARTITION_NAME        HIGH_VALUE
-------------------------- ------------------------------ -------------------
TABLE1                     P1                                      100
TABLE1                      P2                                      200
TABLE1                     P3                                      300
TABLE2                      P1                                      100
TABLE2                     SYS_P41                             200
TABLE2                      SYS_P42                             300
TABLE2                      SYS_P43                             400

Third, Oracle has introduced “Reference Partitioning.” This technique is both novel and a little more complex. You must have a “parent-child relationship” between two tables with active primary and foreign keys between them. There are real benefits to this approach, namely that reference partitioning permits logically equi-partitioning the child table by inheriting the partition key from the parent table without duplicating the key columns.

Here’s an example or reference partitioning:

CREATE TABLE PARENT
(
P1 INTEGER                             NOT NULL PRIMARY KEY,
P2 VARCHAR2(1 BYTE)        NOT NULL
)
PARTITION BY RANGE (P1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200),
PARTITION P3 VALUES LESS THAN (300)
);

CREATE TABLE CHILD
(
C1 INTEGER                             NOT NULL,
C2 VARCHAR2(1 BYTE)             NOT NULL,
C3 VARCHAR2(1 BYTE)             NOT NULL,
constraint child_pk primary key(c1,c2),
constraint child_fk foreign key(c1) references parent(p1)
)
PARTITION BY REFERENCE(child_fk);

And here are the partitions the reference partition example created:

TABLE_NAME        PARTITION_NAME        HIGH_VALUE
-------------------------- ------------------------------ -------------------
CHILD                        P1
CHILD                        P2
CHILD                        P3
PARENT                     P1                                      100
PARENT                     P2                                      200
PARENT                     P3                                      300

Notice how child table high value settings are NULL? Since this is an equi-partitioning method where the child inherits its partitioning directly this from the parent, there is no need to duplicate the meta-data. As pointed out above, this also saves the database from storing the actual data in both places. This could be significant with VLDB’s.

Fourth and finally, Oracle has introduced “Virtual Columns based Partitioning.” I wrote about virtual columns last week – and here’s the example.

CREATE TABLE PROD4 (
XPNUM    CHAR(13)                NOT NULL,
STAT    CHAR(2)      GENERATED ALWAYS AS ((SUBSTR(XPNUM,1,2))),
DEPT    CHAR(4)      GENERATED ALWAYS AS ((SUBSTR(XPNUM,3,4))),
PNUM    CHAR(7)     GENERATED ALWAYS AS ((SUBSTR(XPNUM,7,7))),
CONSTRAINT PROD4_PK PRIMARY KEY (XPNUM)
);

Now with 11g, we can even partition on such virtual columns as shown below:

CREATE TABLE VIRT_PART (
XPNUM   CHAR(13)    NOT NULL,
STAT    CHAR(2)     GENERATED ALWAYS AS ((SUBSTR(XPNUM,1,2))),
DEPT    CHAR(4)     GENERATED ALWAYS AS ((SUBSTR(XPNUM,3,4))),
PNUM    CHAR(7)     GENERATED ALWAYS AS ((SUBSTR(XPNUM,7,7))),
CONSTRAINT VIRT_PART_PK PRIMARY KEY (XPNUM)
)
PARTITION BY HASH (DEPT)
PARTITIONS 4;

Wow – Partitioning has come a long way baby

Bert Scalzo's Blog

Oracle 11g Improves Partitioning