From: jbdhl on 2 Mar 2010 06:10 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 2 Mar 2010 12:47 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 2 Mar 2010 16:03 > 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 2 Mar 2010 16:24 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 2 Mar 2010 17:17 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.
|
Pages: 1 Prev: Looking for DB2 9.5 LUW express-c Next: Making DB2 be I/O aggressive |