Re: How many blocks are read from Disk to Buffer Cache?

"Tech Geek" <Tech_Geek_at_gawab-IHateSpam.com> wrote in message news:Uyfsb.29463$E9.7736_at_nwrddc01.gnilink.net...
> Hi,
>
> I am trying to understand this.
>
> 1. Assuming that I have a table, say of 1000 used blocks (below HWM) and
I
> use the following sql statement which uses a Full Table Scan, to get rows
> which are in about 100 blocks. Will the server process gets or copies all
> the 1000 blocks and puts in the DB Buffer cache or only the blocks (100 in
> this case) which has the candidate records/rows.
>
> Select * from sales where purchase_date between ' ' and ' ';
> (For this test scenario, please assume that I don't have an index on
> purchase_date column).

It will load all 1000 blocks. How is it supposed to know which ones contain 'candidate rows' until it's had a look at them? Guesswork??

> 2. When a query which returns only one row and uses an index to get to
the
> candidate row/block, will it get only the block which has the record to
the
> DBBC or the number of blocks are defined by db_file_multiblock_read_count?

When you retrieve blocks via a full table scan, you do so by reading multiple blocks at one time. But since an index tells you precisely which block(s) it is that contains the required row(s), those reads are always done one block at a time. Even if there are lots of blocks to fetch because you're selecting lots of rows: if Oracle uses an index, it reads one block at a time (which is why, incidentally, if you really are selecting lots of rows as a proportion of the total number of rows, the optimiser is most unlikely to want to go via the index, and will opt to do full table scans instead. The cut-off point where Oracle decides to give up on indexes is very low indeed -around the 2% - 5% mark. If you select more than that percentage of the total number of rows, the single-block access becomes more expensive than just grabbing the lot via multi-block reads).

Regards
HJR
>
> Will some one please help me understand this?
>
>
> I would appreciate your time and help
>
> Thanks
> TG
>
>
Received on Tue Nov 11 2003 - 20:34:22 CST