Hi,
I'm running an experiment on my own home database to understand how data is retrieved from the buffer_cache. Using Oracle 10g 10.2.0.3.0. I've populated 2 tables with 10million records each. The table structure is as follows.
create table bigtable(col1 varchar2(50), col2 varchar2(50))
create table bigtablechild(col1 varchar2(50), col2 varchar2(50))
bigtablechild.col1 is a foreign key to bigtable.col1 and there are no indexes on the tables.
When I run this query, it takes about 30 s to return data each time it runs. I can also see hard drive activity running on my handy hard drive monitor utility each time. I thought that once the data is loaded into the buffer_cache, the query wouldn't scan the hard disk for data. Could someone please help me understand what is happening?
select b.col2 from bigtable a, bigtablechild b where a.col1 = b.col1 and a.col1 = 'ABC8876'
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 5 | 5 |00:00:23.00 | 93671 | 90663 |
|* 2 | TABLE ACCESS FULL| BIGTABLE | 1 | 1 | 1 |00:00:14.26 | 57799 | 54931 |
|* 3 | TABLE ACCESS FULL| BIGTABLECHILD | 1 | 5 | 5 |00:00:08.74 | 35872 | 35732 |
-------------------------------------------------------------------------------------------------------
Edited by: arizona9952 on Jan 19, 2013 9:38 AM