The only case I can think of where view partitioning makes more sense is if it's list partitioning where you can also drop a field from your tables. IE: if you have 10 projects, create 10 project_xx tables where xx is the ID of the project, UNION ALL them together in a view, and create rules on that view to handle DML. Note I haven't actually tested to see if this is better than inherited tables... On Fri, Oct 13, 2006 at 05:00:23AM -0500, Ron Johnson wrote: > Hi, > > I've gotten preliminary approval to buy a server and load a *lot* of > data into it. One table will eventually have 4.5Bn 330 bytes rows, > the other 9Bn 300 byte rows. Other will "only" have a billion rows. > They are easily partitioned by yyyymm, which we call FISCAL_PERIOD. > (In fact, the app creates the integer FISCAL_PERIOD by extracting > year and month from transaction date: YEAR*100+MONTH.) > > Even though using a view means that it would have to be recreated > each period as the oldest table is dropped, 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). > > 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? > > Thanks, > Ron > -- > Ron Johnson, Jr. > Jefferson LA USA > > Is "common sense" really valid? > For example, it is "common sense" to white-power racists that > whites are superior to blacks, and that those with brown skins > are mud people. > However, that "common sense" is obviously wrong. > > ---------------------------(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 > -- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
