> > Consider lists ('e', 'i', 'f'), ('h', 'd','m') and ('l', 'b', 'a') for a > list partitioned tables. I am suggesting that we arrange them as > ('a','b','l'), ('d', 'h', 'm') and ('e', 'f', 'i'). If the given row (either > for comparison or for inserting) has value 'c', we will search for it in > ('a','b','l') but will be eliminate other two partitions since the second's > partition's lowest value is higher than 'c' and lowest values of rest of the > partitions are higher than that of the second partition. Without this order > among the partitions, we will have to compare lowest values of all the > partitions.
I would think that for that case what we'd want to do is create two lists. One looks like this:
[ 'a', 'b', 'd', 'e', f', 'h', 'i', 'l', 'm' ]
The other looks like this:
[3, 3, 2, 1, 1, 2, 1, 1, 3, 2]
small correction; there's an extra 1 here. Every partition in the example has only three values.
Given a particular value, bsearch the first list. If the value is not found, it's not in any partition. If it is found, then go look up the same array index in the second list; that's the containing partition.
+1, if we could do it. It will need a change in the way Amit's patch stores partitioning scheme in PartitionDesc.
This way specifications {('e', 'i', 'f'), ('h', 'd','m') and ('l', 'b', 'a')} and {('h', 'd','m') , ('e', 'i', 'f'), and ('l', 'b', 'a')} which are essentially same, are represented in different ways. It makes matching partitions for partition-wise join a bit tedius. We have to make sure that the first array matches for both the joining relations and then make sure that all the values belonging to the same partition for one table also belong to the same partition in the other table. Some more complex logic for matching subsets of lists for partition-wise join.
At least for straight forward partitioned table matching it helps to have both these array look same independent of the user specification. From that point of view, the partition be ordered by their lowest or highest list values and the second array is the index in the ordered set. For both the specifications above, the list will look like