"Simon Riggs" <[EMAIL PROTECTED]> writes: > If we partition on invoice_date only, there is an implication that > people will search for invoices on date range only too, otherwise why > not just partition on invoice_id. This still works with the compound key > approach.
Well there are practical problems with partitioning on invoice_id. It's convenient to have a predictable partition definition that can be calculated in advance. I suspect what people did with paper invoices is look at the last invoice for a period and note the invoice_id down to check all future invoice_ids against. Essentially partitioning on two separate equivalent partition keys. We could do the same sort of thing since we're looking at constraints, there's nothing stopping the partitions from having two separate but effectively equivalent constraints on them. I'm not sure how to describe "partition based on this rule for dates but note the range of invoice_ids covering a partition and generate a constraint for that as well" But if we could find a way to represent that it would make a lot of common use cases much more convenient to use. >> (But that sounds rather like pie in the sky, actually. Which other >> databases can do that, and how do they do it?) > > Oracle does it, by building a big index. Few people use it. The people that use it are the people stuck by dogmatic rules about "every table must have a primary key" or "every logical constraint must be protected by a database constraint". Ie, database shops run by the CYA principle. But if a database feature is hurting you more than it's helping you then you're not doing yourself any favours by using it. The database is a tool to make your life easier, not something to flog yourself with to prove how good your database design skills are. Oracle calls these "global" indexes and imho they defeat the whole purpose behind partitioning your data in the first place. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
