For the following examples, assume the queries are issued against the following system-versioned system-time table named employee_systime that contains a mix of open and closed rows:
eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00 1004 Fred 222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1003 SRK 111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
In a table that has system time, only the open rows are subject to UPDATEs. A simple SELECT with no temporal qualifiers returns the open table rows:
SELECT * FROM employee_systime; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00 UPDATE employee_systime SET deptno=888 WHERE ename=’Ash’;
A simple SELECT shows the row has been changed as expected. Note that the start time for the system-time period records the time of the UPDATE, because it is from this time that the modification is known to the database and effective:
eid ename deptno sys_start sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash 888 2014-02-23 22:27:56.540000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania 111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred 555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice 555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
A temporal select that shows open and closed rows demonstrates that the original row still exists in the table. It has been closed, and the end boundary of the old row system time has been set to the time of the modification, which is the time when the values in that row ceased to apply or be active in the database.
SELECT * FROM employee_systime FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 22:14:02.820000-08:00' and CURRENT_TIMESTAMP WHERE ename=’Ash’; eid ename deptno sys_start sys_end ---- ------ ------ -------------------------------- -------------------------------- 1002 Ash 888 2014-02-23 22:27:56.540000-08:00 9999-12-31 23:59:59.999999+00:00 1002 Ash 333 2003-07-01 12:11:00.000000-08:00 2014-02-23 22:27:56.540000-08:00
In this way, a system-time table maintains a complete history of changes to the rows in the table.