When are projections done?
jbdhl
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!
John Hurley
snip
Why does it matter?
Any specific release you are investigating?
Any specific problem query that you are trying to optimize?
Michel Cadot
"jbdhl" <jbirk...@gmail.com> a �crit dans le message de news: a6342b7f-315f-4e33...@t23g2000yqt.googlegroups.com...
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
Jonathan Lewis
news:a6342b7f-315f-4e33...@t23g2000yqt.googlegroups.com...
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/2008/03/06/dbms_xplan3/
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
jbdhl
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)?
Jonathan Lewis
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,)
The Boss
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
jbdhl
> think you can assume that the copy from the buffered
> block extracts only the columns needed from the row.
OK, perfect. Thanks!
joel garry
> (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
jbdhl
> 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.
John Hurley
snip
If it is real research you are doing you need to talk to the
developers of the software.
You need to do some testing and generate hypo's and evaluate the
results of your tests.
Getting people's opinions on how it "may be getting done" is a survey
not research at least in my opinion.