Forum OpenACS Q&A: Bloating up a view, is it possible?
I've been drinking from this well that has me using views to denormalize normalized table. Hey d00d, selecting from denormalized tables is so much easier (than thinking.)
If I create a view with a bazillion columns, some computed (e.g. (substr(a, 1, 4) || substr(b, 1, 4)) as passwd), and then I don't use those computed colums in a specific select, are those column values computed anyway?
Apart from use in a computed column used in a where statement, does selection from a view cause any copying to be performed?
Is a faster/better/cheaper to use a view of 100 columns in 10 queries where each query uses only 10 of the columns, or is it more performant (cough) to break that view up into smaller views?
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 :)
I do suspect that PG views aren't very smart about only materializing needed columns in a select but it should be easy to verify. Create a view with a column that calls a function that does something in a loop a few million times and try it...
retrieve the news content blob. Creating a second view
news_items_approved_sans_body and pointing the news queries in question to it,
made those queries lighter (maybe 50%).
So you need to be careful when retrieving "useless" values or perform
"useless" where clauses (in Oracle at least). There's a lot of code in OpenACS
that could and needs to reviewed in this respect.