Re: Partitioning vs. View of a UNION ALL

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)