Implementing Analytics with Greenplum UAP - Getting Started with Greenplum for Big Data Analytics - page 122

CREATE TABLE ranking (id int, rank int, gender
char(1), count int) DISTRIBUTED BY (id)
PARTITIONED BY LIST (gender)
(PARTITION women VALUES ('F'),
PARTITION men VALUES ('M')
DEFAULT PARTITION other);
Partition elimination is a process in which irrelevant data is filtered out, thus reducing
table scans. This process can occur either as a part of the query plan or during the
query execution time.
The following is an example of dynamic partition elimination:
• Building partition table using list:
CREATE TABLE performance_quarter (LIKE
another_table)
DISTRIBUTED BY (id)
PARTITION BY LIST (quartered)
(PARTITION first_quarter VALUES(1),
PARTITION second_quarter VALUES(2),
PARTITION third_quarter VALUES(3),
PARTITION fourth_quarter VALUES(4));
• Dynamic partition elimination while querying the fact:
SELECT * FORM performance_quarter,
dimquarter WHERE dimquarter.description
like 'Quarter1%' AND
performance_quarter.id =
dimquarter.quarterid
The primary goal of table partitioning is to eliminate scanning partitions that contain
data that is not needed to satisfy a query. Following are some important guidelines
to follow while defining table partitions:
• Partitioning should be used for very large tables, such as fact tables, to im-
prove query performance. For smaller tables, unless there is a significant