Re: Constraint exclusion-like behavior for UNION ALL views

On 3/29/17 3:39 PM, David G. Johnston wrote:

> That said, I'm not sure what using materialized views instead of normal
> tables buys you in the first place. I could see possibly using a
> materialized view as the current month's table but the historical tables
> usually don't require refreshing.

My example was simplified for brevity. The actual materialized views in
question do a significant amount of work, pulling from several other
tables, grouping/aggregating, etc. It would be possible to have that
same query populate a normal table instead of being stored as a
materialized view, but there's a reason materialized views were created
in the first place -- to avoid the overhead of manually creating
triggers and so forth -- and I was hoping to find a way to retain those
advantages while also being able to partition the views by date.

My thought was that since check constraints already exist for regular
tables, and since materialized views are implemented as tables (or
table-like substances) it seems reasonable that materialized views
should support check constraints and the query optimization that comes
with them.

-Tony