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