Re: question on retrieving rows

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 16 Mar 2003 14:34:02 GMT
Message-ID: <b5220m$248i8f$1@ID-82536.news.dfncis.de>

> Hi,
> sorry to confuse you. newbie here :P
> like u said, lets say 1 or more rows are on a particular block, and
> off course oracle should know the block location for these rows before
> it retrieve them. My question is from where does oracle get this
> information that these rows are resided on that block? (and how if the
> table is not indexed?)

Without indexes, Oracle doesn't know where each row of a table is. But what it knows is where the blocks are that the data of a table resides on. You can find out about these locations if you query DBA_EXTENTS and DBA_DATA_FILES:

select file_name, s.block_id, s.blocks from dba_data_files f, dba_extents s where f.file_id = s.file_id and s.segment_name = 'TABLE_NAME' and s.owner= 'USER_NAME' Each row returned gives you the information about the location of a chunk of blocks in a file for a table. block_id is the starting block, and blocks is the number of blocks.

Now, if you query a table for some values and there is no index on the table, Oracle has to retrieve all blocks and compare the values in the where condition with the actual values on the disk.

hth
Rene Nyffenegger

> 
> 
> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<tkTca.96752$eG2.13965_at_sccrnsc03>...

>> Note sure I understand the question. The data is stored in blocks. So in a
>> block there are 1 or more rows. If you are accessing the data via some
>> index then the index has the address of the block and the row in the block
>> as part of the index (row id)
>> Jim
>>
>> --
>> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
>> with family. Remove the negative part, keep the minus sign. You can figure
>> it out.
>> "andi" <andihp_at_flashmail.com> wrote in message
>> news:16ce60f4.0303151947.6d414040_at_posting.google.com...
>> > Hi All,
>> > how does oracle know the starting and ending blocks number of
>> > particular tables' rows ?
>> >
>> > TIA,
>> > andi

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Sun Mar 16 2003 - 08:34:02 CST