3.6 Queries and DML Operations with Valid Time Support

This section describes some behaviors and considerations for queries and data manipulation language (insert, update, and delete) operations related to valid time support.

• Queries
  
• Data Manipulation (DML) Operations
  

3.6.1 Queries

All queries issued against a version-enabled table with valid time support take into account the current session's valid time setting (set using the SetValidTime or SetValidTimeFilterON procedure). Unless the query specifies otherwise (for example, by using one of the valid time support operators described in Operators for Valid Time Support), each query displays all rows from the underlying table having a valid time range that overlaps the session valid time or valid time filter, and that satisfy the other conditions of the query.

By default (that is, if the SetValidTime procedure has not been invoked in the session or if it was invoked with no parameters), all rows that are valid at the current time are considered valid, and the valid time period is considered to be from the current time forward without limit.

3.6.2 Data Manipulation (DML) Operations

All DML statements (INSERT, UPDATE, and DELETE) issued against a version-enabled table with valid time support take into account the current session's valid time setting and update mode. (The update mode is controlled by the SetWMValidUpdateModeON and SetWMValidUpdateModeOFF procedures.) The DML statements can affect all rows that are valid for the valid time period.

By default (that is, if the SetValidTime procedure has not been invoked in the session or if it was invoked with no parameters), all rows that are valid at the current time can be affected by DML statements, and all modified rows have their valid time range timestamps set as from the current time until changed.

The following sections describe additional considerations that apply to specific kinds of DML operations.

• Update Operations
  
• Insert Operations
  

3.6.2.1 Update Operations

Update operations to version-enabled tables with valid time support can be sequenced or nonsequenced.

A sequenced update operation occurs when you do not specify a change to the WM_VALID column in the UPDATE statement. In a sequenced update operation, the WM_VALID.ValidTill value for the row is changed to the ValidFrom timestamp of the current session valid time range, and a new row is created in which the WM_VALID period reflects the current session valid time range. Sequenced updates ensure that no duplicate records are created by an UPDATE statement, because the WM_VALID column values are different.

Example 3-13 shows a sequenced update operation, in which employee Baxter is given a raise. Before the update, there is one row for Baxter, with a salary of 40000 and a valid time period from 01-Jan-2000 until changed.

Example 3-13 Sequenced Update Operation

-- Update the salary for an existing employee. Perform "sequenced" update, so
-- that existing time-related information is preserved. This results in two rows
-- for Baxter.
-- First, set valid time to the intended range for Baxter's raise.
EXECUTE DBMS_WM.SetValidTime(TO_DATE('01-01-2003', 'MM-DD-YYYY'), 
  DBMS_WM.UNTIL_CHANGED);
-- Give Baxter a raise, effective 01-Jan-2003 until changed.
UPDATE employees SET salary = 45000 WHERE name = 'Baxter';

The update operation in Example 3-13 modifies the WM_VALID value of the existing row and creates a new row with the new salary value and the WM_VALID value reflecting the session valid time range, as shown by the following statements:

-- Set valid time to encompass virtually all time.
EXECUTE DBMS_WM.SetValidTime(TO_DATE('01-01-1900', 'MM-DD-YYYY'), TO_DATE('01-02-9999', 'MM-DD-YYYY'));

-- See what data exists for Baxter.
SELECT * FROM employees WHERE name = 'Baxter';

NAME                 SALARY
---------------- ----------
WM_VALID(VALIDFROM, VALIDTILL)
--------------------------------------------------------------------------------
Baxter                45000
WM_PERIOD('01-JAN-2003 12:00:00 -04:00', NULL)

Baxter                40000
WM_PERIOD('01-JAN-2000 12:00:00 -04:00', '01-JAN-2003 12:00:00 -04:00')

A sequenced delete operation deletes the portion of a row that falls within the session valid time range; that is, a new row is created in which the WM_VALID period reflects the current session valid time range, and then that row is deleted. If the UPDATE statement in Example 3-13 had instead been DELETE FROM employees WHERE name = 'Baxter';, the new row for Baxter, valid from 01-Jan-2003 until changed, would have been deleted, but any rows for Baxter valid before 01-Jan-2003 would not be affected. There is no concept of a non-sequenced delete operation; for example, if a valid time was not set in Example 3-13, a delete operation WHERE name = 'Baxter' would delete all rows for Baxter.

Sequenced update and delete operations are enabled when a table is version-enabled with valid time support or when valid time support is added to a version-enabled table. However, you can disable support for sequenced update and delete operations (as well as for nonsequenced update operations) by using the SetWMValidUpdateModeOFF procedure, and you can re-enable support by using the SetWMValidUpdateModeON procedure. (Both procedures are described in DBMS_WM Package: Reference .)

A nonsequenced update operation occurs when you specify a change to the WM_VALID column in the UPDATE statement. In a nonsequenced update operation, no additional row is created, and the WM_VALID column value of the updated row or rows reflects what you specified in the UPDATE statement. You must ensure that a nonsequenced update operation will not result in multiple rows with the same primary key value being valid in the period specified in the UPDATE statement; otherwise, the update fails because of a primary key constraint violation.

If the UPDATE statement in Example 3-13 had been a nonsequenced update operation, the result would have been only one row for Baxter: the existing row would have had the salary set to 45000 and the WM_VALID column set to the period specified in the UPDATE statement.

3.6.2.2 Insert Operations

When you insert a row into a version-enabled table with valid time support, you can specify a valid time period for the row. If you specify null timestamps for the period, the session valid time period is used.

When a row is inserted into a version-enabled table with valid time support, Workspace Manager checks to ensure that no existing rows with the same primary key value have a valid time range that overlaps the valid time range of the newly inserted row. If such a row is found, an exception is raised. Example 3-14 shows an attempted insert operation that violates a primary key constraint because overlapping valid time periods.

Example 3-14 Insert Operation Failing Because of Overlapping Time Periods

-- Insert. Should violate primary key constraint, because of overlapping times:
-- existing Coleman row is valid from 01-Jan-2003 until 31-Dec-9999.
INSERT INTO employees VALUES(
  'Coleman',
  55000,
  WMSYS.WM_PERIOD(TO_DATE('01-01-2004', 'MM-DD-YYYY'),
                  TO_DATE('12-31-9999', 'MM-DD-YYYY'))
);
)
*
ERROR at line 6:
ORA-20010: unique key violation 
ORA-06512: at "WM_DEVELOPER.OVM_INSERT_10", line 1 
ORA-04088: error during execution of trigger 'WM_DEVELOPER.OVM_INSERT_10'

To make the statement in Example 3-14 succeed, first change the WM_VALID.ValidTill attribute for the Coleman row to a timestamp reflecting 01-Jan-2004 or an earlier date.