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

Collapse
1: Bloating up a view, is it possible?
Posted by Jerry Asher on 10/03/03 04:00 AM
So a view isn't a real table.  Reading Momjian's book, I get the impression of a view as more of a macro, a series of descriptions to the pg engine on how to expand a query.  I don't if this is true or not.

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?

Collapse
2: Re: Bloating up a view, is it possible? (response to 1)
Posted by Alfred Werner on 10/03/03 07:28 AM
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 :)

Collapse
3: Re: Bloating up a view, is it possible? (response to 1)
Posted by Jonathan Ellis on 10/04/03 06:55 AM
Like Alfred said, there is no copying (tuple updates) preformed when selecting from a view.

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...

Collapse
4: Re: Bloating up a view, is it possible? (response to 1)
Posted by Dirk Gomez on 10/05/03 04:56 PM
When I tuned a news application I found that all the news_items_* views
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.