Database Reference
In-Depth Information
• For example, setting gp_segments_for_planner = 100000 tells the
planner that there are 100,000 segments.
Partitioning
Table partitioning is used to logically divide large tables to improve performance and
facilitate data warehouse maintenance tasks. The primary goal of table partitioning
is to eliminate scanning partitions that contain data that is not needed to satisfy a
query. Consider table partitioning on large tables that can be divided into somewhat
equal parts based on a defining criterion and the defining criteria is used in query
predicates ( WHERE clause). Following are the important features of table partitioning:
• Addresses the problem of supporting very large tables (such as fact tables)
by dividing them into smaller and more manageable pieces
• Improves query performance by scanning only the relevant data
• Supports easier data roll out for archiving
• Works with table inheritance and constraints
• Does not affect the physical distribution of the table data
Partitioning can be range or list based. We can define a date range, numeric range,
or a list. The following is an example of range partitioning:
CREATE TABLE sales (id int, date date, amt
decimal(10,2)) DISTRIBUTED BY (id)
PARTITIONED BY RANGE (date)
(PARTITION Jan13 START (date '2013-01-01')
INCLUSIVE,
PARTITION Feb13 START (date '2013-02-01')
INCLUSIVE,
PARTITION Mar13 START (date '2013-03-01')
INCLUSIVE,
PARTITION Dec13 START (date '2013-12-01')
INCLUSIVE
END (date '2014-01-01') EXCLUSIVE );
The following is an example of list partitioning:
Search WWH ::




Custom Search