We have migrated to new Google sites!
Post date: Nov 02, 2012 11:23:18 PM
If the compression type, compression level and block size are not explicitly defined, the default is no compression , and the block size is set to the Server Configuration Parameter block_size.
Precedence of Compression Settings
Column compression settings are carried through from the table level to the partition level to the subpartition level. The settings made at the lowest level have priority.
1.Column compression settings specified for subpartitions override any compression settings settings at the partition, column or table levels.
2.Column compression settings specified for partitions override any compression settings at the column or table levels.
3.Column compression settings specified at the table level override any compression settings for the entire table.
Note: The INHERITS clause is not allowed in a table that contains a storage directive or a column reference storage directive.
The storage directive and column reference storage directive are ignored by a table created using the LIKE clause.
Optimal Location for Column Compression Settings
The best practice is to set the column compression settings at the level where the data resides. Example 5, below, shows a table with a partition depth of 2. RLE_TYPE compression is added to a column at the subpartition level.
Examples of Storage Directives
The following examples show the use of storage directives in CREATE TABLE statements.
Example 1
In this example, column c1 is compressed using zlib and uses the block size defined by the system. Column c2 is compressed with quicklz, and uses a block size of 65536. Column c3 is not compressed and uses the block size defined by the system.
CREATE TABLE T1 (c1 int ENCODING (compresstype=zlib),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 char)
WITH (appendonly=true, orientation=column);
Creating and Managing Tables 88
Greenplum Database Administrator Guide 4.2 – Chapter 9: Defining Database Objects
Example 2
In this example, column c1 is compressed using zlib and uses the block size defined by the system. Column c2 is compressed with quicklz, and uses a block size of 65536. Column c3 is compressed using RLE_TYPE and uses the block size defined by the system.
CREATE TABLE T2 (c1 int ENCODING (compresstype=zlib),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 char,
COLUMN c3 ENCODING (RLE_TYPE)
)
WITH (appendonly=true, orientation=column)
Example 3
In this example, column c1 is compressed using zlib and uses the block size defined by the system. Column c2 is compressed with quicklz, and uses a block size of 65536. Column c3 is compressed using zlib and uses the block size defined by the system. Note that column c3 uses zlib (not RLE_TYPE) in the partitions, because the column storage in the partition clause has precedence over the storage directive in the column definition for the table.
CREATE TABLE T3 (c1 int ENCODING (compresstype=zlib),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 char,
COLUMN c3 ENCODING (compresstype=RLE_TYPE)
)
WITH (appendonly=true, orientation=column)
PARTITION BY RANGE (c3) (START ('1900-01-01'::DATE)
END ('2100-12-31'::DATE),
COLUMN c3 ENCODING (zlib));
Example 4
In this example, a table is created and a storage directive is assigned directly to c1. Column c2 has no storage directive, and therefore inherits the compression type (quicklz) and block size (65536) from the DEFAULT COLUMN ENCODING clause.
The compression type for column c3 (RLE_TYPE) is defined in the COLUMN c3 ENCODING clause, and the block size for column c3 block size (65536) is defined by the DEFAULT COLUMN ENCODING clause.
There is a requirement that column c4 is not compressed. Because there is a default column encoding set that specifies a compression type, the compression type for column c4 must be explicitly set to none to override the default for the compression type. The default blocksize setting of 65536 is applied to column c4, since it was not explicitly overriden..
CREATE TABLE T4 (c1 int ENCODING (compresstype=zlib),
c2 char,
c3 char,
c4 smallint ENCODING (compresstype=none),
DEFAULT COLUMN ENCODING (compresstype=quicklz,
blocksize=65536),
COLUMN c3 ENCODING (compresstype=RLE_TYPE)
)
WITH (appendonly=true, orientation=column);
Example 5
This example creates a partitioned table of depth 2. If a row contains column j set to 1 or 2, the row goes into an RLE_TYPE compressed partition. If a row contains other values for column j, the row goes into a zlib compressed partition. All other columns use quicklz compression.
CREATE TABLE T5 (
i int,
j int,
k date,
DEFAULT COLUMN ENCODING (blocksize=1048576)
)
WITH (appendonly = true, orientation=column)
PARTITION BY RANGE(k)
SUBPARTITION BY LIST(j)
SUBPARTITION TEMPLATE
(PARTITION one_two VALUES(1, 2)
COLUMN j ENCODING (compresstype=RLE_TYPE),
PARTITION rest VALUES(3, 4, 5, 6, 7, 8, 9, …)
COLUMN j ENCODING (compresstype=zlib, compresslevel=9),
DEFAULT COLUMN ENCODING (compresstype=quicklz)
)
(
START (date '2011-01-01') END (date '2011-12-31')
EVERY (interval '1 day')
);