RE: How does Oracle know where to read from

From: Allen, Brandon <>
Date: Fri, 10 Apr 2009 15:11:23 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E11233E92C5_at_ONEWS06.oneneck.corp>

I'm not sure if this is what you're looking for, but the segments are mapped to extents and you can see where the extents start and end by looking in dba_extents - the underlying x$ tables are where Oracle looks to find the file_id and blocks. In your example below, it doesn't know immediately where that row is located - first it has to either use an index or a FTS to find the row, and first it uses the x$ tables beneath dba_extents to find out what blocks to read for the appropriate index and/or table.


From: [] On Behalf Of amonte Sent: Friday, April 10, 2009 3:03 PM
To: K Gopalakrishnan
Subject: Re: How does Oracle know where to read from

Not really, I understand that part but to find something we need to know where to look for.

Say we have segments emp and its primary key emp_pk in tablespace users and absolute file_id 5, we want to run this query

select *
from emp
where empno = 1234

If this is the input, how does Oracle know this row is located in file_id 5?

If recursive queries is used to find and compute the dba then an index unique scan will never be 1 consistent get (in the second execution) no?

Thank you


2009/4/10 K Gopalakrishnan <<>> Alex, Let me try to answer in simple way.

When you run a query, data dictionary provides the file#,block# or extent info for that table. Once you know the file#,blok# you can compute the dba and search the buffer cache for that dba (yet another simple hash function). If not found send an I/O request on that file#,block#.

Is this what you are looking for or something else/?

On Fri, Apr 10, 2009 at 3:51 PM, amonte <<>> wrote:
> Hi all
> I have a probably very basic question but I cant think how Oracle does it.
> When we query a table we know that data block address is hashed to
> read if buffer chain if reading from cache, otherwise look the rows in
> the data block address.
> My question is when we run a query how is the data block address
> obtained? From where?
> Alex
> --

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
Received on Fri Apr 10 2009 - 17:11:23 CDT

Original text of this message