Comparing Nonpartitioned NoPI Tables with Column-Partitioned Tables with and without Autocompression - Analytics Database - Teradata Vantage

This topic compares nonpartitioned NoPI tables and column-partitioned tables with and without autocompression.

The following CREATE TABLE statement constructs the first version of the table that is examined in this topic, album_artist.

The first version of test table album_artist is created as the nonpartitioned NoPI table album_artist_ord_nopi.

CREATE TABLE album_artist_ord_nopi (
  artist       CHARACTER(25) NOT NULL,
  album        CHARACTER(50) NOT NULL,
  release_date DATE          NOT NULL,
  sold         INTEGER       NOT NULL,
  producer     CHARACTER(40))
NO PRIMARY INDEX;

The populated nonpartitioned NoPI table for album_artist_ord_nopi looks like this.

where:

The second version of test table album_artist is created as column-partitioned table album_artist_cp_nopi_no_ac without autocompression.

CREATE TABLE album_artist_cp_nopi_no_ac (
  artist       CHARACTER(25) NOT NULL NO AUTO COMPRESS,
  album        CHARACTER(50) NOT NULL NO AUTO COMPRESS,
  release_date DATE NOT NULL          NO AUTO COMPRESS,
  sold         INTEGER       NOT NULL NO AUTO COMPRESS,
  producer     CHARACTER(40)          NO AUTO COMPRESS)
NO PRIMARY INDEX
PARTITION BY COLUMN;

The column containers for the populated column-partitioned table for album_artist_cp_nopi_no_ac with no autocompression looks like this.

where:

You might query this table to ask what the albums by Keith Rowe in the collection are.

SELECT album
FROM album_artist_cp_nopi_no_ac
WHERE artist = ‘Keith Rowe’;

The third version of test table album_artist is created as the column-partitioned table album_artist_cp_nopi_ac with autocompression.

CREATE TABLE album_artist_cp_nopi_ac (
  artist       CHARACTER(25) NOT NULL,
  album        CHARACTER(50) NOT NULL,
  release_date DATE          NOT NULL,
  sold         INTEGER       NOT NULL,
  producer     CHARACTER(40))
NO PRIMARY INDEX
PARTITION BY COLUMN;

The column containers for the populated column-partitioned table for album_artist_cp_nopi_ac with autocompression look like this.

The caption at the bottom of each container states the type of autocompression that Vantage chose to apply to it.

The following graphic indicates the extent of the space savings that can be realized by the autocompression of column-partitioned table containers. Beginning with 29 rows of 30 characters each (a total of 870 bytes) from entries in the Artist container that are not displayed elsewhere in this example set, Vantage compresses the container data to a final size of 53 bytes by combining several different autocompression methods, a reduction of 99.4%.

You can query this table container set to ask what the albums by artist Albert Ayler are.

SELECT album
FROM album_artist_cp_nopi_ac
WHERE artist = ‘Albert Ayler’;

Because the entries in the artist container are compressed using run-length encoding, there is only 1 entry for artist Albert Ayler, but that entry points to the 3 different albums that are associated with Albert Ayler, as the following graphic indicates.

The fourth version of album_artist, album_artist_mlpcp_nopi_ac, table uses multilevel partitioning with default autocompression.

CREATE TABLE album_artist_mlpcp_nopi_ac (
  artist       CHARACTER(25) NOT NULL,
  album        CHARACTER(50) NOT NULL,
  release_date DATE          NOT NULL,
  sold         INTEGER       NOT NULL,
  producer     CHARACTER(40))
NO PRIMARY INDEX
PARTITION BY (COLUMN,
              RANGE_N(release_date BETWEEN DATE '1980-01-01'
                                   AND DATE '2000-01-28'
                                   EACH INTERVAL '1' YEAR));

The containers for the populated column-partitioned table for album_artist_mlpcp_nopi_ac with autocompression look like this.

where:

You might query this table container set to ask which artists released albums in 1992 and what the titles of those albums were.

SELECT artist, album, EXTRACT(YEAR FROM release_date) AS year
FROM album_artist_mlpcp__nopi_ac
WHERE year = 1992;

The fifth version of album_artist, album_artist_mccp_nopi_noac, creates a multicolumn container without autocompression. The multicolumn container is defined on the release_date, sold, and producer columns.

CREATE TABLE album_artist_mccp_nopi_noac (
  artist CHARACTER(25) NOT NULL,
  album CHARACTER(50)  NOT NULL,
 (release_date DATE    NOT NULL,
  sold INTEGER         NOT NULL,
  producer CHARACTER(40)))
NO PRIMARY INDEX
PARTITION BY COLUMN NO AUTO COMPRESS;

The three containers for this table look like this.

The following CREATE TABLE statement creates a sixth version of the album_artist table, album_artist_hybrid_row_col_noac, with hybrid ROW and COLUMN storage without autocompression. There is an individual COLUMN partition for the artist and album columns and a grouped ROW partition on the columns release_date, sold, producer, and lyric.

CREATE TABLE album_artist_hybrid_row_col_noac (
  artist            CHARACTER(35) NOT NULL NO AUTOCOMPRESS,
  album             CHARACTER(50) NOT NULL NO AUTOCOMPRESS,
  ROW (release_date DATE          NOT NULL NO AUTOCOMPRESS,
       sold         INTEGER       NOT NULL NO AUTOCOMPRESS,
       producer     CHARACTER (40)         NO AUTO COMPRESS,
       lyric        LONG VARCHAR))
NO PRIMARY INDEX
PARTITION BY COLUMN;

The two containers and the single multicolumn subrow storage partition look like this.

See Teradata Vantage™ - Database Design, B035-1094 for some example comparisons of the number of I/Os required to read the same data from primary-indexed tables, partitioned tables, nonpartitioned NoPI tables, and column-partitioned tables that are partitioned in different ways and for a number of other performance issues related to column-partitioned tables and join indexes.