Outer joins in stored procedures

Let us suppose I have a single large table PEOPLE (with hundreds of thousands of entries) with a single primary key "NAME", and I want to write a PRO*C program whose job it is to retrieve PEOPLE information as fast as possible, meaning highest possible throughput, with no updates.

For simplicity, assume that one 1 field INFO needs to be retrieved for each. NAME. There is **no** to the people that are being retrieved; in other words, each SELECT will return 0 or 1 record.

It is simple to write a stored function GET_PEOPLE_INFO(name IN varchar2(80), info OUT varchar2(80))
returning 0 if sucessful and -1 otherwise.

When it comes to doing many batched transactions in a loop, the following methods suggest themselves:

[1] Write another stored procedure GET_BULK_PEOPLE_INFO that calls
GET_PEOPLE_INFO
many times, and receives and transmit its data via host arrays
[2] Same as [1], but use an OUTER JOIN on a temporary table.

The net effect of all this is to move the inner loop into the SQL engine itself, away from the PL/SQL level. My QUESTION is: is this likely to achieve anything, at for large enough batches? If, for example, batches of 500 are processed in each procedure call, it might introduce a significant latency, but might have a high aggregate throughput (assuming a multithreaded process).

Bruce Balden
Vancouver, BC.
balden_at_mail.bc.rogers.wave.ca Received on Sun Mar 01 1998 - 00:00:00 CST