RE: value from index block or table block

Now if the index is actually corrupt that will present problems, but if someone has the energy to use a binary editor on a data file to carefully modify an index leaf block's column value without changing its length between two values where there is lexical room to make the change without changing the position for sorting, you can probably actually see.

As for me, I'm convinced enough by your demo using chained rows that I can't muster that energy.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, November 23, 2011 1:11 PM To: oracle-l_at_freelists.org
Subject: 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


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