Ron,
Even though using a view means that it would have to be recreated
each period as the oldest table is dropped,
please keep in mind:
views are not really "created" ... also the command is named "create view"
VIEWS, at least in PostgreSQL (and Oracle) are nothing else then "macros" for Queries - the views get substituted within the query with their creation rule.
That is: the "recreation" of a VIEW is a nearly instant process (it just is frustrating to connections using this view)
it seems that it would
still be easier to work with, since you wouldn't have to worry about
preventing a badly behaving user from inserting into the DDL
partition's parent table and create 588 CHECK constraints (12 per
year x 7 years x 7 base tables).
That is true only if you trust your users not to insert into the wrong table of your 12*7*7 tables.
If you have the appropriate check constraints on your parent table, the pushing data into the inherited tables should happen automagically (at least on my databases it does :) )
So... to make sure nobody inserts rubbish you will have to have those 588 check constraints one way or another.
a) to make your partitioning work
b) to ensure nobody inserts data for 2000 into the table for 1900
The most important issue, though, is query speed. Assuming
excellent index support for query WHERE clauses, regardless of
whether partitioning or a "viewed UNION ALL", which will the query
optimizer and constraint_exclusion be more friendly towards?
in an optimal world, should'nt those two options be exactly the same?
a) the partition solution:
query planner decides which of your 12*7*7 tables to access and only scans those. To my undestanding, " constraint_exclusion" only applies to this solution.
b) the "union all" - or "partitioning by hand":
at the beginning of each partial query there will be an index scan on your date-column, learning that no data comes from that partial query and planner skipping on to the next.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.