15.00 - Comparing Unpartitioned NoPI Tables With Column-Partitioned Tables With and Without Autocompression - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Comparing Unpartitioned NoPI Tables With Column‑Partitioned Tables With and Without Autocompression

This topic describes some of the issues that discriminate among unpartitioned NoPI tables, column‑partitioned tables with autocompression, and column‑partitioned tables without autocompression.

The following CREATE TABLE request 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 unpartitioned 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 unpartitioned NoPI table for album_artist_ord_nopi looks like this.

 

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.

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.

 

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.

 
 
 
 
 

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.

 

The following CREATE TABLE request 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 2 containers and the single multicolumn subrow storage partition look like this.

 

See Database Design for some example comparisons of the number of I/Os required to read the same data from primary‑indexed tables, partitioned tables, unpartitioned 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.