Multi-column partitioning

Simple example of table with more than 1 partition column.

create table xp (a int, b int, c int, data varchar2(20))
partition by range (a,b)(
  partition part1 values less than (1,1),
  partition part2 values less than (2,2),
  partition part3 values less than (3,3),
  partition part4 values less than (MAXVALUE,MAXVALUE)

);

Check:

select * from user_part_key_columns;

NAME                           OBJEC COLUMN_NAME     COLUMN_POSITION
------------------------------ ----- --------------- ---------------
XP                             TABLE A                             1
XP                             TABLE B                             2

select partition_name, high_value, partition_position
from user_tab_partitions
where table_name='XP';


PARTITION_NAME                 HIGH_VALUE           PARTITION_POSITION
------------------------------ -------------------- ------------------
PART1                          1, 1                                  1
PART2                          2, 2                                  2
PART3                          3, 3                                  3
PART4                          MAXVALUE, MAXVALUE                    4


Now insert some values:

create or replace procedure popxp
(imax in int, jmax in int)
is
begin
for i in 0..imax loop
  for j in 0..jmax loop
    insert into xp values (i, j, null, null);
  end loop;
end loop;
end;

/

Run it:

exec popxp(3,3);

And look at the values by partition:
select * from xp partition (part1) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         0          0
         0          1
         0          2
         0          3
         1          0


select * from xp partition (part2) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         1          1
         1          2
         1          3
         2          0
         2          1

select * from xp partition (part3) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         2          2
         2          3
         3          0
         3          1
         3          2

select * from xp partition (part4) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         3          3

So in determining the partition for a row, the value of the first column over-rides that of the 2nd column.   For e.g. (0,3) lies in PART1 because a=0 satisfies the condition a<1 for PART1.  Although b=3 violates the condition b<1, this fact is irrelevant.  

The 2nd column is only considered when the value of the 1st column lies on a partition boundary.  For e.g., (2,0), (2,1), (2,2) and (2,3) all lie on partition boundaries for column "a" of PART2.  For (2,0) and (2,1) the "b" values are the deciding factor in placing these rows in PART2.

For (2,2) "b" is on the boundary for PART2, so "a" again determines the partition for the row.  For (2,3) "b" is now a boundary value for PART3, and since a=2 satisfies the condition for PART3, the row goes there (regardless of "b").


In summary,

Row		PART1	->	PART2	->	PART3	->	PART4
(2,0)		a is too big, so go to next partition.		a is boundary.  So consider b. b=0 satisfies condition for PART2, so put the row there.		-		-
(2,1)		a is too big, so go to next partition.		a is boundary.  So consider b. b=1 satisfies condition for PART2, so put the row here.		-		-
(2,2)		a is too big, so go to next partition.		Both a and b lie on boundaries.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.		a=2 satisfies condition for PART3 so put the row here.		-
(2,3)		a is too big, so go to next partition.		b is too big for this partition.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.		a=2 satisfies condition for PART3 so put the row here.		-