Forum OpenACS Q&A: Re: Bloating up a view, is it possible?

Posted by Alfred Werner on
A view, like a table, describes a relation. The only place where they differ is on inserts - because some combinations don't work, like with underlying table unions. I think from a theory perspective it's far safer to regard views and tables as equivalent (again with the insert caveat).

In oracle, the view inserts are calculated, i.e. it figures it out for you. In postgres you have to provide the insert rule. This may mean you can reach some inserts that oracle can't figure out.

I don't think there's anything bad with using a view to denormalize to encapsulate a query - as long as you never insert or update it (update anomalies).

The optimizer should be smart enough to defer the computation - you don't say which database you're talking about, but I don't know enough of the PG or Oracle optimizer internals even if you did - although if you're reading Momjian, it's postgresql :)

A copy is only made in a materialized view.

Beats me. I'd just do whatever was easiest - but hey, that's just me. If it was too slow I'd test the other way or buy more RAM :)