11. Understanding JDBC Connectivity : JDBC Implementation Considerations : Cursors and Select Loops
Share this page                  
Cursors and Select Loops
By default, the JDBC Driver uses a cursor to issue SQL select queries. Cursors permit other SQL operations, such as deletes or updates, to be performed while the cursor is open. (Operations can be restricted during autocommit, as described in How Transactions Are Autocommitted (see page How Transactions Are Autocommitted).)
Cursors also permit multiple queries to be active at the same time. These capabilities are possible because only a limited number of result rows (frequently only a single row) are returned by the DBMS Server for each cursor fetch request. The low ratio of driver requests to returned rows results in lower performance compared to other access methods.
The JDBC Driver uses cursor pre-fetch capabilities whenever possible. Updatable cursors only return a single row for each fetch request. READONLY cursors return a fixed number of rows on each fetch request. For details, see Cursors and Result Set Characteristics (see page Cursors and Result Set Characteristics). By default, the JDBC Driver obtains as many rows as fit in one communications block on each fetch request.
Depending on row size, this can greatly increase data access efficiency. The application can also specify the number of rows to be retrieved for READONLY cursors by using the setFetchSize() method.
The JDBC Driver also permits the JDBC application to use a data access method called a select loop. In a select loop request, the DBMS Server returns all the result rows in a single data stream to the driver. Because select loops use the connection while the result set is open, no other operation or query can be performed until the result set is closed.
The statement cancel() method can be used to interrupt a select loop data stream when a result set needs to be closed before the last row is processed. Because the DBMS Server does not wait for fetch requests from the driver, this access method is the most efficient available.
Select loops are enabled in the JDBC Driver by setting the driver connection property select_loop to a value of 'on.' For more information, see JDBC Driver Properties (see page JDBC Driver Properties).
With select loops enabled, the driver avoids using cursors for SELECT queries unless explicitly indicated by the application. An application can request a cursor be used for a query by assigning a cursor name to the statement (setCursorName() method) or by using the JDBC syntax 'SELECT FOR UPDATE ...' to request an updatable cursor.