Greenplum-the most fully partitioned table operation

I. the principle of Greenplum zoning

Partition table means that a large table is physically divided into several chunks. The partition table in GPDB is implemented in the same principle as in PostgreSQL, which is implemented by table inheritance and constraints. But it is also different from PostgreSQL, in PostgreSQL, a parent table, multiple child tables to implement a partitioned table, you need to manually insert data into the child table. If you insert data into the parent table, it will be inserted directly into the parent table. In GPDB, you can insert data directly into the parent table, and you can automatically insert data into the corresponding child table according to constraints. When the partitioned child table does not exist, the insertion fails.

2.1Partition Table creation, range partitioning (range)

Partition is based on the value range of the partition field, and each partition is a child table.

Eg:create table test_partition_range (id int, name varchar (64), fdate varchar (64) distributed by (id) partition by range (fdate) (partition p1 start ('2017-01-01') inclusive end ('2017-01-31') exclusive, partition p2 start ('2017-02-01') inclusive end ('2017-02-29') exclusive, default partition default_p) Inclusive: specify to include, for example, the above start ('2017-01-01') inclusive contains' 2017-01-01 'exclusive: specify no, for example, the above end (' 2017-01-31') exclusive does not contain '2017-01-31', fast partition (every)

Quickly partition each child table across the cardinality according to the selected range

Eg:create table test_partition_every_1 (id int, name varchar (64), fdate date) distributed by (id) partition by range (fdate) (partition pn_ start ('2017-01-01'::date) end (' 2017-12-31'::date) every ('1 day'::interval), default partition default_p); every: specify spanning cardinality 2.3, list partition (list)

According to the grouping of values, the same data is grouped into a group, that is, in a partition.

Eg:create table test_partition_list (id int, name varchar (64), fdate varchar (10)) distributed by (id) partition by list (fdate) (partition p1 values ('2017-01-01-01,' 2017-01-02'), partition p2 values ('2017-01-03'), default partition pd)

Cut normal zones:

Divide the partition p2 into two alter table test_partition_range split partition p2 at ('2017-02-20') into (partition p2, partition p3) around '2017-02-20'; cut the default partition: alter table test_partition_range split default partition start ('2017-03-01') end ('2017-03-31') into (partition p4, default partition); 3.2. partition add

If default partition exists, you cannot add partitions, you can only split default partition

Alter table test_partition_range_1 add partition p2 start ('2017-02-01') end ('2017-02-31')

Completely delete the corresponding partition table

Alter table test_partition_range_1 DROP partition p23.4, Subarea truncate

Emptying the partition table data is equivalent to deleting the partition and then creating a new one

Alter table test_partition_range_1 truncate partition p1; IV. Sub-partition creation and operation 4.1, sub-partition creation

In GPDB, partitions can be nested and added, and there can be subpartitions under the partition

Create table test_partition_range_2 (id int, name varchar (64), fdate varchar (10) distributed by (id) partition by range (fdate) subpartition by list (name) subpartition template (subpartition C1 values ('xiaoxiao'), subpartition c2 values (' xiaohua')) (partition p1 start ('2017-01-01') end ('2017-01-31')) P1 will be divided into two C1 / c2 subpartitions 4.2 and truncate subpartitions alter table test_partition_range_2 alter partition p1 truncate partition c2 Drop subpartition alter table test_partition_range_2 alter partition p1 drop partition c2