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_at_interia.pl> To: <oracle-l_at_freelists.org> 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 Received on Wed Nov 23 2011 - 12:10:42 CST