When are projections done?
in [Oracle]
|
Prev: Oracle database 10g R2
Next: Query with LONG RAW
From: Jonathan Lewis on 2 Mar 2010 16:21 "jbdhl" <jbirksdahl(a)gmail.com> wrote in message news:43971ed0-54f7-4094-84af-40eb1d00a946(a)f8g2000yqn.googlegroups.com... >> 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
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: jbdhl on 2 Mar 2010 18:52 > so I > think you can assume that the copy from the buffered > block extracts only the columns needed from the row. OK, perfect. Thanks!
From: joel garry on 3 Mar 2010 12:02 On Mar 2, 1:24 pm, "The Boss" <use...(a)No.Spam.Please.invalid> wrote: > 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
From: jbdhl on 3 Mar 2010 19:40
> 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. |