Named vs Unnamed Partitions

Hi,

IMO, the lengthy discussion about Segment Exclusion and Segment 
Visibility Maps has long turned into a discussion about partitioning in 
general. I'm thankful for all the new insights it has brought me and I 
want to continue sharing my view on things. What's following is highly 
theoretical and has brainstorming characteristics. You've been warned.

There are two very distinct ways to handle partitioning. For now, I'm 
calling them named and unnamed partitioning. Let's have a closer look at 
both options from a users point of view. I'm using Andrew's pseudo DDL 
example from the above mentioned thread:
ALTER TABLE foo  SET read_only='t'  WHERE created_on < '2007-01-01';

Given all tuples were read-writeable before, that implicitly created two 
partitions. Giving them names could look like that:
ALTER TABLE foo  SPLIT INTO old_foos AND new_foos;  AT created_on < '2007-01-01'ALTER PARTITION old_foos          SET
READONLY;
 


Instead of only setting the read-only property, one could also set an 
alternative table space for the partition:
ALTER TABLE foo  SET TABLE SPACE large_but_slow_storage  WHERE created_on < '2007-01-01';

vs:
ALTER PARTITION old_foos  SET TABLE SPACE large_but_slow_storage;


Please also note, that neither variant is limited to range partitioning. 
You can theoretically partition by pretty much anything, for example 
with a WHERE clause like:
..WHERE (id % 5) < 2

The primary difference I see between these two ways to declare 
partitions is, that the former only modifies tuple properties 
(read-only, storage location), while the later also tells the database 
*why* it has to modify them.

That has several different effects. First, newly inserted tuples are 
treated differently. For unnamed partitions, there must be defaults, 
like read-writable and a default table space. With named partitions, you 
define split points, so I guess one expects newly inserted tuples to end 
up in the right partition automatically. Unnamed partitioning could be 
equally automatic when letting a function decide, where to insert the 
new tuple.

Second, repartitioning must be treated differently. With unnamed 
partitioning, the admin must first adjust the defaults (if required) and 
then move the existing tuple properties accordingly. With named 
partitions, the admin only needs to adjust the split point and the 
database system knows what it has to do.

And third, but IMO most importantly: to be able to optimize queries, the 
database system has to know split points, so it can exclude partitions 
or segments from scanning. Obviously, with named partitions, it always 
knows them. Otherwise, you'll have to maintain some information about 
the tuples in your partitions, as Simon does with the min/max tuples. As 
soon as required, it could also maintain additional min/max values, i.e. 
for (id % 5) for the above example.


I hope to have shown the most relevant aspects. To conclude, I'd say 
that named partitioning is closer to manually managed partitioning, as 
already known and often used. While unnamed partitioning is closer to 
automated partitioning, where the DBA does *not need* to have names for 
partitions, which is a pretty new and interesting idea to me.

Regards

Markus