Block cursors and lookahead cursors are both associated with NO-LOCK queries. These are queries where the lock status has been explicitly set to NO-LOCK or where the lock status has been explicitly set to SHARE-LOCK and the transaction isolation level is set to read-uncommitted. Block cursors and lookahead cursors are mutually exclusive.
Block cursors are more efficient than lookahead cursors for the following reasons:
The results of a query are bound directly to the area from which they are copied into the record buffer on the client. Lookahead cursors copy out of a common bind area into the lookahead cache and then are copied into client record buffer resulting in multiple copies of the data.
The fetch process used by block cursors fetches multiple rows at a time, reducing the number of driver calls and potentially reducing the total number of network round trips to the server.
Block cursor memory is preallocated prior to fetching a result set, whereas lookahead cursors post allocate memory as records are fetched from the result set.
Block cursors are directly linked to the result set binding feature whereas lookahead cursors have no particular dependency on whether binding or late-binding is active.
The results of a query are bound directly to the area from which they are copied into the record buffer on the client. Lookahead cursors copy out of a common bind area into the lookahead cache and then are copied into client record buffer resulting in multiple copies of the data.