A current valid-time query on a table with valid time considers only open rows where the period of validity overlaps with TEMPORAL_DATE or TEMPORAL_TIMESTAMP in the valid-time dimension. Such rows are called current rows of a table with valid time.
Current valid-time queries on tables with valid time produce snapshot tables as result sets.
- If the session valid-time qualifier is implicitly or explicitly set to current, a conventional SELECT statement that does not specify a temporal qualifier is current in the valid-time dimension for a valid-time table. If the query references a valid-time table, the SELECT can specify an optional CURRENT VALIDTIME.
- A current query can reference the valid-time column anywhere in the query. For valid-time columns defined using derived period columns, the query can reference the component columns that define the valid time. The valid-time column is treated as a conventional Period column, or for valid-time columns that are derived, the component columns are treated as conventional DateTime columns. All conditions, including those specified on valid-time columns, apply only to the current rows. Temporal column references can appear in conditions to further filter the output.
- An asterisk ( * ) in the projection list includes nontemporal columns only.
- Current query processing is as follows:
- Extract the current rows of each of the valid-time tables specified in the query and treat the query as if it is specified on a nontemporal table. The resulting table, regardless of whether the projection list includes the valid-time column, is a nontemporal table without the valid-time dimension.
- Execute the query as if it were a conventional query that was issued on tables without valid time.
Because current query processing considers a snapshot of valid time, all operations, such as joins and aggregations, are the same as they are for conventional queries.
A current query supports the join of two temporal tables of the same or differing valid-time granularities.
Changing the Behavior of CURRENT VALIDTIME SELECT
CURRENT VALIDTIME SELECT statements normally qualify rows for selection by choosing rows where the valid time overlaps TEMPORAL_TIMESTAMP. Within the transaction that contains the SELECT statement, TEMPORAL_TIMESTAMP reflects the time the transaction was begun, and remains fixed throughout the transaction.
For lengthy transactions this behavior may not be desirable, because rows inserted or changed after the transaction has begun would not be selected. Such rows would have a valid-time period that begins after TEMPORAL_TIMESTAMP, and would therefore be considered future rows, not current rows. For example, if another user adds a row to a table after a transaction has begun, and the transaction performs a CURRENT VALIDTIME SELECT, the new row would not be selected.
The following statement changes the behavior of CURRENT VALIDTIME SELECT statements to qualify rows for selection according to whether the valid time of the row overlaps CURRENT_TIMESTAMP. This allows the SELECT to match rows with the latest timestamps.
DIAGNOSTIC SET CURRENT VALIDTIME SELECT AS LATEST [ NOT ] ON FOR SESSION
Because CURRENT_TIMESTAMP is not fixed at the time the transaction was begun, this causes CURRENT VALIDTIME SELECT statements to match even the latest rows that were added or changed after the transaction containing the SELECT statement was begun.
This setting affects SELECT statements and subqueries, derived tables, and views within those SELECT statements. It has no effect on other types of DML, or on subqueries, derived tables, and views within those DML statements. The setting is in effect until it is explicitly disabled using the NOT form of the statement.