更新时间：2026-07-28 GMT+08:00
创建和卸载IMCV
普通表创建IMCV
- 语法
- HTAP通过ALTER方式对普通表创建IMCV的语法如表1所示。
- HTAP通过CREATE方式对普通表创建IMCV，创建普通表时可以指定表级IMCV或者列级IMCV，具体语法如下：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [...]] [COLVIEW | NOCOLVIEW] | table_constraint} [, ... ]] ) [ WITH ( storage_parameter [= value] [, ... ] )] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED [ MEDIUM | HIGH ] ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )] [ TABLESPACE tablespace ] [ table_partitioning_clauses ] [COLVIEW | NOCOLVIEW]表级IMCV和列级IMCV互相影响决定最终加载哪些IMCV列。表级IMCV和列级IMCV的影响关系如表2所示。
- 示例
- 通过ALTER方式创建IMCV。
--创建普通表htap_test并插入数据。 gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30)) with (storage_type = ustore); CREATE TABLE gaussdb=#INSERT INTO htap_test VALUES (generate_series(1,100), 1, 8000, 'Allen', 'For test'); INSERT 0 100 --建表插入数据后执行ALTER COLVIEW创建IMCV。 gaussdb=# ALTER TABLE htap_test COLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17451 | htap_test | test | *** | 0 | f | 5 | 1 2 3 4 5 | 1 | | 13360 | 17451 | public (1 row) --卸载IMCV。 gaussdb=# ALTER TABLE htap_test NOCOLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+---------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ (0 rows) --加载除dept_id/comments外的其他列。 gaussdb=# ALTER TABLE htap_test COLVIEW NOCOLVIEW(dept_id, comments); ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ 17451 | htap_test | test | *** | 0 | f | 3 | 1 3 4 | 1 | | 13360 | 17451 | public (1 row) --卸载IMCV。 gaussdb=# ALTER TABLE htap_test NOCOLVIEW; ALTER TABLE --加载id/name列。 gaussdb=# ALTER TABLE htap_test NOCOLVIEW COLVIEW(id, name); ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ 17451 | htap_test | test | *** | 0 | f | 2 | 1 4 | 1 | | 13360 | 17451 | public (1 row) gaussdb=# DROP TABLE htap_test; DROP TABLE
gs_imcv是HTAP相关的系统表，详情请参见《参考》中“系统表和系统视图 > 系统表 > HTAP > GS_IMCV”章节。
- 建表时指定表级IMCV关键字为COLVIEW，不指定列级IMCV关键字。
gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30)) COLVIEW with (storage_type = ustore); CREATE TABLE --查询系统表gs_imcv，表的全部列均加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17347 | htap_test | test | dbdev | 0 | f | 5 | 1 2 3 4 5 | 1 | | 13360 | 17347 | public gaussdb=# DROP TABLE htap_test; DROP TABLE - 建表时指定表级IMCV关键字为COLVIEW，指定部分列级IMCV关键字为NOCOLVIEW，其余列不指定IMCV关键字。
gaussdb=# CREATE TABLE htap_test( id int, dept_id int NOCOLVIEW, salary int, name varchar(20) NOCOLVIEW, comments varchar(30)) COLVIEW with (storage_type = ustore); CREATE TABLE --查询系统表gs_imcv，列id、salary和comments加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ 17397 | htap_test | test | dbdev | 0 | f | 3 | 1 3 5 | 1 | | 13360 | 17397 | public gaussdb=# DROP TABLE htap_test; DROP TABLE - 建表时不指定表级IMCV关键字，部分列指定IMCV关键字为COLVIEW，其余列不指定IMCV关键字。
gaussdb=# CREATE TABLE htap_test( id int COLVIEW, dept_id int, salary int, name varchar(20) COLVIEW, comments varchar(30)) with (storage_type = ustore); CREATE TABLE --查询系统表gs_imcv，列id、name加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ 17413 | htap_test | test | *** | 0 | f | 2 | 1 4 | 1 | | 13360 | 17413 | public gaussdb=# DROP TABLE htap_test; DROP TABLE
- 通过ALTER方式创建IMCV。
分区表创建IMCV
- 语法
- HTAP可以对分区表通过ALTER方式创建IMCV，分区表ALTER方式创建IMCV继承普通表的用法。分区表额外增加对分区的ALTER语法，如下所示：
ALTER TABLE table_name MODIFY PARTITION part_name COLVIEW; ALTER TABLE table_name MODIFY SUBPARTITION subpart_name COLVIEW;
- HTAP也可以通过CREATE方式对分区表创建IMCV，创建分区表时可以指定表级IMCV、列级IMCV或分区级IMCV，具体语法如下： 一级分区：
CREATE TABLE [ IF NOT EXISTS ] partition_table_name ...... PARTITION BY { {RANGE [COLUMNS] (partition_key) [ PARTITIONS integer ] ( partition_less_than_item [, ... ] )} | {RANGE [COLUMNS] (partition_key) [ PARTITIONS integer ] ( partition_start_end_item [, ... ] )} | {LIST [COLUMNS] (partition_key) [ PARTITIONS integer ] ( PARTITION partition_name VALUES... partition_less_than_item： PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } ) [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED [ MEDIUM | HIGH ] ROW AFTER n { day | month | year } OF NO MODIFICATION [TABLESPACE tablespace_name] [COLVIEW | NOCOLVIEW] [ ON ( EXPR )]] partition_start_end_item： PARTITION partition_name { {START(partition_value) END (partition_value) EVERY (interval_value)} | {START(partition_value) END ({partition_value | MAXVALUE})} | {START(partition_value)} | {END({partition_value | MAXVALUE})} }[COLVIEW | NOCOLVIEW] ... [TABLESPACE tablespace_name]二级分区：CREATE TABLE [ IF NOT EXISTS ] subpartition_table_name ...... PARTITION BY {RANGE [ COLUMNS ] | LIST [ COLUMNS ] | HASH | KEY} (partition_keys) [ INTERVAL (interval_expr) [ STORE IN ( tablespace_name [, ...] ) ] ] [ AUTOMATIC ] [ PARTITIONS integer ] SUBPARTITION BY {RANGE | LIST | HASH | KEY} (subpartition_keys) [ INTERVAL (interval_expr) [ STORE IN ( tablespace_name [, ...] )]] [ AUTOMATIC ] [ SUBPARTITIONS integer ] PARTITION partition_name1 [ VALUES LESS THAN { (val1 | MAXVALUE) | MAXVALUE } | VALUES [ IN ] (val1[, ...]) ]...... [ { COLVIEW [ PRIORITY { HIGH | LOW | NONE } ] | NOCOLVIEW } ] [( { SUBPARTITION subpartition_name1 [ VALUES LESS THAN (val1_1) | VALUES (val1_1[, ...])]...... [ { COLVIEW [ PRIORITY { HIGH | LOW | NONE } ] | NOCOLVIEW } ] } [, ...] )] [, ...] )分区表的IMCV加载列同样由表级IMCV和列级IMCV决定，规则与普通表一致。分区表有分区级IMCV，分区级IMCV和表级IMCV互相影响，决定最终有哪些分区加载IMCV。分区的IMCV列信息和基表保持一致。表级和一级分区级IMCV关键字影响关系如表3所示，二级分区和一级分区IMCV关键字影响关系如表4所示。
- HTAP可以对分区表通过ALTER方式创建IMCV，分区表ALTER方式创建IMCV继承普通表的用法。分区表额外增加对分区的ALTER语法，如下所示：
- 示例
- 指定分区加载IMCV。
--创建分区表htap_test并插入数据。 gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) PARTITION BY HASH (id)( PARTITION id_p1, PARTITION id_p2, PARTITION id_p3); CREATE TABLE gaussdb=# INSERT INTO htap_test VALUES (generate_series(1,100), 1, 8000, 'Allen', 'For test'); INSERT 0 100 --表htap_test没有加载IMCV，系统表gs_imcv查询记录为空。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+---------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ (0 rows) --通过ALTER为指定分区id_p1加载IMCV。 gaussdb=# ALTER TABLE htap_test MODIFY PARTITION id_p1 COLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 16752 | htap_test | test | *** | 16752 | f | 5 | 1 2 3 4 5 | 1 | 16756 | 13360 | 16752 | public 16756 | id_p1 | test | *** | 16752 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16752 | public (2 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE --创建二级分区表并插入数据。 gaussdb=# CREATE TABLE test_imcv_subpartition(c1 INT, c2 INT) PARTITION BY RANGE (c1) SUBPARTITION BY LIST (c2)( PARTITION id_p1 VALUES LESS THAN (10)( SUBPARTITION sub_p1 VALUES (1), SUBPARTITION sub_p2 VALUES (2) ), PARTITION id_p2 VALUES LESS THAN (20), PARTITION id_p3 VALUES LESS THAN (MAXVALUE) ); CREATE TABLE gaussdb=# INSERT INTO test_imcv_subpartition VALUES (generate_series(1,30), 1); gaussdb=# INSERT INTO test_imcv_subpartition VALUES (generate_series(1,30), 2); --表test_imcv_subpartition没有加载IMCV，系统表gs_imcv查询记录为空。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ (0 rows) gaussdb=# ALTER TABLE test_imcv_subpartition MODIFY SUBPARTITION sub_p1 COLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+------------------------+--------+----------+-----------+------------+-----------+---------+----------+-------------+-------+---------+------------ 16772 | test_imcv_subpartition | test | dbdev | 16752 | f | 2 | 1 2 | 1 | 17432 | 13360 | 16772 | public 16775 | id_p1 | test | dbdev | 16752 | t | 2 | 1 2 | 1 | 16777 | 13360 | 16772 | public 16777 | sub_p1 | test | dbdev | 16775 | t | 2 | 1 2 | 1 | | 13360 | 16772 | public (2 rows) gaussdb=# DROP TABLE test_imcv_subpartition; DROP TABLE - 创建分区表时指定表级IMCV关键字为COLVIEW，不指定分区级IMCV关键字。
--一级分区。 gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) COLVIEW PARTITION BY HASH (id)( PARTITION id_p1, PARTITION id_p2, PARTITION id_p3); CREATE TABLE --所有分区均加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------------+-------+---------+------------ 17417 | htap_test | test | *** | 17417 | f | 5 | 1 2 3 4 5 | 1 | 17421 17422 17423 | 13360 | 17417 | public 17421 | id_p1 | test | *** | 17417 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17417 | public 17422 | id_p2 | test | *** | 17417 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17417 | public 17423 | id_p3 | test | *** | 17417 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17417 | public (4 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE--二级分区表。 gaussdb=# CREATE TABLE test_imcv_subpartition(c1 INT, c2 INT) COLVIEW PARTITION BY RANGE (c1) SUBPARTITION BY LIST (c2)( PARTITION id_p1 VALUES LESS THAN (10)( SUBPARTITION sub_p1 VALUES (1), SUBPARTITION sub_p2 VALUES (2) ), PARTITION id_p2 VALUES LESS THAN (20), PARTITION id_p3 VALUES LESS THAN (MAXVALUE) ); CREATE TABLE --所有分区均加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+------------------------+--------+----------+-----------+------------+-----------+---------+----------+-------------------+-------+---------+------------ 16788 | test_imcv_subpartition | test | *** | 16788 | f | 2 | 1 2 | 1 | 16792 16795 16797 | 13360 | 16788 | public 16793 | sub_p1 | test | *** | 16792 | t | 2 | 1 2 | 1 | | 13360 | 16788 | public 16794 | sub_p2 | test | *** | 16792 | t | 2 | 1 2 | 1 | | 13360 | 16788 | public 16792 | id_p1 | test | *** | 16788 | t | 2 | 1 2 | 1 | 16793 16794 | 13360 | 16788 | public 16796 | id_p2_subpartdefault1 | test | *** | 16795 | t | 2 | 1 2 | 1 | | 13360 | 16788 | public 16795 | id_p2 | test | *** | 16788 | t | 2 | 1 2 | 1 | 16796 | 13360 | 16788 | public 16798 | id_p3_subpartdefault1 | test | *** | 16797 | t | 2 | 1 2 | 1 | | 13360 | 16788 | public 16797 | id_p3 | test | *** | 16788 | t | 2 | 1 2 | 1 | 16798 | 13360 | 16788 | public (8 rows) gaussdb=# DROP TABLE test_imcv_subpartition; DROP TABLE - 创建分区表时指定表级IMCV关键字为COLVIEW，指定部分分区IMCV关键字为NOCOLVIEW。
--一级分区表。 gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) COLVIEW PARTITION BY HASH (id)( PARTITION id_p1, PARTITION id_p2 NOCOLVIEW, PARTITION id_p3 ); CREATE TABLE --除id_p2外的其他分区均加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17428 | htap_test | test | *** | 17428 | f | 5 | 1 2 3 4 5 | 1 | 17432 17434 | 13360 | 17428 | public 17432 | id_p1 | test | *** | 17428 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17428 | public 17434 | id_p3 | test | *** | 17428 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17428 | public (3 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE--二级分区表。 gaussdb=# CREATE TABLE test_imcv_subpartition(c1 INT, c2 INT) COLVIEW PARTITION BY RANGE (c1) SUBPARTITION BY LIST (c2)( PARTITION id_p1 VALUES LESS THAN (10)( SUBPARTITION sub_p1 VALUES (1) NOCOLVIEW, SUBPARTITION sub_p2 VALUES (2) ), PARTITION id_p2 VALUES LESS THAN (20) NOCOLVIEW, PARTITION id_p3 VALUES LESS THAN (MAXVALUE) ); CREATE TABLE --除NOCOLVIEW外的其他分区均加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+------------------------+--------+----------+-----------+------------+-----------+---------+----------+-------------------+-------+---------+------------ 16788 | test_imcv_subpartition | test | *** | 16788 | f | 2 | 1 2 | 1 | 16792 16795 16797 | 13360 | 16788 | public 16794 | sub_p2 | test | *** | 16792 | t | 2 | 1 2 | 1 | | 13360 | 16788 | public 16792 | id_p1 | test | *** | 16788 | t | 2 | 1 2 | 1 | 16793 16794 | 13360 | 16788 | public 16798 | id_p3_subpartdefault1 | test | *** | 16797 | t | 2 | 1 2 | 1 | | 13360 | 16788 | public 16797 | id_p3 | test | *** | 16788 | t | 2 | 1 2 | 1 | 16798 | 13360 | 16788 | public (5 rows) gaussdb=# DROP TABLE test_imcv_subpartition; DROP TABLE
- 创建分区表时指定表级IMCV关键字为NOCOLVIEW，指定部分分区IMCV关键字为COLVIEW。
--一级分区。 gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) NOCOLVIEW PARTITION BY HASH (id)( PARTITION id_p1, PARTITION id_p2 COLVIEW, PARTITION id_p3 ); CREATE TABLE --分区id_p2加载IMCV，其余分区不加载IMCV。 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17438 | htap_test | test | *** | 17438 | f | 5 | 1 2 3 4 5 | 1 | 17443 | 13360 | 17438 | public 17443 | id_p2 | test | *** | 17438 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17438 | public (2 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE--二级分区。 gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) WITH (STORAGE_TYPE=USTORE) PARTITION BY RANGE (id) SUBPARTITION BY RANGE (dept_id) ( PARTITION id_p1 VALUES LESS THAN (100) ( SUBPARTITION id_sp11 VALUES LESS THAN(50) COLVIEW, SUBPARTITION id_sp12 VALUES LESS THAN( MAXVALUE ) ), PARTITION id_p2 VALUES LESS THAN (MAXVALUE) COLVIEW ( SUBPARTITION id_sp21 VALUES LESS THAN(50), SUBPARTITION id_sp22 VALUES LESS THAN( MAXVALUE ) ) ); CREATE TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 16815 | id_sp21 | test | *** | 16814 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16807 | public 16816 | id_sp22 | test | *** | 16814 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16807 | public 16814 | id_p2 | test | *** | 16807 | t | 5 | 1 2 3 4 5 | 1 | 16815 16816 | 13360 | 16807 | public 16812 | id_sp11 | test | *** | 16811 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16807 | public 16811 | id_p1 | test | *** | 16807 | t | 5 | 1 2 3 4 5 | 1 | 16812 | 13360 | 16807 | public 16807 | htap_test | test | *** | 16807 | f | 5 | 1 2 3 4 5 | 1 | 16811 16814 | 13360 | 16807 | public (6 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE
- 指定分区加载IMCV。
普通表卸载IMCV
- 语法
- 示例 ALTER TABLE ... NOCOLVIEW方式删除IMCV。
gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30)) COLVIEW with (storage_type = ustore); CREATE TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17477 | htap_test | test | *** | 0 | f | 5 | 1 2 3 4 5 | 1 | | 13360 | 17477 | public (1 row) --删除IMCV后，系统表gs_imcv查询无记录。 gaussdb=# ALTER TABLE htap_test NOCOLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ (0 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE
分区表卸载IMCV
- 语法
分区表继承普通表卸载IMCV的方式，并且可以针对分区卸载IMCV列存数据和IMCV元信息。
ALTER TABLE table_name MODIFY PARTITION part_name NOCOLVIEW; ALTER TABLE table_name MODIFY SUBPARTITION subpart_name NOCOLVIEW;
- 示例
- 对一级分区表卸载IMCV。
gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) 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 gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17885 | htap_test | test | *** | 17885 | f | 5 | 1 2 3 4 5 | 1 | 17890 17891 | 13360 | 17885 | public 17890 | id_p2 | test | *** | 17885 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17885 | public 17891 | id_p3 | test | *** | 17885 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17885 | public (3 rows) gaussdb=# ALTER TABLE htap_test MODIFY PARTITION id_p2 NOCOLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 17885 | htap_test | test | *** | 17885 | f | 5 | 1 2 3 4 5 | 1 | 17891 | 13360 | 17885 | public 17891 | id_p3 | test | *** | 17885 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 17885 | public (2 rows) gaussdb=# ALTER TABLE htap_test DROP PARTITION id_p3; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ (0 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE - 对二级分区表卸载IMCV。
gaussdb=# CREATE TABLE htap_test( id int, dept_id int, salary int, name varchar(20), comments varchar(30) ) WITH (STORAGE_TYPE=USTORE) PARTITION BY RANGE (id) SUBPARTITION BY RANGE (dept_id) ( PARTITION id_p1 VALUES LESS THAN (100) ( SUBPARTITION id_sp11 VALUES LESS THAN(50), SUBPARTITION id_sp12 VALUES LESS THAN( MAXVALUE ) ), PARTITION id_p2 VALUES LESS THAN (MAXVALUE) COLVIEW ( SUBPARTITION id_sp21 VALUES LESS THAN(50), SUBPARTITION id_sp22 VALUES LESS THAN( MAXVALUE ) ) ); CREATE TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 16766 | htap_test | test | *** | 16766 | f | 5 | 1 2 3 4 5 | 1 | 16773 | 13360 | 16766 | public 16774 | id_sp21 | test | *** | 16773 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16766 | public 16775 | id_sp22 | test | *** | 16773 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16766 | public 16773 | id_p2 | test | *** | 16766 | t | 5 | 1 2 3 4 5 | 1 | 16774 16775 | 13360 | 16766 | public (4 rows) gaussdb=# ALTER TABLE htap_test MODIFY SUBPARTITION id_sp21 NOCOLVIEW; ALTER TABLE gaussdb=# SELECT * FROM gs_imcv; reloid | relname | dbname | username | parentoid | imcvispart | imcvnattr | imcvkey | priority | childoids | dboid | rootoid | schemaname --------+-----------+--------+----------+-----------+------------+-----------+-----------+----------+-------------+-------+---------+------------ 16766 | htap_test | test | *** | 16766 | f | 5 | 1 2 3 4 5 | 1 | 16773 | 13360 | 16766 | public 16775 | id_sp22 | test | *** | 16773 | t | 5 | 1 2 3 4 5 | 1 | | 13360 | 16766 | public 16773 | id_p2 | test | *** | 16766 | t | 5 | 1 2 3 4 5 | 1 | 16774 16775 | 13360 | 16766 | public (3 rows) gaussdb=# DROP TABLE htap_test; DROP TABLE
- 对一级分区表卸载IMCV。
父主题： DDL
