Re: Declarative partitioning

The row (100, 100) landed in t1_mc_p1 which has partition bounds as (1, 200) and (100, 300) which should not accept a row with b = 100. It looks like the binary search got confused with the reversed order of ranges (should that be allowed?)

Symantec of multiple columns for ranges (may be list as well) looks confusing. The current scheme doesn't allow overlapping range for one of the partitioning keys even if the combined range is non-overlapping.
create table t1_multi_col (a int, b int) partition by range (a, b);
create table t1_mc_p1 partition of t1_multi_col for values start (1, 100) end (100, 200);
create table t1_mc_p2 partition of t1_multi_col for values start (1, 200) end (100, 300);
ERROR:  new partition's range overlaps with that of partition "t1_mc_p1" of "t1_multi_col"
HINT:  Please specify a range that does not overlap with any existing partition's range.
create table t1_mc_p2 partition of t1_multi_col for values start (1, 300) end (100, 400);
ERROR:  new partition's range overlaps with that of partition "t1_mc_p1" of "t1_multi_col"
HINT:  Please specify a range that does not overlap with any existing partition's range.

That should be better realised using subpartitioning on b. The question is, if one column's value is enough to identify partition (since they can not contain overlapping values for that column), why do we need mutliple columns/expressions as partition keys? IIUC, all the other column does is to disallow certain range of values for that column, which can better be done by a CHECK constraint. It looks like Oracle looks at combined range and not just one column.