3.1 Valid Time Support: Introduction and Example

Some applications need to store data with an associated time range that indicates the validity of the data. That is, each record is valid only within the time range associated with the record.

You can enable valid time support when you version-enable a table. (You can also add valid time support to an existing version-enabled table, as explained in Adding Valid Time Support to an Existing Table.) If you enable valid time support, each row contains an added column to hold the valid time period associated with the row. You can specify a valid time range for the session, and Workspace Manager will ensure that queries and insert, update, and delete operations correctly reflect and accommodate the valid time range. The valid time range specified can be in the past or the future, or it can include the past, present, and future.

Example 3-1 presents a simple example of valid time support. The example does the following:

1. Creates a table of employees and their salaries.

2. Version-enables the table, specifying valid time support, which causes a column named WM_VALID to be added to the table automatically.

3. Inserts rows into the table. For each row, it specifies the employee name, salary, and valid time period.

4. Sets the valid time range for the session.

5. Updates a row, specifying a new salary and valid time period for an employee.

6. Disables versioning on the table.

Example 3-1:

• Refers to valid time support concepts and techniques that will be explained in other sections of this chapter.

• Assumes that you are familiar with the Workspace Manager concepts and techniques explained in Introduction to Workspace Manager.

• Does not create workspaces or savepoints. (These are shown in Example: Marketing Budget Options and Example: Warehouse Expansion Options in Simplified Examples Using Workspace Manager.)

Example 3-1 Valid Time Support

-- Create a very simple employees table (deliberately oversimplified
-- for purposes of illustration).
CREATE TABLE employees (
  name VARCHAR2(16) PRIMARY KEY,
  salary NUMBER 
);

-- Version-enable the table. Specify TRUE for valid time support.
EXECUTE DBMS_WM.EnableVersioning ('employees', 'VIEW_WO_OVERWRITE', FALSE, TRUE);

INSERT INTO employees VALUES(
  'Adams',
  30000,
  WMSYS.WM_PERIOD(TO_DATE('01-01-1990', 'MM-DD-YYYY'), 
                  TO_DATE('01-01-2005', 'MM-DD-YYYY'))
);
INSERT INTO employees VALUES(
  'Baxter',
  40000,
  WMSYS.WM_PERIOD(TO_DATE('01-01-2000', 'MM-DD-YYYY'), DBMS_WM.UNTIL_CHANGED)
);

INSERT INTO employees VALUES(
  'Coleman',
  50000,
  WMSYS.WM_PERIOD(TO_DATE('01-01-2003', 'MM-DD-YYYY'), 
                  TO_DATE('12-31-9999', 'MM-DD-YYYY'))
);

COMMIT;

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

-- 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';

-- Disable versioning. By default (keepWMValid parameter value of TRUE),
-- the WM_VALID column is kept, with all its data. 
COMMIT;
EXECUTE DBMS_WM.DisableVersioning ('employees');