Oracle Partitioning Types - VI (Multiple Column Partition)

Multiple Column Partitioning
As the name suggests we can define the partition key using multiple column in the table.
So basically if there are two columns which you would like to use to define partitioning to facilitate related data to be stored in the same partition. There are be more than 2 columns for defining column keys. The idea is if using the first column key oracle is not able to ascertain which partition it has to go into, it used the second key and like-wise. The reason why oracle is not able to define the correct partition could be because the partition bound values are overlapping, between partitions.

SQL> CREATE TABLE DATA_LOAD
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY RANGE (data_id,source_id)
8 ( PARTITION data_01_2009 VALUES LESS THAN (10,10) TABLESPACE DATA_01,
9 PARTITION data_02_2009 VALUES LESS THAN (10,20) TABLESPACE DATA_02,
10 PARTITION data_03_2009 VALUES LESS THAN (20,30) TABLESPACE DATA_03,
11 PARTITION data_04_2009 VALUES LESS THAN (30,40) TABLESPACE DATA_04
12 );
Table created.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD NO DATA_01_2009 0
DATA_LOAD NO DATA_02_2009 0
DATA_LOAD NO DATA_03_2009 0
DATA_LOAD NO DATA_04_2009 0

SQL> insert into DATA_LOAD values (1,1,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (1,15,sysdate-10,11,'KERELA');
1 row created.

SQL> insert into DATA_LOAD values (15,19,sysdate-10,11,'KERELA');
1 row created.

SQL> insert into DATA_LOAD values (25,25,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (29,36,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (10,12,sysdate-10,11,'KERELA');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD NO DATA_01_2009 2
DATA_LOAD NO DATA_02_2009 1
DATA_LOAD NO DATA_03_2009 1
DATA_LOAD NO DATA_04_2009 2

So you can see that the insert statement no. 2, had the second value as 15, but it went into partition 1, as it was table to decide using the first value. But the last insert statement had the first value as 10, which is the partition bound value for 1st and 2nd partition, so the row used the second value i.e. 12, and inserted the row in partition 2.

Part I
Part II
Part III
Part IV
Part V
Part VII