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.