3 Expressions

ROWID specification

TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid has data type ROWID. You can examine a rowid by querying the ROWID pseudocolumn.

Because the ROWID pseudocolumn is not a real column, it does not require database space and cannot be updated, indexed or dropped.

The rowid value persists throughout the life of the table row, but the system can reassign the rowid to a different row after the original row is deleted. Zero is not a valid value for a rowid.

Rowids persists through recovery, backup and restore operations. They do not persist through replication, ttMigrate or ttBulkCp operations.

See "Expression specification" for more information on rowids. See "ROWID data type" for more information about the ROWID data type.

ROWNUM specification

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which the row was selected. The first row selected has a ROWNUM of 1, the second a ROWNUM of 2, and so on.

Use ROWNUM to limit the number of rows returned by a query as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

The order in which rows are selected depends on the index used and the join order. If you specify an ORDER BY clause, ROWNUM is assigned before sorting. However, the presence of the ORDER BY clause may change the index used and the join order. If the order of selected rows changes, the ROWNUM value associated with each selected row could also change.

For example, the following query may return a different set of employees than the preceding query if a different index is used:

SELECT * FROM employees WHERE ROWNUM < 10 ORDER BY last_name;

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, the following query returns no rows:

SELECT * FROM employees WHERE ROWNUM > 1;

Use ROWNUM to assign unique values to each row of a table. For example:

UPDATE my_table SET column1 = ROWNUM;

If your query contains either FIRST NumRows or ROWS m TO n, do not use ROWNUM to restrict the number of rows returned. For example, the following query results in an error message:

SELECT FIRST 2 * FROM employees WHERE ROWNUM <1 ORDER BY employee_id;
2974: Using rownum to restrict number of rows returned cannot be combined with first N or rows M to N