15.10 - ALTER TABLE (ANSI Bitemporal Table Form) - Teradata Database

Teradata Database ANSI Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
15.10
category
Programming Reference
featnum
B035-1186-015K

Purpose  

Temporal syntax for ALTER TABLE allows you to create ANSI bitemporal tables from existing nontemporal tables, and combine the ALTER TABLE syntaxes for system-time and valid-time tables.

Adding valid time to the table involves these ALTER TABLE operations:

  • Adding or altering the two DateTime columns that will serve as the beginning and ending bounds of the valid-time period
  • Adding a valid-time derived period column to the table, and designating the derived column VALIDTIME
  • Adding system time to the table involves these ALTER TABLE operations:

  • Adding a SYSTEM_TIME derived period column to the table by specifying the columns that will serve as the beginning and ending bounds of the system-time period.
  • Adding or altering the columns that will serve as the beginning bound and ending bound of the system-time period. If these columns already exist in the table, special attributes must be added to them.
  • Adding system versioning to the table
  • Syntax  

    There is no special syntax for altering tables to bitemporal tables. Use the combined special syntax that is discussed in “ALTER TABLE (ANSI System-Time Table Form)” on page 31 and “ALTER TABLE (ANSI Valid-Time Table Form)” on page 60.

    Note: The table must be altered to add a valid-time dimension prior to adding the system-time dimension, because ALTER TABLE operations on system-time tables are severely restricted.

    Example : ALTER TABLE to Convert a Nontemporal Table to an ANSI Bitemporal Table

    The following SQL creates a regular nontemporal table, and inserts some rows:

    CREATE MULTISET TABLE employee_bitemp (
     eid INTEGER NOT NULL,
     ename VARCHAR(5),
     deptno INTEGER NOT NULL,
     terms VARCHAR(5),
     job_start DATE NOT NULL,
     job_end DATE NOT NULL,
     sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL,
     sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL
     )
     PRIMARY INDEX (eid);
    INSERT INTO employee_bitemp VALUES 
     (1001,'Sania',111,'TW08',DATE'2002-01-01',DATE'2006-12-31',   TIMESTAMP'2002-01-01 00:00:00.000000-08:00',
     TIMESTAMP'2002-07-01 12:00:00.350000+00:00');
    INSERT INTO employee_bitemp VALUES 
     (1004,'Fred',222,'PW12', DATE'2001-05-01',DATE'9999-12-31',   TIMESTAMP'2001-05-01 12:00:00.350000-08:00',
      TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

    INSERT INTO employee_bitemp VALUES
     (1002,'Ash',333,'TA05',DATE'2003-01-01',DATE'2003-12-31',   TIMESTAMP'2003-01-01 12:11:00.000000-08:00',
      TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

    INSERT INTO employee_bitemp VALUES
     (1003,'SRK',111,'TM02',DATE'2004-02-10',DATE'2005-02-10',   TIMESTAMP'2004-02-10 00:00:00.000000-08:00',
      TIMESTAMP'2004-12-01 00:12:23.120000+00:00');

    INSERT INTO employee_bitemp VALUES
     (1005,'Alice',222,'TW10',DATE'2004-12-01',DATE'9999-12-31',   TIMESTAMP'2004-12-01 12:00:00.450000-08:00',
      TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

    INSERT INTO employee_bitemp VALUES
     (1010,'Mike',444,'TW07',DATE'2015-01-01',DATE'2016-12-31',   TIMESTAMP'2004-12-01 00:12:23.120000-08:00',
      TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

    After rows have been inserted, an unqualified SELECT on the table returns all rows:

    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-01-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  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

    The following ALTER TABLE statement adds valid time to the table:

    ALTER TABLE employee_bitemp 
     ADD PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME;

    A simple select from the table still returns all rows, as it would from any 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-01-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  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

    The following ALTER TABLE statements add system time to the table:

    ALTER TABLE employee_bitemp 
     ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end) 
     ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
                   GENERATED ALWAYS AS ROW START
     ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
                   GENERATED ALWAYS AS ROW END;
     
    ALTER TABLE employee_bitemp
     ADD SYSTEM VERSIONING;

    Because the table now includes a system-time dimension, rows that had dates for sys_end that were not 9999-12-31 23:59:59.999999+0:0 are considered closed rows, logically deleted from the database, so now a simple select shows only four rows:

    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-01-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