postgres=# select relname, relkind, relstorage from pg_class  where relkind='r';  
            relname            | relkind | relstorage   
-------------------------------+---------+------------  
 sql_languages                 | r       | h  
 sql_packages                  | r       | h  
 sql_parts                     | r       | h  
 sql_sizing                    | r       | h  
 sql_sizing_profiles           | r       | h  
 gp_san_configuration          | r       | h

h表示heap 行存储表。

c表示append only column 存储表。

a表示表示append only 行存储表。

postgres=# select relname, relkind, relstorage from pg_class  where relname='a';  
 relname | relkind | relstorage   
---------+---------+------------  
 a       | r       | c  
(1 row)  
  
Time: 0.518 ms

如何查看分布键

1、查看分布键

SELECT attrnums  
FROM pg_catalog.gp_distribution_policy t  
WHERE localoid = '679926' ;  
  
 attrnums   
----------  
 {1}  
(1 row)

2、查看分布键名称

SELECT attname FROM pg_attribute   
WHERE attrelid = '679926'   
AND attnum = '1' ;  
  
 attname   
---------  
 uid  
(1 row)

如何查看分区

postgres=# select * from pg_partitions where tablename='tbl_pos';

二、单表交换数据

对sales表进行交换。

CREATE TABLE sales2 (LIKE sales)  
WITH (appendonly=true, compresstype=quicklz, compresslevel=1, orientation=column);  
  
INSERT INTO sales2 SELECT * FROM sales;  
  
DROP TABLE sales;  
  
ALTER TABLE sales2 RENAME TO sales;  
  
GRANT ALL PRIVILEGES ON sales TO admin;  
  
GRANT SELECT ON sales TO guest;

三、分区表交换分区

对于分区表，Greenplum提供了交换分区的语法

http://greenplum.org/docs/510/ref_guide/sql_commands/ALTER_TABLE.html

EXCHANGE [DEFAULT] PARTITION  
  
Exchanges another table into the partition hierarchy into the place of an existing partition.   
In a multi-level partition design, you can only exchange the lowest level partitions (those that contain data).  
  
The Greenplum Database server configuration parameter gp_enable_exchange_default_partition controls   
availability of the EXCHANGE DEFAULT PARTITION clause. The default value for the parameter is off.   
The clause is not available and Greenplum Database returns an error if the clause is specified in an ALTER TABLE command.  
  
For information about the parameter, see Server Configuration Parameters.  
  
Warning: Before you exchange the default partition, you must ensure the data in the table to be exchanged,   
the new default partition, is valid for the default partition. For example, the data in the new default   
partition must not contain data that would be valid in other leaf child partitions of the partitioned table.   
Otherwise, queries against the partitioned table with the exchanged default partition that are executed by GPORCA might return incorrect results.  
  
WITH TABLE table_name - The name of the table you are swapping into the partition design.   
You can exchange a table where the table data is stored in the database. For example,   
the table is created with the CREATE TABLE command.  
  
With the EXCHANGE PARTITION clause, you can also exchange a readable external table   
(created with the CREATE EXTERNAL TABLE command) into the partition hierarchy in the place   
of an existing leaf child partition. If you specify a readable external table,   
you must also specify the WITHOUT VALIDATION clause to skip table validation against the   
CHECK constraint of the partition you are exchanging.  
  
Exchanging a leaf child partition with an external table is not supported in these cases:  
  
The partitioned table is created with the SUBPARTITION clause or if a partition has a subpartition.  
  
The partitioned table contains a column with a check constraint or a NOT NULL constraint.  
  
WITH | WITHOUT VALIDATION - Validates that the data in the table matches the CHECK   
constraint of the partition you are exchanging. The default is to validate the data against the CHECK constraint.  
  
Warning: If you specify the WITHOUT VALIDATION clause, you must ensure that the data   
in table that you are exchanging for an existing child leaf partition is valid against   
the CHECK constraints on the partition. Otherwise, queries against the partitioned table might return incorrect results.  
  
SET SUBPARTITION TEMPLATE  
  
Modifies the subpartition template for an existing partition. After a new subpartition   
template is set, all new partitions added will have the new subpartition design   
(existing partitions are not modified).  
  
SPLIT DEFAULT PARTITION  
  
Splits a default partition. Only a range partition can be split, not a list partition.   
In a multi-level partition design, you can only split the lowest level default partitions   
(those that contain data). Splitting a default partition creates a new partition containing   
the values specified and leaves the default partition containing any values that do not match to an existing partition.  
  
AT - For list partitioned tables, specifies a single list value that should be used as the criteria for the split.  
  
START - For range partitioned tables, specifies a starting value for the new partition.  
  
END - For range partitioned tables, specifies an ending value for the new partition.  
  
INTO - Allows you to specify a name for the new partition. When using the INTO clause   
to split a default partition, the second partition name specified should always be that   
of the existing default partition. If you do not know the name of the default partition,   
you can look it up using the pg_partitions view.  
  
SPLIT PARTITION  
  
Splits an existing partition into two partitions. Only a range partition can be split,   
not a list partition. In a multi-level partition design, you can only split the lowest   
level partitions (those that contain data).  
  
AT - Specifies a single value that should be used as the criteria for the split.   
The partition will be divided into two new partitions with the split value specified   
being the starting range for the latter partition.  
  
INTO - Allows you to specify names for the two new partitions created by the split.  
  
partition_name  
  
The given name of a partition.  
  
FOR (RANK(number))  
  
For range partitions, the rank of the partition in the range.  
  
FOR ('value')  
  
Specifies a partition by declaring a value that falls within the partition boundary specification.   
If the value declared with FOR matches to both a partition and one of its subpartitions (for example,   
if the value is a date and the table is partitioned by month and then by day), then FOR will operate   
on the first level where a match is found (for example, the monthly partition).   
If your intent is to operate on a subpartition, you must declare so as follows:   
ALTER TABLE name ALTER PARTITION FOR ('2016-10-01') DROP PARTITION FOR ('2016-10-01');

例子

postgres=# select oid from pg_class where relname='tbl_pos';  
  oid     
--------  
 679926  
(1 row)  
  
postgres=# SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '679926' ORDER BY c.relname;  
        oid          
-------------------  
 tbl_pos_1_prt_p1  
 tbl_pos_1_prt_p10  
 tbl_pos_1_prt_p2  
 tbl_pos_1_prt_p3  
 tbl_pos_1_prt_p4  
 tbl_pos_1_prt_p5  
 tbl_pos_1_prt_p6  
 tbl_pos_1_prt_p7  
 tbl_pos_1_prt_p8  
 tbl_pos_1_prt_p9  
(10 rows)  
  
SELECT attrnums  
FROM pg_catalog.gp_distribution_policy t  
WHERE localoid = '679926' ;  
  
 attrnums   
----------  
 {1}  
(1 row)  
  
SELECT attname FROM pg_attribute   
WHERE attrelid = '679926'   
AND attnum = '1' ;  
  
 attname   
---------  
 uid  
(1 row)  
  
  
postgres=# select * from pg_partitions where partitiontablename='tbl_pos_1_prt_p5';  
-[ RECORD 1 ]------------+--------------------------------------------------------------------------------------------------  
schemaname               | postgres  
tablename                | tbl_pos  
partitionschemaname      | postgres  
partitiontablename       | tbl_pos_1_prt_p5  
partitionname            | p5  
parentpartitiontablename |   
parentpartitionname      |   
partitiontype            | list  
partitionlevel           | 0  
partitionrank            |   
partitionposition        | 5  
partitionlistvalues      | 5::smallint  
partitionrangestart      |   
partitionstartinclusive  |   
partitionrangeend        |   
partitionendinclusive    |   
partitioneveryclause     |   
partitionisdefault       | f  
partitionboundary        | PARTITION p5 VALUES(5) WITH (appendonly=true, orientation=row, compresstype=zlib, checksum=false)  
parenttablespace         | pg_default  
partitiontablespace      | pg_default

查看这个表的膨胀率发现已经膨胀了100%

postgres=# select * from gp_toolkit.__gp_aovisimap_compaction_info('tbl_pos_1_prt_p5'::regclass);   
NOTICE:  gp_appendonly_compaction_threshold = 10  
 content | datafile | compaction_possible | hidden_tupcount | total_tupcount | percent_hidden   
---------+----------+---------------------+-----------------+----------------+----------------  
       2 |        1 | t                   |        20833264 |       20833264 |         100.00  
       1 |        1 | t                   |        20833546 |       20833546 |         100.00  
       6 |        1 | t                   |        20833383 |       20833383 |         100.00  
       7 |        1 | t                   |        20833287 |       20833287 |         100.00  
       0 |        1 | t                   |        20833180 |       20833180 |         100.00  
       5 |        1 | t                   |        20833205 |       20833205 |         100.00  
      10 |        1 | t                   |        20833420 |       20833420 |         100.00  
      12 |        1 | t                   |        20833193 |       20833193 |         100.00  
       4 |        1 | t                   |        20833417 |       20833417 |         100.00  
       9 |        1 | t                   |        20833259 |       20833259 |         100.00  
       8 |        1 | t                   |        20833455 |       20833455 |         100.00  
       3 |        1 | t                   |        20833441 |       20833441 |         100.00  
      17 |        1 | t                   |        20833182 |       20833182 |         100.00  
      13 |        1 | t                   |        20833436 |       20833436 |         100.00  
      15 |        1 | t                   |        20833418 |       20833418 |         100.00  
      14 |        1 | t                   |        20833197 |       20833197 |         100.00  
      11 |        1 | t                   |        20833306 |       20833306 |         100.00  
      16 |        1 | t                   |        20833304 |       20833304 |         100.00  
      19 |        1 | t                   |        20833285 |       20833285 |         100.00  
      26 |        1 | t                   |        20833215 |       20833215 |         100.00  
      25 |        1 | t                   |        20833440 |       20833440 |         100.00  
      24 |        1 | t                   |        20833193 |       20833193 |         100.00  
      28 |        1 | t                   |        20833394 |       20833394 |         100.00  
      30 |        1 | t                   |        20833336 |       20833336 |         100.00  
      32 |        1 | t                   |        20833516 |       20833516 |         100.00  
      31 |        1 | t                   |        20833313 |       20833313 |         100.00  
      35 |        1 | t                   |        20833274 |       20833274 |         100.00  
      39 |        1 | t                   |        20833460 |       20833460 |         100.00  
      47 |        1 | t                   |        20833269 |       20833269 |         100.00  
      34 |        1 | t                   |        20833406 |       20833406 |         100.00  
      23 |        1 | t                   |        20833253 |       20833253 |         100.00  
      45 |        1 | t                   |        20833305 |       20833305 |         100.00  
      42 |        1 | t                   |        20833365 |       20833365 |         100.00  
      36 |        1 | t                   |        20833176 |       20833176 |         100.00  
      41 |        1 | t                   |        20833301 |       20833301 |         100.00  
      20 |        1 | t                   |        20833407 |       20833407 |         100.00  
      22 |        1 | t                   |        20833333 |       20833333 |         100.00  
      29 |        1 | t                   |        20833259 |       20833259 |         100.00  
      37 |        1 | t                   |        20833547 |       20833547 |         100.00  
      38 |        1 | t                   |        20833285 |       20833285 |         100.00  
      44 |        1 | t                   |        20833413 |       20833413 |         100.00  
      40 |        1 | t                   |        20833352 |       20833352 |         100.00  
      27 |        1 | t                   |        20833464 |       20833464 |         100.00  
      33 |        1 | t                   |        20833272 |       20833272 |         100.00  
      43 |        1 | t                   |        20833230 |       20833230 |         100.00  
      18 |        1 | t                   |        20833330 |       20833330 |         100.00  
      46 |        1 | t                   |        20833467 |       20833467 |         100.00  
      21 |        1 | t                   |        20833247 |       20833247 |         100.00  
(48 rows)

主备回收空间，新建表，写入数据，交换分区

postgres=# create table tbl_pos_1_prt_p5_exchange(like tbl_pos_1_prt_p5) with (appendonly=true, orientation=row, compresstype=zlib, checksum=false) distributed by (uid);  
CREATE TABLE  
  
  
postgres=# begin;  
BEGIN  
  
postgres=# lock table tbl_pos_1_prt_p5 in ACCESS EXCLUSIVE mode;  
LOCK TABLE  
  
postgres=# insert into tbl_pos_1_prt_p5_exchange select * from tbl_pos_1_prt_p5;  
  
  
-- 注意只调用一次，多次调用会被多次交换(交互偶数次就等于没有改变了)。  
postgres=#  alter table tbl_pos alter partition p5 EXCHANGE PARTITION p5 WITH TABLE tbl_pos_1_prt_p5_exchange with VALIDATION;  
NOTICE:  exchanged partition "p5" of partition "p5" of relation "tbl_pos" with relation "tbl_pos_1_prt_p5_exchange"  
ALTER TABLE  
  
postgres=# drop table tbl_pos_1_prt_p5_exchange;  
DROP TABLE  
  
postgres=# end;