Re: value from index block or table block

From: Jonathan Lewis <>
Date: Wed, 23 Nov 2011 18:10:42 -0000
Message-ID: <2823D10778A340EB9CB5F3F5171EC2A6_at_Primary>

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


Jonathan Lewis

  • Original Message ----- From: "Grzegorz Goryszewski" <> To: <> Sent: Wednesday, November 23, 2011 3:56 PM Subject: value from index block or table block

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

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

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 ?

-- Received on Wed Nov 23 2011 - 12:10:42 CST

Original text of this message