RE: How does Oracle know where to read from
Date: Fri, 10 Apr 2009 15:11:23 -0700
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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
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?
2009/4/10 K Gopalakrishnan <kaygopal_at_gmail.com<mailto:kaygopal_at_gmail.com>> 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 <ax.mount_at_gmail.com<mailto:ax.mount_at_gmail.com>> 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?
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