In a keyset-driven cursor, the membership and order of rows in the result set are fixed at cursor-open time, but value changes made by the cursor owner and committed changes made by other users are visible. If a change disqualifies a row for membership or affects the order of a row, the row does not disappear or move unless the cursor is closed and reopened. Inserted phantom rows data does not appear, but changes to existing data do appear as the rows are fetched.
Specify keyset-driven cursors by issuing the SET SCROLLOPTION KEYSET statement. Dynamic standard cursors are the default if no SET SCROLLOPTION statement is issued.
In a keyset-driven cursor, all keys for the result set are kept locally (which is one reason a unique index is required). Given the results of n rows, the keyset contains the same n rows in the result set. The fetch buffer contains 10 rows by default, and moves forward through the keysets as each FETCH statement is executed. If the SET ANSI_DEFAULTS ON statement is issued, the fetch buffer contains 1 row. You can modify the size of the fetch buffer by using the SET FETCH_BUFFER statement.
Although values can change between fetches, rows do not move around if the changes affect ORDER BY columns, and they do not disappear if they no longer satisfy the WHERE clause.