17.10 - Comparing Nonpartitioned NoPI Tables With Column-Partitioned Tables With and Without Autocompression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.

album_artist_ord_nopi
part HB row # 1s & 0s artist album release_date sold producer
0 n 2 0,1 Keith Rowe Harsh 06-25-1980 1,850,000 Felix Klopotek
0 n 3 1,1 Derek Bailey Lot ‘74 07-04-2010 1,000,000 Derek Bailey
0 n 1 1,1 Albert Ayler Spiritual Unity 11-28-1964 2,375,000 Bernard Stollman
0 n 5 1,1 Albert Ayler Bells 01-28-1965 975,000 Bernard Stollman
0 n 6 1,1 Albert Ayler Spirits Rejoice 06-25-1965 3,100,000 Bernard Stollman
0 n 8 1,0 Pierre Boulez Pli Selon Pli 07-04-1973 3,250,000 Paul Myers
0 n 7 0,1 Keith Rowe Duos for Doris 04-14-2003 2,500,000 Jon Abbey
0 n 9 1,1 Karlheinz Stockhausen Sternklang 11-28-1976 750,000 Dr. Rudolf Werner
0 n 4 1,1 Bix Beiderbecke Singin’ the Blues 04-14-1990 3,125,000 Tommy Rockwell

where:

Row header element … Represents the …
part partition number for the container.
HB hash bucket number for the container.
Row #n row number for the container.
1s & 0s presence bits for the container.

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.


Column containers for populated partitioned table

where:

Column container element … Represents the …
Part n partition number for the container.
HB hash bucket number for the container.
Row #n row number for the container.
1s & 0s presence bits for the container.

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.

artist   album   release_date   sold   producer
Part 1-HB-Row # 1 Part 1-HB-Row # 1 Part 1-HB-Row # 1 Part 1-HB-Row # 1 Part 1-HB-Row # 1
1s and 0s 1s and 0s 1s and 0s 1s and 0s 1s and 0s
Keith Rowe (2) Harsh 06-25-1980 1,850,000 Felix Klopotek
Derek Bailey Lot ‘74 07-04-1980 1,000,000 Derek Bailey
Albert Ayler (3) Spiritual Unity 11-28-1992 2,375,000 Bernard Stollman
Pierre Boulez Bells 06-25-1980  975,000 Bernard Stollman
Karlheinz Stockhausen Spirits Rejoice 01-28-2000 3,100,000 Bernard Stollman
Bix Beiderbecke Pli Selon Pli 07-04-1980 3,250,000 Paul Myers
  Duos for Doris 04-14-1992 2,500,000 Jon Abbey
  Sternklang 11-28-1992 750,000 Dr. Rudolf Werner
  Singin’ the Blues 04-14-1992 3,125,000 Tommy Rockwell
Run-Length Encoding Trim Trailing Spaces Value List Compression No compression Null compression

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