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