创建和卸载IMCV

普通表创建IMCV

语法

HTAP可以通过ALTER方式对普通表创建IMCV，如表1所示。

详情请参见《参考》中“SQL参考 > SQL语法 > A > ALTER TABLE”章节。

表1 ALTER方式创建IMCV语法和相关影响
语法	加载列
ALTER TABLE ... COLVIEW	全部列。
ALTER TABLE ... NOCOLVIEW	无，IMCV信息被删除。
ALTER TABLE ... NOCOLVIEW COLVIEW(col_list)	col_list中全部列。
ALTER TABLE ... COLVIEW NOCOLVIEW(col_list)	不在col_list中的其他列。

HTAP可以通过CREATE方式对普通表创建IMCV，创建普通表时可以指定表级IMCV或者列级IMCV。

详情请参见《参考》中“SQL参考 > SQL语法 > C > CREATE TABLE”章节。

表级IMCV和列级IMCV互相影响决定最终加载哪些IMCV列。表级和列级IMCV关键字的影响关系如表2所示。

表2 表级和列级IMCV关键字影响关系
表级IMCV关键字	列级IMCV关键字	最终加载列
COLVIEW	无/COLVIEW	全部列。
COLVIEW	无/COLVIEW/NOCOLVIEW	除定义NOCOLVIEW外的其他列。
NOCOLVIEW/无	无/COLVIEW/NOCOLVIEW	定义了COLVIEW的列。

示例

通过ALTER方式创建IMCV。

--创建普通表htap_test并插入数据。
htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)) with (storage_type = ustore) distribute by hash(id);
CREATE TABLE

htapdb=# INSERT INTO htap_test VALUES (generate_series(1,100), 1, 8000, 'Allen', 'For test');
INSERT 0 100

--建表插入数据后执行ALTER COLVIEW创建IMCV。
htapdb=# ALTER TABLE htap_test COLVIEW;
ALTER TABLE
htapdb=# SELECT total FROM gs_imcv_flush('htap_test');
 total 
-------
 2
(1 row)

htapdb=# SELECT node_name, rel_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test |         5 | 1 2 3 4 5 |            1 |             6 |        8153 |              0 |            0
 dn_6003_6004 | htap_test |         5 | 1 2 3 4 5 |            1 |             6 |        8185 |              0 |            0
(2 rows)

--卸载IMCV。
htapdb=# ALTER TABLE htap_test NOCOLVIEW;
ALTER TABLE

htapdb=# SELECT node_name, rel_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
 node_name | rel_name | imcvnattr | imcvkey | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
-----------+----------+-----------+---------+--------------+---------------+-------------+----------------+--------------
(0 rows)

--加载除dept_id/comments外的其他列。
htapdb=# ALTER TABLE htap_test COLVIEW NOCOLVIEW(dept_id, comments);
ALTER TABLE

htapdb=# SELECT node_name, rel_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | imcvnattr | imcvkey | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+-----------+---------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test |         3 | 1 3 4   |            1 |             4 |        8153 |              0 |            0
 dn_6003_6004 | htap_test |         3 | 1 3 4   |            1 |             4 |        8185 |              0 |            0
(2 rows)

--卸载IMCV。
htapdb=# ALTER TABLE htap_test NOCOLVIEW;
ALTER TABLE

--加载id/name列。
htapdb=# ALTER TABLE htap_test NOCOLVIEW COLVIEW(id, name);
ALTER TABLE

htapdb=# SELECT node_name, rel_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | imcvnattr | imcvkey | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+-----------+---------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test |         2 | 1 4     |            1 |             3 |        8153 |              0 |            0
 dn_6003_6004 | htap_test |         2 | 1 4     |            1 |             3 |        8185 |              0 |            0
(2 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

gs_imcv是HTAP相关的共享系统表，详情请参见《参考》中“系统表和系统视图 > 系统表 > HTAP > GS_IMCV”章节。

建表时指定表级IMCV关键字为COLVIEW，不指定列级IMCV关键字。

htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)) COLVIEW with (storage_type = ustore) distribute by hash(id);
CREATE TABLE

--查询系统表gs_imcv，表的全部列均加载IMCV。
htapdb=# SELECT relname,imcvkey FROM gs_imcv;
  relname  |  imcvkey  
-----------+-----------
 htap_test | 1 2 3 4 5
(1 row)

htapdb=# DROP TABLE htap_test;
DROP TABLE

建表时指定表级IMCV关键字为COLVIEW，指定部分列级IMCV关键字为NOCOLVIEW，其余列不指定IMCV关键字。

htapdb=#  CREATE TABLE htap_test(
          id int,
          dept_id int NOCOLVIEW,
          salary int,
          name varchar(20) NOCOLVIEW,
          comments varchar(30)) COLVIEW WITH (storage_type = ustore) DISTRIBUTE BY HASH(id);
CREATE TABLE

--查询系统表gs_imcv，列id、salary和comments加载IMCV。
htapdb=# SELECT relname,imcvkey FROM gs_imcv;
  relname  | imcvkey 
-----------+---------
 htap_test | 1 3 5
(1 row)

htapdb=# DROP TABLE htap_test;
DROP TABLE

建表时不指定表级IMCV关键字，部分列指定IMCV关键字为COLVIEW，其余列不指定IMCV关键字。

htapdb=# CREATE TABLE htap_test(
          id int COLVIEW,
          dept_id int,
          salary int,
          name varchar(20) COLVIEW,
          comments varchar(30)) WITH (storage_type = ustore) DISTRIBUTE BY HASH(id);
CREATE TABLE

--查询系统表gs_imcv，列id、name加载IMCV。
htapdb=# SELECT relname,imcvkey FROM gs_imcv;
  relname  | imcvkey 
-----------+---------
 htap_test | 1 4
(1 row)

htapdb=# DROP TABLE htap_test;
DROP TABLE

分区表创建IMCV

语法

HTAP可以通过ALTER方式对分区表创建IMCV，分区表ALTER方式创建IMCV继承普通表的用法。对分区表额外增加对分区的ALTER语法，如下所示：
```
ALTER TABLE table_name MODIFY PARTITION part_name COLVIEW;
```

详情请参见《参考》中“SQL参考 > SQL语法 > A > ALTER TABLE”章节。

HTAP可以通过CREATE方式对分区表创建IMCV，创建分区表时可以指定表级IMCV、列级IMCV或分区级IMCV。

具体语法请参见《参考》中“SQL参考 > SQL语法 > C > CREATE TABLE”章节

分区表的IMCV加载列同样由表级IMCV和列级IMCV决定，规则与普通表一致。分区表有分区级IMCV，分区级IMCV和表级IMCV互相影响，决定最终有哪些分区加载IMCV。分区的IMCV列信息和基表保持一致。表级IMCV和分区级IMCV关键字的影响关系如表3所示。

表3 表级和分区级IMCV关键字影响关系
表级IMCV关键字	分区IMCV关键字	分区是否创建IMCV
COLVIEW	无/COLVIEW	是
无/COLVIEW/NOCOLVIEW	NOCOLVIEW	否
无/NOCOLVIEW	COLVIEW	是

示例

指定分区加载IMCV。

--创建分区表htap_test并插入数据。
htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)
          ) WITH(storage_type = ustore) DISTRIBUTE BY HASH(id)
          PARTITION BY HASH (id)(
          PARTITION id_p1,
          PARTITION id_p2,
          PARTITION id_p3);
CREATE TABLE

htapdb=# INSERT INTO htap_test VALUES (generate_series(1,100), 1, 8000, 'Allen', 'For test');
INSERT 0 100

--表htap_test没有加载IMCV，系统表gs_imcv查询记录为空。
htapdb=# SELECT relname,imcvkey FROM gs_imcv;
 relname | imcvkey 
---------+---------
(0 rows)

--通过ALTER为指定分区id_p1加载IMCV。
htapdb=# ALTER TABLE htap_test MODIFY PARTITION id_p1 COLVIEW;
ALTER TABLE

htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name, rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test | id_p1          |         5 | 1 2 3 4 5 |            1 |             6 |        8101 |              0 |            0
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p1          |         5 | 1 2 3 4 5 |            1 |             6 |        8101 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(4 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

创建分区表时指定表级IMCV关键字为COLVIEW，不指定分区级IMCV关键字。

htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)
          ) COLVIEW WITH(storage_type = ustore) DISTRIBUTE BY HASH(id)
          PARTITION BY HASH (id)(
          PARTITION id_p1,
          PARTITION id_p2,
          PARTITION id_p3);
CREATE TABLE

--所有分区均加载IMCV。
htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test | id_p1          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test | id_p2          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p1          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p2          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(8 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

创建分区表时指定表级IMCV关键字为COLVIEW，指定部分分区IMCV关键字为NOCOLVIEW。

htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)
          ) COLVIEW WITH(storage_type = ustore) DISTRIBUTE BY HASH(id)
          PARTITION BY HASH (id)(
          PARTITION id_p1,
          PARTITION id_p2 NOCOLVIEW,
          PARTITION id_p3 );
CREATE TABLE

--除id_p2外的其他分区均加载IMCV。
htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test | id_p1          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p1          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(6 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

创建分区表时指定表级IMCV关键字为NOCOLVIEW，指定部分分区IMCV关键字为COLVIEW。

--一级分区。
htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)
          ) NOCOLVIEW WITH(storage_type = ustore) DISTRIBUTE BY HASH(id)
          PARTITION BY HASH (id)(
          PARTITION id_p1,
          PARTITION id_p2 COLVIEW,
          PARTITION id_p3 );
CREATE TABLE

--分区id_p2加载IMCV，其余分区不加载IMCV。
htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test | id_p2          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p2          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(4 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

普通表卸载IMCV

语法
通过ALTER语句删除IMCV列存数据并删除IMCV元信息。
```
ALTER TABLE table_name NOCOLVIEW;
```

示例

ALTER TABLE ... NOCOLVIEW方式删除IMCV。

htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)) COLVIEW WITH (storage_type = ustore) DISTRIBUTE BY HASH(id);
CREATE TABLE

htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(2 rows)

htapdb=# ALTER TABLE htap_test NOCOLVIEW;
ALTER TABLE

--删除IMCV后，系统函数gs_global_imcv_info()查询无记录。
htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
 node_name | rel_name | partition_name | imcvnattr | imcvkey | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
-----------+----------+----------------+-----------+---------+--------------+---------------+-------------+----------------+--------------
(0 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

分区表卸载IMCV

语法
 分区表继承普通表删除IMCV的方式，并且可以针对分区删除IMCV列存数据和IMCV元信息。
```
ALTER TABLE table_name MODIFY PARTITION part_name NOCOLVIEW;
```

示例

对分区表删除IMCV。

htapdb=# CREATE TABLE htap_test(
          id int,
          dept_id int,
          salary int,
          name varchar(20),
          comments varchar(30)
 ) WITH (storage_type = ustore) DISTRIBUTE BY HASH(id)
 PARTITION BY RANGE (id)
 (
  PARTITION id_p1 VALUES LESS THAN (100),
  PARTITION id_p2 VALUES LESS THAN (200) COLVIEW,
  PARTITION id_p3 VALUES LESS THAN (300) COLVIEW,
  PARTITION id_p4 VALUES LESS THAN (400) ,
  PARTITION id_p5 VALUES LESS THAN (MAXVALUE));
CREATE TABLE

htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test | id_p2          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p2          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(6 rows)

htapdb=# ALTER TABLE htap_test MODIFY PARTITION id_p2 NOCOLVIEW;
ALTER TABLE

htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
  node_name   | rel_name  | partition_name | imcvnattr |  imcvkey  | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
--------------+-----------+----------------+-----------+-----------+--------------+---------------+-------------+----------------+--------------
 dn_6001_6002 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6001_6002 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test | id_p3          |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
 dn_6003_6004 | htap_test |                |         5 | 1 2 3 4 5 |            0 |             0 |           0 |              0 |            0
(4 rows)

htapdb=# ALTER TABLE htap_test DROP PARTITION id_p3;
ALTER TABLE

htapdb=# SELECT node_name, rel_name, partition_name, imcvnattr, imcvkey, rowgroup_num, cu_num_in_mem, cu_mem_size, cu_num_in_disk, cu_disk_size FROM gs_global_imcv_info() ORDER BY node_name,rel_name;
 node_name | rel_name | partition_name | imcvnattr | imcvkey | rowgroup_num | cu_num_in_mem | cu_mem_size | cu_num_in_disk | cu_disk_size 
-----------+----------+----------------+-----------+---------+--------------+---------------+-------------+----------------+--------------
(0 rows)

htapdb=# DROP TABLE htap_test;
DROP TABLE

父主题： DDL

上一篇：DDL

下一篇：其他DDL