I have a two-column partition key, and my data keeps going in the wrong partition. Why ?

The Oracle (tm) Users' Co-Operative FAQ

I have a two-column partition key, and my data keeps going in the wrong partition. Why ?

Unfortunately, the natural assumption that many people make when specifying multiple columns in their partitioning key, is that they are creating an 'n'-dimensional partitioning of the data, where 'n' is the number of columns in the partitioning key. Consider a simple example where there are 2 columns in the partition key.

SQL> create table DEMO (
  2    pcol1 date,
  3    pcol2 date,
  4    data varchar2(10) )
  5  partition by range (pcol1,pcol2)
  6  (  partition p_PRE_2000 values less than
  7       (to_date('01/01/2000','dd/mm/yyyy'), to_date('01/01/2000','dd/mm/yyyy')),
  8     partition p_2000_JAN_JUN values less than
  9       (to_date('01/07/2000','dd/mm/yyyy'), to_date('01/07/2000','dd/mm/yyyy')),
 10     partition p_2000_JUL_DEC values less than
 11       (to_date('01/01/2001','dd/mm/yyyy'), to_date('01/01/2001','dd/mm/yyyy')),
 12     partition p_2001_JAN_JUN values less than
 13       (to_date('01/07/2001','dd/mm/yyyy'), to_date('01/07/2001','dd/mm/yyyy'))
 14  );
Table created.

Its fairly obvious that the desired result here is for each partition to hold a 6 month window of data based on the columns PCOL1 and PCOL2. But the partition key columns are not dimensions, a better label would be tie-breakers. When data is presented for insertion, the columns in the partition key are evaluated "left to right" in order to determine the correct partition. Thus in the DDL above, the second partition key column (PCOL2) will only be considered when the first column is not sufficient to determine the partition.

So lets see what happens when we add some data.

SQL> alter session set nls_date_format = 'dd/mm/yyyy';
Session altered.
SQL> insert into DEMO values ('01/02/1999','01/01/1999','row1');
SQL> insert into DEMO values ('01/02/2000','01/01/1999','row2');
SQL> insert into DEMO values ('01/02/2001','01/08/1999','row3');
SQL> insert into DEMO values ('01/02/2001','01/08/2000','row4');
SQL> insert into DEMO values ('01/02/1999','01/02/1999','row5');
SQL> insert into DEMO values ('01/02/2000','01/02/2000','row6');
SQL> insert into DEMO values ('01/02/2001','01/08/2001','row7');
SQL> insert into DEMO values ('01/02/2001','01/08/1999','row8');
SQL> insert into DEMO values ('01/08/2000','01/08/2000','row9');
SQL> insert into DEMO values ('01/08/1999','01/02/2001','row10');
SQL> select * from DEMO partition (p_PRE_2000);
PCOL1      PCOL2      DATA
---------- ---------- ----------
01/02/1999 01/01/1999 row1
01/02/1999 01/02/1999 row5
01/08/1999 01/02/2001 row10
SQL> select * from DEMO partition (p_2000_JAN_JUN);
PCOL1      PCOL2      DATA
---------- ---------- ----------
01/02/2000 01/01/1999 row2
01/02/2000 01/02/2000 row6
SQL> select * from DEMO partition (p_2000_JUL_DEC);
PCOL1      PCOL2      DATA
---------- ---------- ----------
01/08/2000 01/08/2000 row9
SQL> select * from DEMO partition (p_2001_JAN_JUN);
PCOL1      PCOL2      DATA
---------- ---------- ----------
01/02/2001 01/08/1999 row3
01/02/2001 01/08/2000 row4
01/02/2001 01/08/2001 row7
01/02/2001 01/08/1999 row8

Only rows 1,5,6 appear to satisfy what the user desired, but in fact, all of the rows have been correctly located, because in all cases, the leading column of the partition key (PCOL1) was enough to determine the correct partition. To emphasise this further, consider

SQL> insert into DEMO values ('01/08/1999','01/02/2005','row11');
1 row created.

It would appear from the original DDL that the year 2005 is out of bounds for PCOL2, but of course, this was not even considered because the value for PCOL1 is quite valid, and sufficient to determine that partiton P_PRE_2000 is the destination for this row.

Further reading: N/A