Pseudocolumns

Pseudocolumns behave the same as table columns, but do not store values. Therefore, pseudocolumns have only the read attribute. You cannot perform operations on pseudocolumns, such as inserting, updating, and deleting data. Notice

ApsaraDB for OceanBase does not support the ROWID pseudocolumn.

Hierarchical query pseudocolumns

Hierarchical query pseudocolumns are valid only in hierarchical queries. To define hierarchical relationships in queries, you must use a CONNECT BY clause.

CONNECT_BY_ISCYCLE pseudocolumn

The CONNECT_BY_ISCYCLE pseudocolumn is used to assist in marking the row from which a loop starts. If the child node of the current row is also one of the ancestor nodes of the current row, CONNECT_BY_ISCYCLE returns 1. Otherwise, 0 is returned.

CONNECT_BY_ISCYCLE must be used in conjunction with NOCYCLE of the CONNECT BY clause. Otherwise, an error is reported for the query result because a loop exists in the tree structure result.

CONNECT_BY_ISLEAF pseudocolumn

The CONNECT_BY_ISLEAF pseudocolumn is used to assist in marking leaf nodes in a hierarchy structure. If the current row does not have a child node, that is, the current row is the leaf node of the tree, 1 is returned. Otherwise, 0 is returned.

LEVEL pseudocolumn

The LEVEL pseudocolumn is used to assist in marking the hierarchy of nodes. In the hierarchy structure, the root is at level 1, the child nodes of the root is at level 2, and so on. For example, the return value of LEVEL for the root node is 1, the return value of LEVEL for the child node of the root node is 2, and so on.

Sequence pseudocolumns

A sequence pseudocolumn is an auto-increment numeric sequence that is generated by the database based on specific rules. The sequence pseudocolumn is often used as primary keys and unique keys due to its auto-increment characteristics. You can use the following two methods to obtain the values of the sequence pseudocolumn:

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next auto-increment value of the sequence.

When you use a sequence pseudocolumn, you must add the sequence name before CURRVAL or NEXTVAL and use a period (.) for reference. For example, if the name of a sequence is SEQ_FOO , you can use SEQ_FOO.CURRVAL to obtain the current value of the SEQ_FOO sequence. You can also use SEQ_FOO.NEXTVAL to obtain the next auto-increment value of the SEQ_FOO sequence.

Application scenarios of sequence pseudocolumns

You can use the CURRVAL and NEXTVAL values of the sequence pseudocolumns in the following positions:

• The select lists of SELECT statements that are in subqueries, materialized views, or views

• The select lists of subqueries in INSERT statements

• The VALUES clauses in INSERT statements

• The SET clauses in UPDATE statements

You cannot use the CURRVAL and NEXTVAL values of the sequence pseudocolumns in the following positions:

• The subqueries in DELETE, SELECT, or UPDATE statements

• The queries of views or materialized views

• The SELECT statements that contain the DISTINCT operator

• The SELECT statements that contain the GROUP BY or ORDER BY clause

• The SELECT statement that is combined with another SELECT statement by using the UNION, INTERSECT, or MINUS set operator

• The WHERE clauses in SELECT statements

• The DEFAULT values of columns in CREATE TABLE or ALTER TABLE statements

• The conditions of CHECK constraints

How to use sequence pseudocolumns

When you create a sequence, you must specify its initial value and step size. When you reference NEXTVAL for the first time, the initial value of the sequence is returned. When you subsequently reference NEXTVAL, a new value is returned after the return value of the previous sequence plus the step size that is specified for the sequence. When you reference CURRVAL at any time, the current value of the sequence, that is, the return value for the last reference to NEXTVAL, is returned.

Before you reference the CURRVAL pseudocolumn of a sequence in a session, you must first initialize the sequence value for the current session by using the NEXTVAL pseudocolumn of the sequence.

When you create a sequence, you can define its initial value and the increment between values of the sequence. When you reference NEXTVAL for the first time, the initial value of the sequence is returned. When you subsequently reference NEXTVAL, the sequence value is incremented by the defined increment and a new value is returned. For any reference to CURRVAL, the current value of the sequence, that is, the return value for the last reference to NEXTVAL, is always returned. For more information about how to create sequences, see CREATE SEQUENCE.

If you reference NEXTVAL in a single SQL statement, ApsaraDB for OceanBase increments the sequence in the following ways:

• Each time the outer query block of a SELECT statement returns one row, the sequence is incremented once. Such a query block can appear in the following positions:

• Each time an UPDATE statement updates one row, the sequence is incremented once.

• For each INSERT statement that contains a VALUES clause, the sequence is incremented once.

• Each time a MERGE statement merges one row, the sequence is incremented once. NEXTVAL can appear in the merge_insert_clause or merge_update_clause clause or both. NEXTVAL is incremented as each row is updated and inserted, even if the sequence values are not used for the update or insert operation. If NEXTVAL is specified multiple times in these positions, the sequence is incremented once for each row. The same value is returned for all the occurrences of NEXTVAL in the row.

If the NEXTVAL pseudocolumn of a sequence is referenced multiple times in these positions, the sequence is incremented only once. That is, the next sequence value of the current sequence is returned for all the NEXTVAL pseudocolumns that are referenced.

If both CURRVAL and NEXTVAL pseudocolumns of a sequence are referenced in these positions, ApsaraDB for OceanBase increments the sequence. That is, the next sequence value of the current sequence is returned for the CURRVAL and NEXTVAL pseudocolumns that are referenced.

Multiple users can access a sequence at the same time without waiting and locking.

ROWSCN pseudocolumn

The ORA_ROWSCN pseudocolumn reflects the latest System Change Number (SCN) to a row. The SCN indicates the commit time of the transaction that modifies the data of this row.

ROWNUM pseudocolumn

The ROWNUM pseudocolumn numbers each row in the query result. The value indicates the specific position of the row in the query result set. The value 1 is returned for the first row, 2 for the second row, and so on.

Instructions on the usage of ROWNUM

ROWNUM can limit the number of returned rows. In the following example, five data records in the employees table are returned.

SELECT * FROM employees WHERE rownum <=5;

If an ORDER BY clause follows ROWNUM, the results that meet the WHERE conditions are resorted. Assume that you embed an ORDER BY clause in a subquery and use the ROWNUM pseudocolumn as a condition in the top-level query. In this case, you can force the ROWNUM condition to be applied after the rows are sorted. For example, you cannot obtain the expected result if you execute the following statement to query information about the five oldest employees. The statement only sorts the first five employee information entries in the query result by age:

SELECT * FROM employees WHERE rownum <=5 ORDER BY age DESC;

The following statement is valid:

SELECT * FROM (SELECT * FROM employees ORDER BY age DESC) WHERE rownum <= 5;

If you specify that ROWNUM is greater than a positive integer in the WHERE clause, FALSE is always returned. For example, the following SQL statement returns no information:

SELECT * FROM employees WHERE rownum > 1;

This is because when the result of the first row in the table is retrieved, the value of the ROWNUM pseudocolumn of this row is assigned 1. In this case, the result is FALSE when the WHERE condition is checked. This row is discarded. When the result of the second row is retrieved, the value of the ROWNUM pseudocolumn of this row is still assigned 1. The result is still FALSE when the WHERE condition is checked. This row is also discarded. This way, all the rows fail to meet the condition. Therefore, no data is returned.

You can also assign the ROWNUM values to a column in the table by executing a UPDATE statement, as shown in the following example.

UPDATE employees SET id = rownum;

The statement assigns the ROWNUM values to the id column in the employees table. The id column is assigned values 1, 2,... until the total number of rows in the table is reached. Notice

If you use ROWNUM in queries, view optimization may be affected.