Choosing a Cursor Type
Choosing a cursor type depends on several variables that include:
Size of the result set.
Percentage of the data likely to be needed.
Performance of the cursor open.
Need for cursor operations, such as scrolling or positioned updates.
Level of visibility to data modifications made by other users.
Rules for Choosing a Cursor Type
Some rules to follow when choosing a cursor type are:
Basetable and forward-only cursors open faster than static or keyset-driven cursors. Internal temporary work tables must be built when static and keyset-driven cursors are opened, but they are not required for basetable and forward-only cursors.
Static and keyset-driven cursors increase the usage of the temporary database. Static server cursors build the entire cursor in the temporary database. Keyset-driven cursors build the keyset in the temporary database.
If the base table is retrieved as is, without any projection or selection, then a base table cursor is recommended because it has the least overhead.
Static cursors consume the most resources because they duplicate the data.