fast way to retrieve alla data blocks Options

[hichem]

Hello,
this is Hichem IT engineer. and i a m new in this foum,

thank you very much for your forum , I am interested about the fast way to access all data in physical block
what is the quick way to bring data blocks using the rowid,
I found this script but soon as I can have faster access:
select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one

my question is can retrieve all the data in one block more quick than this query.
in wait thanks .

[burleson]

>> select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';

That should cauase a full scan since you cannot index on rowid.

[hichem]

Very glad to discuss with u, Thank you mister for your response, my question is can i retreive all data block in one transaction, cause i know that the header reader from hard disc retrieve one data block in each transaction, is it possible to read all the data of that block in one transaction, i hope that my question is comprehensible , in waiting your response best regards
yours Hichem.

[burleson]

Hi Hichem,

>> is it possible to read all the data of that block in one transaction

Why do you want to do this? Oracle data blocks make no relationship to the data?

Take a look at dbms_rowid.rowid_block_number

When you fetch a row, Oracle "reads" the entire data block from disk into the db_cache_size RAM data buffer.

ROWID is a psuedo-column, and Oracle ROWID values contain information necessary to locate a row on a specific data block:


http://docs.oracle.com/cd/B19306_01/server...ocolumns008.htm

ROWID Locates:

• The data object number of the object
• The data block in the datafile in which the row resides

[hichem]

Thanx; lot
in my project i want to optimize a query, and the solution is to retrieve all a block and make a join with an ohter table
u have say
<<---- When you fetch a row, Oracle "reads" the entire data block from disk into the db_cache_size RAM data buffer-->>
and here is my reflection, since the data block is present in the db_cache_size, if i want to retrieve an other row present in this block do i need to access the hard disk (and that is expensive in time) or oracle gonna retrieve it from the db_cache_size, it is important to me cause my job is optimization of the query.

thanks

[burleson]

oracle gonna retrieve it from the db_cache_size!!


Learn to use the autotrace utility, that will tell you total disk and buffer I/O:

http://www.dba-oracle.com/t_OracleAutotrace.htm