RDBMS - Cursor (Iterator)

Data System Architecture

About

A cursor represents data returned by a query.

In computer science, a database cursor is a control structure that enables traversal over the records in a database.

A cursor:

A cursor can be viewed as a pointer to one row in a set of rows.

The cursor can only reference one row at a time, but can move to other rows of the result set as needed and performs operations on this row.

The cursor data are mainly created in the database server and the data flows toward the client per block when needed.

The Cursors API facilitate data processing such as:

  • retrieval,
  • addition
  • and removal of database records.

The database cursor characteristic of traversal makes cursors closed to the programming language concept of iterator but as they are handled in a client/server architecture (and then not locally) they need to be closed.

A cursor is a handle for a session-specific private SQL area that holds:

  • and other processing information (position of the pointer to go forward and backward, …)

Characteristic of cursors

The following information may vary depending on the specific database system.

Network

Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE.

Repeated network round trips can severely impact the speed of the operation using the cursor.

Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.

Resources/Implementation

Cursors allocate resources on the server, such as:

  • locks,
  • packages,
  • processes,
  • and temporary storage.

For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query's result set.

If a cursor is not properly closed (deallocated), the resources will not be freed until the database session (connection) itself is closed. This wasting of resources on the server can lead to performance degradations and failures.

Documentation / Reference





Discover More
Card Puncher Data Processing
Android - Cursor

cursor implementation in Android. A cursor is what a content provider implementation will return in response of a query. Many iterators in Java implement the java/util/IteratorIterator interface, but...
Card Puncher Data Processing
Design Pattern - (Iterator|Cursor)

An iterator is an interface that can express sequences of unlimited size, such as the range of integers between 0 and Infinity. It allow a user to loop over every element of a collection (container) while...
Jdbc Class Architecture
JDBC - Resultset (SELECT|SQL query)

The java/sql/ResultSetResultSet interface encapsulates the results of an SQL query and implements a cursor API. Statements may also be batched, allowing an application to submit multiple updates to a data...
Card Puncher Data Processing
PL/SQL - Cursor

A cursor in the context of Oracle. A cursor is a SQL datatype. A cursor is a pointer to a private SQL area that stores information processing a specific: SELECT or DML statement. The cursor data...
Oracle Database Sql Processing
RDBMS - Sql Processing (Engine)

A SQL engine is responsible to produce a result set on the client side (generally backed by a cursor on the back-end side) from a SQL statement. See Every relational database has an SQL engine:...
Oracle Database Sql Processing
SQL Engine - SQL Parser

The parser in an SQL engine parse a Sql statement. It's the first stage of SQL processing. This stage involves separating the pieces of a SQL statement into a SQL Tree where each node is a SQL token...
Oracle Database Sql Processing
SQL Engine - Hard Parse (Library cache miss)

During the sql parsing (which is a step of the sql processing), if the Database (for instance Oracle) cannot reuse existing code, then it must build a new executable version of the application code (ie...
Oracle Database Sql Execution Parse Tree
SQL Engine - How to read a physical plan (Execution Plan | Execution Tree)

How to read a physical plan The figure below is an execution tree or physical plan, that shows the flow of row sources from one step to another. In general, the order of the steps (relational operator)...



Share this page:
Follow us:
Task Runner