If a request requires multiple columns to return the requested result set, the Optimizer query plan includes assembling projected column values from selected table rows together to form the result rows. This can be combined with row partition elimination to further reduce the data that must be accessed to satisfy a query.
Column partitioning can use system-applied compression techniques to reduce the storage requirements for a table. These compression methodologies can reduce the I/O required to process queries and, when combined with row partitioning, the number of compression opportunities may increase.
The following CREATE TABLE request creates a table definition with four column partitions, two of which are internal partitions that Analytics Database creates for all column-partitioned tables.
The theoretical maximum number of column partitions for this table is 65,534, including the two column partitions for internal use, and the maximum column partition number is 65,535. The difference is caused by there being at least one unused column partition number available for altering a column partition.
However, this table can have no more than 2,050 column partitions because the total number of columns for a table cannot exceed 2,048. To have 2,050 column partitions, each user-defined column partition can have only one column.
CREATE TABLE sales_1 ( (storeid INTEGER NOT NULL, /* Partition 1 */ productid INTEGER NOT NULL, salesdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, totalrevenue DECIMAL(13,2)), (totalsold INTEGER, /* Partition 2 */ topsalesperson INTEGER, note VARCHAR(356))) PRIMARY AMP INDEX (storeid, productid) PARTITION BY COLUMN; -- Defines 2 multicolumn partitions plus 2 -- additional column partitions for internal -- use.
The following example creates a table definition for a column-partitioned table with row partitioning.
The COLUMN specification in the PARTITION BY clause defines 7 column partitions: 5 column partitions based on the column grouping in the column definition list plus 2 additional column partitions for internal use.
The maximum number of column partitions is 779, including the 2 column partitions for internal use. Therefore, you can add 770 column partitions to this table. The maximum column partition number is 780.
The RANGE_N function in the partitioning expression defines 48 row partitions with the option to add up to 36 row partitions for a maximum of 84 row partitions. The maximum row partition number is 84.
The maximum combined partition number for this table is 65,520, which is computed from the following product: 780*84. This is the product of the maximum partition number of each partitioning level. The table has 2-byte partitioning because the number of combined partitions is not greater than 65,535.
If you create this table using multicolumn partitions, you can add columns up to the maximum of 2,048. You can also specify the ADD option to specify a higher number of column partitions that can be added, but then the table uses 8-byte partitioning rather than 2-byte partitioning.
CREATE TABLE sales_2 (
storeid INTEGER NOT NULL,
productid INTEGER NOT NULL,
salesdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
totalrevenue DECIMAL(13,2),
(totalsold INTEGER,
topsalesperson INTEGER,
note VARCHAR(256)))
PRIMARY AMP INDEX (storeid, productid)
PARTITION BY (COLUMN, -- Defines 4 single-column partitions and
-- 1 multicolumn partition plus 2
-- additional column partitions for internal
-- use.
RANGE_N(salesdate BETWEEN DATE '2007-01-01'
AND DATE '2010-12-31'
EACH INTERVAL '1' MONTH)
ADD 36);
You can use column partitioning to improve query performance through column partition elimination.
You can use row partitioning to improve query performance through row partition elimination, which reduces the need to access all of the rows in a table. Analytics Database uses efficient methods to reconstruct qualifying rows from the projected columns of the column partitions that are not eliminated.
Advantages of column partitioning include a simple CREATE TABLE syntax with default autocompression, the ability of the Optimizer to perform column partition elimination, and to facilitate efficient access data from column partitions. Analytics Database manages the column partitions so that the table appears to you as a single object.