On Wed, 2007-03-07 at 21:27 -0700, Jim Nasby wrote:
> On Mar 7, 2007, at 3:26 AM, Simon Riggs wrote:
> > If you know that the constraints on each of the tables is distinct,  
> > then
> > building a UNIQUE index on each of the partitions is sufficient to  
> > prove
> > that all rows in the combined partitioned table are distinct also.
> >
> > The hard part there is checking that the partition constraints are
> > distinct. If the partition constraints are added one at a time, you  
> > can
> > use the predicate testing logic to compare the to-be-added partition's
> > constraint against each of the already added constraints. That becomes
> > an O(N) problem.
> >
> > What is really needed is a data structure that allows range partitions
> > to be accessed more efficiently. This could make adding partitions and
> > deciding in which partition a specific value goes an O(logN)  
> > operation.
> Directing data to child tables with triggers pretty much necessitates  
> having some way to codify what partition a particular row belongs in.  
> IE: for partitioning by month, you'll see things like naming the  
> partition tables "parent_table_name_$YEAR_$MONTH", so the  
> 'partitioning function' takes a date or timestamp and then returns  
> what partition it belongs to. Perhaps there is some way to use that  
> mapping to drive the selection of what partitions could contain a  
> given value?
> One possibility would be to require 3 functions for a partitioned  
> table: one accepts the partitioning key and tells you what partition  
> it's in, one that tells you what the minimum partitioning key for a  
> partition would be, and one that tells you what the maximum would be.  
> If the user supplied those 3 functions, I think it would be possibly  
> to automatically generate code for the triggers and check  
> constraints. The min/max partition key functions might allow you to  
> more efficiently do partition elimination, too.

ISTM this is a good idea.

SQLServer uses partitioning functions and I like that approach. It makes
it much easier to do partition-wise joins between tables that share
partitioning functions.

  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to