Thread: Views - Under the Hood
Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 terry@chosen-ones.org
On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker <terry@chosen-ones.org> wrote: > Greetings: > > Lately, I've begun using views quite often especially when queries for various > reports, etc. become complicated. I am now wondering if there is a price to > pay in terms of overhead for this. In truth, I don't really understand how a > view works. I know that it takes on many of the attributes of a table, but is > it a table? Is the data pulled together when one selects from the view or is > it maintained as a table all along. Guidance to the ignorant appreciated... In pgsql a view is actually a rule that fires off the original query for you. So it's a simple wrapper, and is the same, for the most part, as simply typing in the original query again. So, it's pretty simple, and there's no real overhead to worry about.
On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote: > On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker <terry@chosen-ones.org> wrote: > > Greetings: > > > > Lately, I've begun using views quite often especially when queries for > > various reports, etc. become complicated. I am now wondering if there is > > a price to pay in terms of overhead for this. In truth, I don't really > > understand how a view works. I know that it takes on many of the > > attributes of a table, but is it a table? Is the data pulled together > > when one selects from the view or is it maintained as a table all along. > > Guidance to the ignorant appreciated... > > In pgsql a view is actually a rule that fires off the original query > for you. So it's a simple wrapper, and is the same, for the most > part, as simply typing in the original query again. So, it's pretty > simple, and there's no real overhead to worry about. Thank you Scott! This is exactly what I needed to know... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 terry@chosen-ones.org
terry@chosen-ones.org (Terry Lee Tucker) writes: > Lately, I've begun using views quite often especially when queries for various > reports, etc. become complicated. I am now wondering if there is a price to > pay in terms of overhead for this. In truth, I don't really understand how a > view works. I know that it takes on many of the attributes of a table, but is > it a table? Is the data pulled together when one selects from the view or is > it maintained as a table all along. Guidance to the ignorant appreciated... Under the hood, views represent a rewriting of the query. http://www.postgresql.org/docs/8.4/static/rules-views.html If you have two tables that are joined together, in a view, then when you query the view, you're really running a more complex query than you're seeing, namely one that joins together the two tables, and does whatever else you put into your query. It *looks* like a table, for almost all intents and purposes, but what it is, really, is a structure that leads to your queries being rewritten to access the *real* tables that underly the view. So the date is, as you suggest, "pulled together when one selects from the view." -- output = reverse("moc.liamg" "@" "enworbbc") http://www3.sympatico.ca/cbbrowne/slony.html "People are more vocally opposed to fur than leather because it's easier to harass rich women than motorcycle gangs." [bumper sticker]
On 04/11/2010 19:58, Chris Browne wrote: > Under the hood, views represent a rewriting of the query. > > http://www.postgresql.org/docs/8.4/static/rules-views.html > > If you have two tables that are joined together, in a view, then when > you query the view, you're really running a more complex query than > you're seeing, namely one that joins together the two tables, and does > whatever else you put into your query. > > It *looks* like a table, for almost all intents and purposes, but what > it is, really, is a structure that leads to your queries being rewritten > to access the *real* tables that underly the view. Besides not being able to write to views without adding extra rules, are there are other intents and purposes for which a view doesn't look like a table? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
One of the benefits of writing views instead of using SQL in your code, is that any developer or developer tool can use the view. So the DB developer writes the view and maybe define indexes that can speed up the query and any developer of any software that uses the DB can refer to the View instead of writing the SQL. Moreover if sw developers use tools like ORMs those toolo can take advantage of the View and write all the code to use them to speed up the sw developer work. Il 04/11/2010 20.24, Terry Lee Tucker ha scritto: > On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote: >> On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker <terry@chosen-ones.org> > wrote: >>> Greetings: >>> >>> Lately, I've begun using views quite often especially when queries for >>> various reports, etc. become complicated. I am now wondering if there is >>> a price to pay in terms of overhead for this. In truth, I don't really >>> understand how a view works. I know that it takes on many of the >>> attributes of a table, but is it a table? Is the data pulled together >>> when one selects from the view or is it maintained as a table all along. >>> Guidance to the ignorant appreciated... >> In pgsql a view is actually a rule that fires off the original query >> for you. So it's a simple wrapper, and is the same, for the most >> part, as simply typing in the original query again. So, it's pretty >> simple, and there's no real overhead to worry about. > Thank you Scott! This is exactly what I needed to know... > -- ================================================== dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it ==================================================