When are projections done?

#1

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!

#2

On Mar 2, 6:10*am, jbdhl <jbirksd... (AT) gmail (DOT) com> wrote:

snip

Quote:

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!

Why does it matter?

Any specific release you are investigating?

Any specific problem query that you are trying to optimize?

#3

"jbdhl" <jbirksdahl (AT) gmail (DOT) com> a écrit dans le message de news: a6342b7f-315f-4e33-b6b7-c5d0a35724c3...oglegroups.com...

Quote:

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!

Oracle does not fetch rows, it reads blocks matching the
conditions you gave and then returns you selected columns
when you fetch the rows.

Regards
Michel

#4

"jbdhl" <jbirksdahl (AT) gmail (DOT) com> wrote

Quote:

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!

At the earliest possible moment.

If you use dbms_xplan, one of the less well-known format
options is "advanced" which will give a list of the columns
projected at each line of the plan. (Funnily enough, the
"advanced" option gives more data than the "all" option.)

http://jonathanlewis.wordpress.com/2...6/dbms_xplan3/

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

#5

Quote:

At the earliest possible moment.

Could that be directly inside the access method that fetches
individual rows from the page in the buffer? Or is the projection
always performed at some point *after* the initial fetch of the
relevant row(s)?

#6

"jbdhl" <jbirksdahl (AT) gmail (DOT) com> wrote

Quote:

At the earliest possible moment.

Could that be directly inside the access method that fetches
individual rows from the page in the buffer? Or is the projection
always performed at some point *after* the initial fetch of the
relevant row(s)?

If you wanted a definite answer you'd have to ask the
Oracle programmers. But it wouldn't make sense to
copy a row from a buffered block into local memory
and then extract the fields from the local copy - so I
think you can assume that the copy from the buffered
block extracts only the columns needed from the row.
(There are aspects of the CPU costing algorithm that
tend to confirm this,)

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

#7

jbdhl wrote:

Quote:

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/...ions-done.aspx
(MSSQL-forum)

--
Jeroen

#8

Quote:

so I
think you can assume that the copy from the buffered
block extracts only the columns needed from the row.

OK, perfect. Thanks!

#9

On Mar 2, 1:24*pm, "The Boss" <use... (AT) No (DOT) Spam.Please.invalid> wrote:

Quote:

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-db2http://www.eggheadcafe.com/software/aspnet/35820528/when-are-projecti...
(MSSQL-forum)

Obviously, he wants to know the mechanics of how each engine does this
fundamental task. That's a good thing, in my opinion, I'd _like_ to
see a Kyte-style exposition of these all together. I don't think the
concepts and performance guides really cover this as they should, and
if there are decent 3rd party books, everyone would like to know.

Of course, asking the question this way avoids the whole concurrency
issue, which makes value judgements of the engines based on these
mechanics, well, baseless. If the OP is going there, that would
likely be a mistake.

I think tools such as tracing could/should be improved to make this
process more clear. The trace analysis tools have a long way to go,
and of course are limited by available instrumentation. There's a big
gap between the GUI tools, which (incorrectly) assume complete control
as well as a usage by the clueless, and the more sophisticated tools
which assume a certain level of knowledge (as the OP doesn't have -
note for example he seems unclear about how Oracle uses blocks and row
locking rather than pages - and most people wouldn't).

jg
--
@home.com is bogus.
http://www.oraclestore.com/images/products/489644.jpg

#10

Quote:

Obviously, he wants to know the mechanics of how each engine does this
fundamental task.

Exactly. In order to proceed with a research project, I need to know
how this basic task is handled in the most common row-stores. I
believe I have enough information for now. Thanks for the answers.