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
