normalize_option - Teradata Vantage - Analytics Database

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 two 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.

To normalize a table defined with columns that have a BLOB, CLOB, XML, Geospatial, JSON, or DATASET data type, you must 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 two input Period or derived Period columns overlap in time.

ON MEETS OR OVERLAPS

ON OVERLAPS OR MEETS

Normalize the table based on when the two 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.

Example: NORMALIZE

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