13.6. More Database Objects

Sequences

In SQL, a sequence is a database object that automatically generates a succession of values—note that this is a completely different meaning of the word “sequence” from its usage in XQuery, as we’ll see later. Sequence objects were first introduced in SQL:2003 and are useful whenever it’s necessary to produce a unique key value. Having the unique value generated inside the database, instead of in an external application, reduces the possibility of issues arising in areas such as performance and concurrency. Some SQL implementations may precompute the next values in a sequence and cache these ready for immediate use.

In some respects, a sequence is similar to an identity column in a table. However, unlike an identity column, a sequence is not tied to one specific table, and sequence values are unique within a database, rather than being unique within a table. Sequences can be created using the following syntax.

create sequence sequence-name
  [ as data-type | start with start-value |
  increment by increment-value |
  maxvalue value | no maxvalue
  minvalue value | no minvalue |
  cycle | no cycle [ .... ]]

The data type must be a numeric type with a scale of zero (e.g., integer). The options are similar to those for identity columns, which were discussed earlier. One additional feature of sequences is that some implementations also allow a zero increment, so that the sequence effectively becomes a global constant value.

The standard syntax for obtaining the next value from a sequence is:

next value for sequence-name

but some implementations may have variations on this. They may also provide some means of accessing the last sequence number generated without generating a new one, such as:

previous value for sequence-name

SQL also defines drop sequence and alter sequence commands, but since these work in the obvious way, we won’t discuss them here.

Indexes

An indexis typically an ordered binary tree of key values, with pointers to the locations of the full records used to store the data. Because indexes are relevant only for physical modeling, not logical modeling, the SQL standard will never mention them. However, most commercial systems allow users to explicitly create and drop indexes, using syntax such as create [ unique ] index indexname on tablename (index-column [ asc | desc ][,...]) and drop index indexname. Optimizers use indexes to improve performance and may create and drop indexes themselves. If supported, the unique index option provides one way of enforcing uniqueness constraints. However, most uniqueness constraints are better declared using primary key or unique declarations, although uniqueness constraints on optional columns usually require triggers. Indexes are often created automatically for primary and foreign keys.

Indexing can dramatically improve query performance, especially for large tables, since indexes often allow random access in main memory rather than sequential disk access. Some commercial systems may also allow a chosen index to be specified as clustered.This creates an alignment between the logical sequence of values in the indexed column(s) and the order in which rows are physically stored, potentially offering improved performance in retrieving row data. Obviously, if a table has multiple indexes, only one can be selected as the basis for clustering; typically this will be the index on the primary key column(s).

Although query efficiency (and hence perhaps constraint enforcement performance) can be improved by indexing, there is a price to be paid. Indexing may slow down the updating of a table, since any change to the data will involve not only a change to the base table, but also to any indexes that have been defined. However, this trade-off may still be acceptable, depending on the situation. Individual DBMSs may offer many different kinds of index, and strategies for choosing indexes may also vary from one DBMS to another. Since this book is primary concerned with conceptual and logical modeling, index strategies will be not be considered further.

Cursors

A cursor is essentially a mechanism to provide row-at-a-time access to the results of a prespecified select statement. The result is retained for as long as the cursor is being used (during which time the cursor is said to be open).Using a cursor is a four-stage process.

It’s fairly common practice for these steps to be controlled directly from an application program. This is one reason why cursors have a reputation for sluggish operation, which is discussed in more detail later.

Most vendors support the basic SQL-92 syntax for cursor declaration, which is shown here. Some details were added in later versions of the standard, but these did not affect the basic SQL cursor concept.

declare cursor-name [ sensitive | insensitive ] [ scroll | no scroll] cursor
  for select_statement
  [for { read only | update [ of column_name [ .... ]] } ]

Optionally, the cursor can be declared as for read only (which improves performance if updating is not required) or for update, possibly restricted to specific columns. A cursor that is declared as insensitive makes a temporary copy of the data to be used, and all data requests are answered from this temporary table. Modifications therefore cannot be made to base tables. If insensitive is omitted, or the sensitive option is chosen, changes made to the underlying tables may be reflected in subsequent fetches. The scroll option specifies that a range of navigation operations should be enabled for the cursor. If omitted, or if the no scroll option is chosen, the cursor is limited to just fetching the next row of the result. Once a cursor is defined, it can be opened. The syntax could hardly be simpler:

open cursor-name

There’s no option for the open clause to change the select statement; it simply executes the one already defined in the associated cursor declaration. It also positions the cursor to point just before the first row of the result set. Given an open cursor, data can be fetched from the results produced by the select statement.

fetch [ [ next | prior | first | last | { absolute | relative } ]from ] cursor-name
  into target-specification [ .... ]

In the navigation options, next fetches the next row from the current cursor position, prior fetches the previous row, first and last, respectively, fetch the first and last rows of the result set, absolute retrieves a row by its row number in the result set, and relative moves the cursor the specified number of rows backward or forward in the result set. If none of the navigation options is specified in the fetch clause, next is assumed. If the cursor declaration did not include scroll, the only option available is next. Under these circumstances, if we want to move to a previous row we have to close the cursor, reopen it, and then fetch repeatedly until we reach the required row.

The target-specification varies somewhat depending on the environment that the data is being fetched into. The main restriction is that the number of variables waiting to receive values must be the same as the number of columns produced by the select statement associated with the cursor. Closing a cursor is as simple as opening it.

close cursor-name

Once the cursor is closed, its result set becomes undefined. Cursors are automatically closed at the end of the transaction they are contained within, even if a close command is not explicitly used. The following code fragment illustrates a typical series of cursor activities. The host language here is ’C.

/* variables dnum etc. will have been declared previously */

exec sql declare c1 cursor for
  select deptno, deptname, mgrno from tdept
  where admrdept = 'AO0';

exec sql open  c1;

while ( SQLCODE==O ) {
  exec sql fetch c1 into :dnum, :dname, :mnum;

   */       -- do something interesting with the values

   */
}

exec sql close  c1;
/*carry on */

Cursors have a reputation for poor performance. One reason for this is that they are often used to retrieve data for use in an application program running on a different server to the database system. This requires some processing effort on the application server to construct the cursor and submit it to the databases server. More importantly, the network overhead (serializing, transmission, and deserializing, both ways) introduces a significant lag in operations. This can be partly addressed by moving the cursor to a stored procedure in the database server. A better solution is to rethink the data requirements in terms of set operations rather than row-at-a-time operations, using the inherent relational power of the database server.