Fetch into an Array
Oracle versions 7-8
A fetch involves two significant processing steps. First it confirms that data is available to satisfy the query. For a user session connecting by means of the API available to Oracle Pro*C/C++ programs, this involves populating the SQL communications area (SQLCA). Second, the fetch performs the memory-to-memory copy of the data from the database block buffer in the SGA to the user process. Both of these steps go through the server process that connects the user process (often on a different machine) to the Oracle instance and database. In a client-server environment, both steps involve network transmission.
If a fetch returns a single row of data to the user process, then there have been two trips across the network for this row of data. Fetching one hundred rows of data one row at a time implies two hundred trips, each calling a database kernel subroutine. This processing effort can be greatly reduced by fetching into an array.
An array fetch returning twenty rows would fetch data for the SQLCA and then would fetch twenty rows of table data. Fetching one hundred rows of data into a twenty-row array implies ten round trips, not two hundred.
The coding technique is not challenging. Here is an example supplied
by Tom Kyte of Oracle in his "Ask Tom" column.