DELETE FROM employee_bitemp FOR PORTION OF job_dur FROM DATE’2009-01-01’ TO DATE’2010-01-01’ WHERE ename=’Fred’; eid ename deptno terms job_start job_end sys_start sys_end ---- ----- ------ ----- ---------- ---------- -------------------------------- --------------------------------1002 Ash 333 TA05 2003/01/01 2003/12/31 2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 222 TW10 2004/12/01 9999/12/31 2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00 1010 Mike 444 TW07 2015/01/01 2016/12/31 2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 222 PW12 2001/05/01 9999/12/31 2001-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
Because the PA of the DELETE statement was within the PV of the affected row, two rows result, just as for a deletion on a valid-time table:
SELECT * FROM employee_bitemp; eid ename deptno terms job_start job_end sys_start sys_end ---- ----- ------ ----- ---------- ---------- -------------------------------- -------------------------------- 1002 Ash 333 TA05 2003/01/01 2003/12/31 2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00 1005 Alice 222 TW10 2004/12/01 9999/12/31 2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00 1010 Mike 444 TW07 2015/01/01 2016/12/31 2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 222 PW12 2001/05/01 2009/01/01 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00 1004 Fred 222 PW12 2010/01/01 9999/12/31 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
In system time these rows are both considered to be new rows added to the table, which is reflected by their sys_start values that show the time that the middle portion of the Fred row valid time was deleted. To see all that occurred in system time to the table as a result of the “partial” deletion, use a temporal query that shows all rows, open and closed:
SELECT * FROM employee_bitemp
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 00:00:00.000000+00:00' AND CURRENT_TIMESTAMP;
eid ename deptno terms job_start job_end sys_start sys_end
---- ----- ------ ----- ---------- ---------- -------------------------------- --------------------------------
1002 Ash 333 TA05 2003/01/01 2003/12/31 2003-12-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice 222 TW10 2004/12/01 9999/12/31 2004-12-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1010 Mike 444 TW07 2015/01/01 2016/12/31 2004-12-01 00:12:23.120000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania 111 TW08 2002/01/01 2006/12/31 2002-01-01 00:00:00.000000-08:00 2002-07-01 12:00:00.350000+00:00
1004 Fred 222 PW12 2001/05/01 9999/12/31 2001-05-01 12:00:00.350000-08:00 2014-02-28 21:06:33.460000-08:00
1004 Fred 222 PW12 2001/05/01 2009/01/01 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred 222 PW12 2010/01/01 9999/12/31 2014-02-28 21:06:33.460000-08:00 9999-12-31 23:59:59.999999+00:00
1003 SRK 111 TM02 2004/02/10 2005/02/10 2004-02-10 00:00:00.000000-08:00 2004-12-01 00:12:23.120000+00:00
Now you can see there are actually three physical Fred rows in the table. The two new rows that were added to account for Fred’s time before and after the deletion, and the original Fred row has been logically deleted from the table, as reflected by the sys_end time that shows the time of the change. The valid-time bounds in the deleted row are those of the original Fred row, before the deletion.