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

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
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.

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 Teradata Database 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, Teradata Database compresses the container data to a final size of 53 bytes by combining several different autocompression methods, a reduction of 99.4%.



You might 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.

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 Harsh 06-25-1980 1,850,000 Felix Klopotek
Albert Ayler Bells 06-25-1980 975,000 Bernard Stollman
Derek Bailey Lot ‘74 07-04-1980 1,000,000 Derek Bailey
Pierre Boulez Pli Selon Pli 07-04-1980 3,250,000 Paul Myers
artist   album   release_date   sold   producer
Part 101-HB-Row #1 Part 201-HB-Row#1 Part 301-HB-Row #1 Part 401-HB-Row #1 Part 501-HB-Row #1
1s and 0s 1s and 0s 1s and 0s 1s and 0s 1s and 0s
Keith Rowe Duos for Doris 04-14-1992 2,500,000 Jon Abbey
Bix Beiderbecke Singin’ the Blue 04-14-1992 3,125,000 Tommy Rockwell
Albert Ayler Spiritual Unity 11-28-1992 2,375,000 Bernard Stollman
Karlheinz Stockhausen Sternklang 11-28-1992 750,000 Dr. Rudolf Werner
artist   album   release_date   sold   producer
Part 601-HB-Row #1 Part 201-HB-Row#1 Part 301-HB-Row #1 Part 401-HB-Row #1 Part 501-HB-Row #1
1s and 0s 1s and 0s 1s and 0s 1s and 0s 1s and 0s
Albert Ayler Spirits Rejoice 01-28-2000 3,100,000 Bernard Stollman

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

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

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.

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

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.