Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> fast way to retrieve alla data blocks
hichem
post Apr 19 2012, 03:56 PM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 17-April 12
Member No.: 47,094



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

thank you very much for your forum rolleyes.gif , 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 .
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2012, 06:09 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,710
Joined: 26-January 04
Member No.: 13



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

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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
hichem
post Apr 28 2012, 05:40 PM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 17-April 12
Member No.: 47,094



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.
Go to the top of the page
 
+Quote Post
burleson
post Apr 28 2012, 06:18 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,710
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
hichem
post Apr 29 2012, 08:48 AM
Post #5


Newbie
*

Group: Members
Posts: 5
Joined: 17-April 12
Member No.: 47,094



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
Go to the top of the page
 
+Quote Post
burleson
post Apr 29 2012, 10:21 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,710
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th November 2014 - 12:35 PM