GBase 8c 创建和管理表（二）

操作步骤

创建和管理表操作步骤如下，以使用默认表空间为例。

步骤1 创建分区表（假设用户已创建tpcds schema）

gbase=# CREATE TABLE  tpcds.customer_address (ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_country character varying(20), ca_gmt_offset numeric(5,2), ca_location_type character(20), ca_street_number character(10), ca_street_name character varying(60), ca_street_type character(15), ca_suite_number character(10),ca_city character varying(60), ca_county character varying(30), ca_state character(2), ca_zip character(10), ca_country character varying(20), ca_gmt_offset numeric(5,2), ca_location_type character(20)) PARTITION BY RANGE (ca_address_sk) (PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE)) ENABLE ROW MOVEMENT;

当结果显示为如下信息，则表示创建成功。

CREATE TABLE

创建列存分区表的数量建议不超过1000个。

步骤2 插入数据

将表tpcds.customer_address的数据插入到表tpcds.web_returns_p2中。 例如在数据库中创建了一个表tpcds.customer_address的备份表tpcds.web_returns_p2，现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中，则可以执行如下命令。

gbase=# CREATE TABLE tpcds.web_returns_p2 (

ca_address_sk integer NOT NULL, ca_address_id character(16), ca_street_number character(10) , ca_street_name character varying(60), ca_street_type character(15), ca_suite_number character(10), ca_city character varying(60), ca_county character varying(30), ca_state character(2), ca_zip character(10), ca_country character varying(20), ca_gmt_offset numeric(5,2), ca_location_type character(20))

PARTITION BY RANGE (ca_address_sk) (

PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE)

)

ENABLE ROW MOVEMENT;

CREATE TABLE

gbase=# INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address;

INSERT 0 0

步骤3 修改分区表行迁移属性

gbase=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;

ALTER TABLE

步骤4 删除分区

删除分区P8。

gbase=# ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;

ALTER TABLE

步骤5 增加分区

增加分区P8，范围为 40000<= P8<=MAXVALUE。

gbase=# ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN

(MAXVALUE);

ALTER TABLE

步骤6 重命名分区

重命名分区P8为P_9。

gbase=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;

ALTER TABLE

重命名分区P_9为P8。

gbase=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;

ALTER TABLE

步骤7 查询分区

查询分区P6。

gbase=# SELECT * FROM tpcds.web_returns_p2 PARTITION (P6);

gbase=# SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888);

步骤8 删除分区表和表空间

gbase=# DROP TABLE tpcds.customer_address; DROP TABLE

gbase=# DROP TABLE tpcds.web_returns_p2; DROP TABLE