Re: BULK COLLECT - Can it be implemented for a few rows .

Aravindh (knaravindh81_at_gmail.com) wrote:
: Hi ,

: The original requirement was to REDUCE the amount of DISK READS. Since
: the OLD PROC was consuming a heavy disk reads and it was loading the
: database . Thats why we designed a new procedure using the BULK COLLECT
: approach .

: I read all over the net and all the m,aterials said that the BULK
: collect was the fastest and the efficient . But the net disk reads did
: not come down when it was run on the INCREMENTAL basis for about 850
: rows / 4500 rows.

: However the total disk reads when run for a FULL LOAD did come down for
: the new procedure .

: So can you please suggest ways to reduce the disk reads in the NEW PROC

Well I don't know this for any kind of fact, but it seems to me that if you are restricting the rows to be read then at some low level oracle will have to target and read individual chunks of the disk to get those specific rows, so doing a bulk read of those rows might very well not reduce disk reads compared to a simple loop of one row at a time. (Presumably, processing them in bulk would still save in other areas.)

On the other hand, a full load equates to reading the entire table, which permits oracle to simply read all the disk chunks of that table in the largest possible continuous regions, which would indeed be expected to reduce the number of individual disk reads.

Based on that analysis (which may be bogus), the only way to reduce disk reads would be to find a way to force all the targetted data into contiguous regions, and of course Oracle would have to "know" about that contiguity to be able to make use of it.

Perhaps it is a coincidence, but there is a feature called table "partitioning", which allows rows with similar attributes to be stored "together". I would be curious if you could partition the data in such a way that the things you wish to read in bulk are always together in a partition, and perhaps then oracle would be able to read that partition as a single unit when you select just the targetted rows.

However, all this is mere speculation on my part, and may just as likely be exposing my own ignorance of the subject. Received on Thu Dec 08 2005 - 03:28:22 CET