A normalize condition for normalize_column.
To coalesce 2 values from a Period column or derived Period column that meet or overlap. The result of a normalization operation on 2 input values is a third Period value that is the union of the 2 input Period values.
You can only specify the NORMALIZE option once for a table.
A normalized table does not have 2 rows with the same data values whose normalize column values meet or overlap. Because of this, if you insert a new row or update an existing row in the table, it is coalesced with all existing rows whose data values are the same as the new row and whose normalize column values overlap or meet.
You cannot specify the NORMALIZE option for a volatile table.
The NORMALIZE option specifies a mandatory single normalize column and normalization condition with an optional ALL BUT column list to exclude the specified columns from the normalization process.
- ALL BUT (normalize_ignore_column_name)
- Exclude the specified column set from the normalization process.
- You cannot normalize a table defined with columns that have a BLOB, CLOB, JSON, or XML data type unless you explicitly specify those columns as members of the normalize_ignore_column_name column set.
- ON normalize_column
- The Period or derived Period column on which the table is to be normalized.
- This cannot be a TRANSACTIONTIME column, but can be a VALIDTIME or other period column.
- ON OVERLAPS
- Normalize the table based on when the 2 input Period or derived Period columns overlap in time.
- ON MEETS OR OVERLAPS
- ON OVERLAPS OR MEETS
- Normalize the table based on when the 2 input Period or derived Period columns either overlap in time or are temporally continuous, but do not overlap.
- You cannot specify ON MEETS as a normalize condition.
This CREATE TABLE request defines a table that normalizes on the duration column. Rows are normalized only when values of the columns emp_id , project_name , and dept_id are the same and the Period values for duration meet or overlap.
CREATE TABLE project ( emp_id INTEGER, project_name VARCHAR(20), dept_id INTEGER, duration PERIOD(DATE), NORMALIZE ON duration);
The following CREATE TABLE request defines a similar project table that also normalizes on the duration column, but specifies dept_id as a column to ignore for normalization. For such a table, a row is normalized only when the values of the columns emp_id and project_name are the same and Period values for duration meet or overlap.
CREATE TABLE project ( emp_id INTEGER, project_name VARCHAR(20), dept_id INTEGER, duration PERIOD(DATE), NORMALIZE ALL BUT(dept_id) ON duration);