Re: value from index block or table block

It's difficult to say for certain (and may be version dependent), but it's 
probably getting columns from the index whenever possible.

Two arguments in favour:
a) If you call dbms_xplan with the 'projection' option then you will see (with 
your example) that col2 appears in the projection for the index line of the 
execution plan - this is inconclusive.
b) if you set up your table so that you have a chained row with columns (colX 
and colY, say) in the "chained" part of the row and a new index (col1, colx), 
then you can show that the logical I/O and continued fetches are different when 
you run
    select col1, colX, col3 where col1 = constant
compared to
    select col1, colY, col3 where col1 = constant
a few variations on this theme are fairly convincing


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com


----- Original Message ----- 
From: "Grzegorz Goryszewski" <grzegorzof@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, November 23, 2011 3:56 PM
Subject: value from index block or table block


Hi,
I know that seems crazy but please clarify that for me ,let say for
10.2.0.3 but that does not matter .

Let say we got table t with col1, col2, col3 and index on that table on
col1, col2 and
query

select col1,col2,col3 from t where col1 = 'value' ;
Let say that
TABLE ACCESS BY INDEX ROWID was used for that query

My question from where Oracle retrieves col1,col2 values ?
From index block and then col3 value from table block via rowid found in
index block ?
Or maybe only rowid from index and then col1,col2,col3 values from table
block only ?

--
http://www.freelists.org/webpage/oracle-l

Other related posts: