From: jbdhl on
Consider a table and a query referring to only a subset of the columns
in that table. How early in the query evaluation is the projection
carried out?

Are the columns to be selected filtered out as early as in the very
access method that reads the table rows from the buffer, or are the
projection handled later, after the whole row has been fetched by the
access method?

Does it depend on the complexity of the query, how far down the three
that the projection is handled out?

Thanks!
From: Ian on
On 3/2/10 4:10 AM, jbdhl wrote:
> Consider a table and a query referring to only a subset of the columns
> in that table. How early in the query evaluation is the projection
> carried out?
>
> Are the columns to be selected filtered out as early as in the very
> access method that reads the table rows from the buffer, or are the
> projection handled later, after the whole row has been fetched by the
> access method?

DB2 stores rows on pages, and I/O requests are done at the page level,
so your query will fetch entire rows if it reads the table. If the
query can be satisfied using only indexes, it will only read in the
index page(s) necessary. But like tables, an index page may have more
than 1 key value.


If DB2 sorts rows or builds temporary tables to process your query, it
will only include the columns it requires in the sort -- depending on
where the sort takes place, this may only involve columns involved in
a join, or it could include all columns requested from a particular
table. But DB2 should not be including the columns the query isn't
interested in.




From: jbdhl on
> DB2 stores rows on pages, and I/O requests are done at the page level,
> so your query will fetch entire rows if it reads the table.

Data is read in a two steps: one I/O reads a page into the buffer. And
individual rows are thereafter fetched from the buffered page. The
access method that reads the relevant rows from the buffer, does that
ever perform any kind of projection itself? That is, will the
projection happen after the row has been read from the buffer?
From: The Boss on
jbdhl wrote:
> Consider a table and a query referring to only a subset of the columns
> in that table. How early in the query evaluation is the projection
> carried out?
>
> Are the columns to be selected filtered out as early as in the very
> access method that reads the table rows from the buffer, or are the
> projection handled later, after the whole row has been fetched by the
> access method?
>
> Does it depend on the complexity of the query, how far down the three
> that the projection is handled out?
>
> Thanks!

What problem are you trying to solve, given the fact that you've been asking
this very same question in groups/forums for 3 different DBMS's:
comp.databases.oracle.server
comp.databases.ibm-db2
http://www.eggheadcafe.com/software/aspnet/35820528/when-are-projections-done.aspx
(MSSQL-forum)

--
Jeroen


From: Ian on
On 3/2/10 2:03 PM, jbdhl wrote:
>> DB2 stores rows on pages, and I/O requests are done at the page level,
>> so your query will fetch entire rows if it reads the table.
>
> Data is read in a two steps: one I/O reads a page into the buffer. And
> individual rows are thereafter fetched from the buffered page. The
> access method that reads the relevant rows from the buffer, does that
> ever perform any kind of projection itself? That is, will the
> projection happen after the row has been read from the buffer?

I already answered this question. But, basically, it depends on the
access plan.