Notes on effect of ALTER TABLE ADD COLUMN in a PureData System for Analytics

Answer

This document explains what happens under the covers when a column is added to a table using ALTER TABLE ADD COLUMN. Note that the same behavior applies when doing an ALTER TABLE DROP COLUMN.

Let's start with a new database containing one table:


[nz@nz30052-h2 ~]$ nzsql
Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type: \h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

SYSTEM.ADMIN(ADMIN)=> create database training;
CREATE DATABASE

SYSTEM.ADMIN(ADMIN)=> \c training
You are now connected to database training.

TRAINING.ADMIN(ADMIN)=> create table t1(col1 int);
CREATE TABLE

TRAINING.ADMIN(ADMIN)=> \d t1
Table "T1"
Attribute | Type | Modifier | Default Value
-----------+---------+----------+---------------
COL1 | INTEGER | |
Distributed on hash: "COL1"

Now, list all the objects in this database by selecting from _v_objects:

TRAINING.ADMIN(ADMIN)=> select * from _v_objects;
OBJID | OBJNAME | OWNER | CREATEDATE | OBJTYPE | OBJCLASS | DESCRIPTION | SCHEMA | SCHEMAID
--------+---------+-------+---------------------+---------+----------+-------------+--------+----------
202221 | T1 | ADMIN | 2014-10-29 13:26:47 | TABLE | 4905 | | ADMIN | 202219
(1 row)

Now add a column to the table:

TRAINING.ADMIN(ADMIN)=> alter table t1 add column col2 char(10);
ALTER TABLE

TRAINING.ADMIN(ADMIN)=> \d t1
Table "T1"
Attribute | Type | Modifier | Default Value
-----------+---------------+----------+---------------
COL1 | INTEGER | |
COL2 | CHARACTER(10) | |
Distributed on hash: "COL1"
Versions: 2

Notice that there is an additional line of output from \d t1. It says "Versions: 2"

List all the objects in this database again by selecting from _v_objects:

TRAINING.ADMIN(ADMIN)=> select * from _v_objects;
OBJID | OBJNAME | OWNER | CREATEDATE | OBJTYPE | OBJCLASS | DESCRIPTION | SCHEMA | SCHEMAID
--------+--------------+-------+---------------------+----------------+----------+-------------+--------+----------
202261 | T1 | ADMIN | 2014-10-29 13:27:24 | TABLE | 4961 | | ADMIN | 202219
202235 | _TV_202223_2 | ADMIN | 2014-10-29 13:26:47 | TABLE VERSION | 4963 | | ADMIN | 202219
202223 | _BTS_202223 | ADMIN | 2014-10-29 13:26:47 | BASE TABLE SET | 4962 | | ADMIN | 202219
202221 | _TV_202223_1 | ADMIN | 2014-10-29 13:26:47 | TABLE VERSION | 4963 | | ADMIN | 202219
(4 rows)

Things now look a bit more complex. The original table has been replaced with a "table" plus a base table set and two table versions. This was done so that it was not necessary to perform a full CREATE TABLE AS SELECT to make a complete copy of the table including the new column.

The original table has now been replaced by something that functions more like a view. It joins the original copy of the table containing just COL1 INT (now the _TV_202223_1 table version) to the COL2 CHAR(10) (_TV_202223_2 table version).

You can get a listing of all tables containing multiple versions on your appliance using nz_altered_tables. Here is an example:

[nz@nz30052-h2 ~]$ nz_altered_tables

# Of Versioned Tables 1
Total # Of Versions 2

Database | Schema | Table Name | Size (Bytes) | # Of Versions
----------+--------+------------+--------------------------+---------------
TRAINING | ADMIN | T1 | 0 | 2
(1 row)

Multi-versioned tables can have performance implications since the system has to join the table versions together to reassemble the full table.

The multiple versions of the table can be reintegrated into a single table with the GROOM command. Here is an example:

TRAINING.ADMIN(ADMIN)=> groom table t1 versions;
NOTICE: Groom will not purge records deleted by transactions that started after 2014-10-29 13:30:31.
NOTICE: If this process is interrupted please either repeat GROOM VERSIONS or issue 'GENERATE STATISTICS ON "T1"'
NOTICE: Groom processed 0 pages; purged 0 records; scan size unchanged; table size unchanged.
GROOM VERSIONS

Note that if the table being groomed contains a lot of data, this can take a long time. It is essentially doing something similar to a CREATE TABLE AS SELECT to reassemble everything.

Here is what _v_objects looks like after the groom:

TRAINING.ADMIN(ADMIN)=> select * from _v_objects;
OBJID | OBJNAME | OWNER | CREATEDATE | OBJTYPE | OBJCLASS | DESCRIPTION | SCHEMA | SCHEMAID
--------+---------+-------+---------------------+---------+----------+-------------+--------+----------
202235 | T1 | ADMIN | 2014-10-29 13:26:47 | TABLE | 4905 | | ADMIN | 202219
(1 row)


It is recommended to GROOM VERSIONS after adding or dropping a table column, or at least on a regular basis, to prevent performance issues.