华为云用户手册

功能特点 TRUNCATE TABLE在功能上与不带WHERE子句DELETE语句相同：二者均删除表中的全部行。 TRUNCATE TABLE比DELETE速度快且使用系统和事务日志资源少： DELETE语句每次删除一行，并在事务日志中为所删除每行记录一项。 TRUNCATE TABLE通过释放存储表数据所用数据页来删除数据，并且只在事务日志中记录页的释放。 TRUNCATE，DELETE，DROP三者的差异如下： TRUNCATE TABLE，删除内容，释放空间，但不删除定义。 DELETE TABLE，删除内容，不删除定义，不释放空间。 DROP TABLE，删除内容和定义，释放空间。

示例 创建表。 1 CREATE TABLE tpcds.reason_t1 AS TABLE tpcds.reason; 清空表tpcds.reason_t1。 1 TRUNCATE TABLE tpcds.reason_t1; 删除表。 1 DROP TABLE tpcds.reason_t1; 创建分区表。 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE tpcds.reason_p ( r_reason_sk integer, r_reason_id character(16), r_reason_desc character(100) )PARTITION BY RANGE (r_reason_sk) ( partition p_05_before values less than (05), partition p_15 values less than (15), partition p_25 values less than (25), partition p_35 values less than (35), partition p_45_after values less than (MAXVALUE) ); 插入数据。 1 INSERT INTO tpcds.reason_p SELECT * FROM tpcds.reason; 清空分区p_05_before。 1 ALTER TABLE tpcds.reason_p TRUNCATE PARTITION p_05_before; 清空13所在的分区p_15。 1 ALTER TABLE tpcds.reason_p TRUNCATE PARTITION for (13); 清空分区表。 1 TRUNCATE TABLE tpcds.reason_p; 删除表。 1 DROP TABLE tpcds.reason_p;

查询数据在各DN分布 1 2 3 4 5 6 7 8 9 10 SELECT table_skewness('my_table'); table_skewness ------------------------------------ ("dn_6007_6008 ",3,50.000%) ("dn_6009_6010 ",2,33.333%) ("dn_6003_6004 ",1,16.667%) ("dn_6001_6002 ",0,0.000%) ("dn_6005_6006 ",0,0.000%) ("dn_6011_6012 ",0,0.000%) (6 rows)

查询某一有数据分布DN上分区P1所对应的cudesc和delta表名称 1 2 3 4 5 6 EXECUTE DIRECT ON (dn_6003_6004) 'select a.relname from pg_class a, pg_partition b where (a.oid=b.reldeltarelid or a.oid=b.relcudescrelid) and b.relname=''my_table_p1'''; relname ---------------------- pg_delta_part_60317 pg_cudesc_part_60317 (2 rows)

查询分区边界 1 2 3 4 5 6 7 8 9 SELECT relname, partstrategy, boundaries FROM pg_partition where parentid=(select parentid from pg_partition where relname='my_table'); relname | partstrategy | boundaries -------------+--------------+------------ my_table | r | my_table_p1 | r | {600} my_table_p2 | r | {800} my_table_p3 | r | {950} my_table_p4 | r | {1000} (5 rows)

函数和存储过程有什么区别？ 函数和存储过程是数据库管理系统中常见的两种对象，它们在实现特定功能时具有相同点，也有不同点。了解它们的特点和适用场景，对于合理设计数据库结构和提高数据库性能具有重要意义。 表1 函数和存储过程的区别 函数 存储过程 两者都可以用于实现特定的功能。无论是函数还是存储过程，都可以封装一系列的SQL语句，以完成某些特定的操作。 两者都可以接收输入参数，并且根据参数的不同来进行相应的操作。 函数的标识符为FUNCTION。 存储过程的标识符为PROCEDURE。 函数必须返回一个具体的值，并且规定返回值的数值类型。 存储过程可以没有返回值，也可以有返回值，甚至可以有多个返回值，可以通过输出参数返回结果，也可以直接在存储过程中使用SELECT语句返回结果集。 函数适用于需要返回单个值的情况，比如计算某个数值、字符串处理、返回表等。 存储过程适用于需要执行DML操作的情况，比如批量插入、更新、删除数据等。 创建并调用函数 创建表emp并插入数据，查询表数据如下： 1 2 3 4 5 6 7 8 SELECT * FROM emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+----------+------+---------------------+---------+--------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 (4 rows) 创建函数emp_comp，用于接受两个数字作为输入并返回计算值： 1 2 3 4 5 6 7 8 9 CREATE OR REPLACE FUNCTION emp_comp ( p_sal NUMBER, p_comm NUMBER ) RETURN NUMBER IS BEGIN RETURN (p_sal + NVL(p_comm, 0)) * 24; END; / 使用SELECT命令调用函数： 1 2 3 4 5 6 7 8 SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm) "Total Compensation" FROM emp; Name | Salary | Commission | Total Compensation -------+---------+------------+-------------------- SMITH | 800.00 | | 19200.00 ALLEN | 1600.00 | 300.00 | 45600.00 JONES | 2975.00 | | 71400.00 WARD | 1250.00 | 500.00 | 42000.00 (4 rows) 创建并调用存储过程 创建表MATCHES并插入数据，查询表数据如下： 1 2 3 4 5 6 7 8 9 SELECT * FROM MATCHES; matchno | teamno | playerno | won | lost ---------+--------+----------+-----+------ 1 | 1 | 6 | 3 | 1 7 | 1 | 57 | 3 | 0 8 | 1 | 8 | 0 | 3 9 | 2 | 27 | 3 | 2 11 | 2 | 112 | 2 | 3 (5 rows) 创建存储过程delete_matches，用于删除给定球员参加的所有比赛： 1 2 3 4 5 6 CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) AS BEGIN DELETE FROM MATCHES WHERE playerno = p_playerno; END; / 调用存储过程delete_matches： 1 CALL delete_matches(57); 再次查询表MATCHES，由返回结果可知，playerno为57的数据已被删除： 1 2 3 4 5 6 7 8 SELECT * FROM MATCHES; matchno | teamno | playerno | won | lost ---------+--------+----------+-----+------ 11 | 2 | 112 | 2 | 3 8 | 1 | 8 | 0 | 3 1 | 1 | 6 | 3 | 1 9 | 2 | 27 | 3 | 2 (4 rows) 父主题： 数据库使用

如果建表时没有指定分布列，数据会怎么存储？ 8.1.2及以上集群版本，可通过GUC参数default_distribution_mode来查询和设置表的默认分布方式。 如果建表时没有指定分布列，数据会以下几种场景来存储： 场景一 若建表时包含主键/唯一约束，则选取HASH分布，分布列为主键/唯一约束对应的列。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE TABLE warehouse1 ( W_WAREHOUSE_SK INTEGER PRIMARY KEY, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "warehouse1_pkey" for table "warehouse1" CREATE TABLE SELECT getdistributekey('warehouse1'); getdistributekey ------------------ w_warehouse_sk (1 row) 场景二 若建表时不包含主键/唯一约束，但存在数据类型支持作分布列的列，则选取HASH分布，分布列为第一个数据类型支持作分布列的列。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE warehouse2 ( W_WAREHOUSE_SK INTEGER , W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) ); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'w_warehouse_sk' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE SELECT getdistributekey('warehouse2'); getdistributekey ------------------ w_warehouse_sk (1 row) 场景三 若建表时不包含主键/唯一约束，也不存在数据类型支持作分布列的列，选取ROUNDROBIN分布。 CREATE TABLE warehouse3 ( W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) ); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'w_warehouse_id' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE SELECT getdistributekey('warehouse3'); getdistributekey ------------------ w_warehouse_id (1 row) 父主题： 数据库使用

操作步骤 以下将演示不同权限的授权方法和验证过程。 打开窗口1（即dbadmin连接会话窗口，后续不再提示），使用系统管理员dbadmin连接DWS数据库，创建用户u1~u5（系统默认会创建u1~u5的同名SCHEMA）。 1 2 3 4 5 CREATE USER u1 PASSWORD '{password}'; CREATE USER u2 PASSWORD '{password}'; CREATE USER u3 PASSWORD '{password}'; CREATE USER u4 PASSWORD '{password}'; CREATE USER u5 PASSWORD '{password}'; 在SCHEMA u1下创建表u1.t1。 1 CREATE TABLE u1.t1 (c1 int, c2 int); 为表中插入两条数据。 1 2 INSERT INTO u1.t1 VALUES (1,2); INSERT INTO u1.t1 VALUES (1,2); DWS中引入了SCHEMA层概念，如果有SCHEMA，需要先给用户赋予SCHEMA的使用权限。 1 GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5; 给只读用户u2赋予表u1.t1的查询权限。 1 GRANT SELECT ON u1.t1 TO u2; 打开窗口2（即用户u2连接会话窗口，后续不再提示），使用用户u2连接DWS数据库，验证u2可以查询u1.t1表，但是不能写入和修改，此时u2为只读用户。 1 2 3 SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; 切回窗口1，分别给u3、u4、u5赋予对应的权限。 1 2 3 GRANT INSERT ON u1.t1 TO u3; --插入用户u3，可以插入数据 GRANT SELECT,UPDATE ON u1.t1 TO u4; --改写用户u4，可以修改表 GRANT ALL PRIVILEGES ON u1.t1 TO u5; --拥有所有权限的用户u5，可以对表进行查询、插入、改写和删除 打开窗口3，使用用户u3连接DWS数据库，验证u3可以插入u1.t1，但是不能查询和修改，此时u3为插入用户。 1 2 3 SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; 打开窗口4，使用用户u4连接DWS数据库，验证u4可以修改u1.t1，同时还可以查询，但是不能插入，此时u4为改写用户。 1 2 3 SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; 打开窗口5，使用用户u5连接DWS数据库，验证u5可以查询、插入、修改和删除u1.t1，此时u5为拥有所有权限的用户。 1 2 3 4 SELECT * FROM u1.t1; INSERT INTO u1.t1 VALUES (1,20); UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; DELETE FROM u1.t1; 最后切回窗口1，通过函数has_table_privilege分别查询每个用户的权限。 1 SELECT * FROM pg_class WHERE relname = 't1'; 返回结果，查看relacl字段，该字段回显结果如下。"rolename=xxxx/yyyy" --表示rolename对该表有xxxx权限，且权限来自yyyy； 例如下图，与以上验证结果完全一致。 u1=arwdDxtA/u1，表示u1为owner，拥有所有权限。 u2=r/u1，表示u2拥有读权限。 u3=a/u1，表示u3拥有插入权限。 u4=rw/u1，表示u4拥有读和修改权限。 u5=arwdDxtA/u1，表示u5拥有所有权限。

场景介绍 假设当前有用户u1~u5，在系统中有对应的同名Schema u1~u5，各用户的权限管控如下： u2作为只读用户，需要表u1.t1的SELECT权限。 u3作为插入用户，需要表u1.t1的INSERT权限。 u4作为改写用户，需要表u1.t1的UPDATE权限。 u5作为拥有所有权限的用户，需要表u1.t1的所有权限。 表1 表u1.t1的表权限分类 用户名 用户类型 Grant授权语句 查询 插入 修改 删除 u1 所有者 - √ √ √ √ u2 只读用户 1 GRANT SELECT ON u1.t1 TO u2; √ x x x u3 插入用户 1 GRANT INSERT ON u1.t1 TO u3; x √ x x u4 改写用户 1 GRANT SELECT,UPDATE ON u1.t1 TO u4; 须知： 授予UPDATE权限必须同时授予SELECT权限，否则会出现信息泄露。 √ x √ x u5 拥有所有权限的用户 1 GRANT ALL PRIVILEGES ON u1.t1 TO u5; √ √ √ √

语法格式 1 2 3 4 5 6 GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFEREN CES | TRIGGER | ANALYZE | ANALYSE } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ];

场景一：返回结果集很大 以行存表的Seq Scan和Index Scan为例： Seq Scan：按照表的记录的排列顺序从头到尾依次检索扫描，每次扫描要取到所有的记录。这也是最简单最基础的扫表方式，扫描的代价比较大。 Index Scan：对于指定的查询，先扫描一遍索引，从索引中找到符合要求的记录的位置（指针），再定位到表中具体的Page去获取，即先走索引，再读表数据。 因此，根据两种扫描方式的特点可知，多数情况下，Index Scan要比Seq Scan快。但是如果获取的结果集占所有数据的比重很大时（超过70%），这时Index Scan因为要先扫描索引再读表数据反而不如直接全表扫描的速度快。

如何将联结查询的null结果替换成0？ 在执行outer join（left join、right join、full join）联结查询时，outer join在匹配失败的情况下结果集会补空，产生大量NULL值， 可以在联结查询时将这部分null值替换为0。 可使用coalesce函数，它的作用是返回参数列表中第一个非NULL的参数值。例如： 1 2 3 4 5 SELECT coalesce(NULL,'hello'); coalesce ---------- hello (1 row) 有表course1和表course2，使用left join对两表进行联结查询： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT * FROM course1; stu_id | stu_name | cour_name ----------+------------+-------------------- 20110103 | ALLEN | Math 20110102 | JACK | Programming Design 20110101 | MAX | Science (3 rows) SELECT * FROM course2; cour_id | cour_name | teacher_name ---------+--------------------+-------------- 1002 | Programming Design | Mark 1001 | Science | Anne (2 rows) SELECT course1.stu_name,course2.cour_id,course2.cour_name,course2.teacher_name FROM course1 LEFT JOIN course2 ON course1.cour_name = course2.cour_name ORDER BY 1; stu_name | cour_id | cour_name | teacher_name ------------+---------+--------------------+-------------- ALLEN | | | JACK | 1002 | Programming Design | Mark MAX | 1001 | Science | Anne (3 rows) 使用coalesce函数将查询结果中的空值替换为0或其他非0值： 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT course1.stu_name, coalesce(course2.cour_id,0) AS cour_id, coalesce(course2.cour_name,'NA') AS cour_name, coalesce(course2.teacher_name,'NA') AS teacher_name FROM course1 LEFT JOIN course2 ON course1.cour_name = course2.cour_name ORDER BY 1; stu_name | cour_id | cour_name | teacher_name ------------+---------+--------------------+-------------- ALLEN | 0 | NA | NA JACK | 1002 | Programming Design | Mark MAX | 1001 | Science | Anne (3 rows) 父主题： 数据库使用

唯一约束和唯一索引有什么区别？ 唯一约束和唯一索引概念上不同 唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。 如果没有声明DISTRIBUTE BY REPLICATION，则唯一约束的列集合中必须包含分布列。 唯一索引用于限制索引字段值的唯一性，或者是多个字段组合值的唯一性。CREATE UNIQUE INDEX创建唯一索引。 唯一约束和唯一索引功能上不同 约束主要是为了保证数据的完整性，索引主要是为了辅助查询。 唯一约束和唯一索引使用方法上不同 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有NULL。 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。 GaussDB (DWS)行存表支持唯一约束，而列存表不支持。 创建一个唯一索引，这个索引独立的、可以单独删除。目前，GaussDB(DWS)只有B-Tree可以创建唯一索引。 如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。 如果表的一个字段，要作为另外一个表的外键，这个字段必须有唯一约束（或是主键），如果只是有唯一索引，就会报错。 示例：创建两个列的复合索引，并不要求是唯一索引。 1 2 CREATE TABLE t (n1 number,n2 number,n3 number,PRIMARY KEY (n3)); CREATE INDEX t_idx ON t(n1,n2); GaussDB(DWS)支持多个唯一索引。 1 2 CREATE UNIQUE INDEX u_index ON t(n3); CREATE UNIQUE INDEX u_index1 ON t(n3); 可以使用上述示例创建的索引t_idx来创建唯一约束t_uk，而且它只在列n1上唯一，也就是说唯一约束比索引更加严格。 1 ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE USING INDEX u_index; 父主题： 数据库使用

列转行 使用union all，将各科目（math、physics和literature）整合为一列，示例如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT * FROM ( SELECT name, 'math' AS subject, math AS score FROM students_info1 union all SELECT name, 'physics' AS subject, physics AS score FROM students_info1 union all SELECT name, 'literature' AS subject, literature AS score FROM students_info1 ) order by name; name | subject | score ------+------------+------- jack | math | 90 jack | physics | 95 jack | literature | 95 lily | math | 95 lily | physics | 80 lily | literature | 92 matu | math | 75 matu | physics | 90 matu | literature | 85 (9 rows)

静态行转列 静态行转列需要手动指定每一列的列名，如果存在则取其对应值，否则将赋其默认值0。 1 2 3 4 5 6 7 8 9 10 SELECT name, sum(case when subject='math' then score else 0 end) as math, sum(case when subject='physics' then score else 0 end) as physics, sum(case when subject='literature' then score else 0 end) as literature FROM students_info GROUP BY name; name | math | physics | literature ------+------+---------+------------ matu | 75 | 90 | 85 lily | 95 | 80 | 92 jack | 90 | 95 | 95 (3 rows)

动态行转列 8.1.2及以上集群版本可使用GROUP_CONCAT生成列存语句。 1 2 3 4 5 6 7 8 9 10 SELECT group_concat(concat('sum(IF(subject = ''', subject, ''', score, 0)) AS "', name, '"'))FROM students_info; group_concat ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ sum(IF(subject = 'literature', score, 0)) AS "jack",sum(IF(subject = 'literature', score, 0)) AS "lily",sum(IF(subject = 'literature', score, 0)) AS "matu",sum(IF(subject = 'math', score, 0)) AS "jack",sum(IF (subject = 'math', score, 0)) AS "lily",sum(IF(subject = 'math', score, 0)) AS "matu",sum(IF(subject = 'physics', score, 0)) AS "jack",sum(IF(subject = 'physics', score, 0)) AS "lily",sum(IF(subject = 'physics ', score, 0)) AS "matu" (1 row) 8.1.1及更低版本中可用LISTAGG生成列存语句。 1 2 3 4 5 6 7 8 SELECT listagg(concat('sum(case when subject = ''', subject, ''' then score else 0 end) AS "', subject, '"'),',') within GROUP(ORDER BY 1)FROM (select distinct subject from students_info); listagg ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- sum(case when subject = 'literature' then score else 0 end) AS "literature",sum(case when subject = 'physics' then score else 0 end) AS "physics",sum(case when subject = 'math' then score else 0 end) AS "math " (1 row) 再通过视图动态重建： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE OR REPLACE FUNCTION build_view() RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE sql text; rec record; BEGIN sql := 'select LISTAGG( CONCAT( ''sum(case when subject = '''''', subject, '''''' then score else 0 end) AS "'', subject, ''"'' ) ,'','' ) within group(order by 1) from (select distinct subject from students_info);'; EXECUTE sql INTO rec; sql := 'drop view if exists get_score'; EXECUTE sql; sql := 'create view get_score as select name, ' || rec.LISTAGG || ' from students_info group by name'; EXECUTE sql; END$$; 执行重建： 1 CALL build_view(); 查询视图： 1 2 3 4 5 6 7 SELECT * FROM get_score; name | literature | physics | math ------+------------+---------+------ matu | 85 | 90 | 75 lily | 92 | 80 | 95 jack | 95 | 95 | 90 (3 rows)

示例表 创建行存表students_info并插入数据。 1 2 3 4 5 6 7 8 9 10 CREATE TABLE students_info(name varchar(20),subject varchar(100),score bigint) distribute by hash(name); INSERT INTO students_info VALUES('lily','math',95); INSERT INTO students_info VALUES('lily','physics',80); INSERT INTO students_info VALUES('lily','literature',92); INSERT INTO students_info VALUES('matu','math',75); INSERT INTO students_info VALUES('matu','physics',90); INSERT INTO students_info VALUES('matu','literature',85); INSERT INTO students_info VALUES('jack','math',90); INSERT INTO students_info VALUES('jack','physics',95); INSERT INTO students_info VALUES('jack','literature',95); 查看表students_info信息。 1 2 3 4 5 6 7 8 9 10 11 12 SELECT * FROM students_info; name | subject | score ------+------------+------- matu | math | 75 matu | physics | 90 matu | literature | 85 lily | math | 95 lily | physics | 80 lily | literature | 92 jack | math | 90 jack | physics | 95 jack | literature | 95 创建列存表students_info1并插入数据。 1 2 3 4 CREATE TABLE students_info1(name varchar(20), math bigint, physics bigint, literature bigint) with (orientation = column) distribute by hash(name); INSERT INTO students_info1 VALUES('lily',95,80,92); INSERT INTO students_info1 VALUES('matu',75,90,85); INSERT INTO students_info1 VALUES('jack',90,95,95); 查看表students_info1信息。 1 2 3 4 5 6 7 SELECT * FROM students_info1; name | math | physics | literature ------+------+---------+------------ matu | 75 | 90 | 85 lily | 95 | 80 | 92 jack | 90 | 95 | 95 (3 rows)

场景介绍 以学生成绩为例： 老师会按照学科录入成绩，每科老师都会单独录入每个学生对应学科的成绩，而每位学生只关注自己各科的成绩。如果把老师录入数据作为原始表，那么学生查看自己的成绩就要用到行转列；如果让学生自己填写各科的成绩并汇总，然后老师去查自己学科所有学生的成绩，那就是列转行。 行转列与列转行的示意图如下： 图1 示意图 行转列 将多行数据转换成一行显示，或将一列数据转换成多列显示。 列转行 将一行数据转换成多行显示，或将多列数据转换成一列显示。

如何清理与回收存储空间？ GaussDB(DWS) 数据仓库 中保存的数据在删除后，可能没有释放占用的磁盘空间形成脏数据，导致磁盘浪费、创建及恢复快照性能下降等问题，如何清理？ 清理与回收存储空间对系统的影响如下： 删除无用的脏数据，释放存储空间。 数据库将进行大量读写操作，可能影响正常使用，建议选择空闲时间执行。 数据库的存储空间越大，即数据可能越多，清理的时间越长。 清理与回收存储空间操作步骤如下： 连接数据库。具体操作步骤请参见连接集群。 执行以下命令，清理与回收存储空间。 VACUUM FULL; 默认清理当前用户在数据库中，拥有权限的每一个表。没有权限的表则直接跳过回收操作。 当系统显示以下内容时，表示清理完成： 1 VACUUM VACUUM FULL回收所有过期的行空间，但是它需要对每个正在处理的表进行独占锁定，并且可能需要很长时间才能完成大型 分布式数据库 表。一般建议对指定表执行VACUUM FULL，若需要对整个数据库执行VACUUM FULL，建议在数据库维护期间操作。 使用FULL参数会导致统计信息丢失，如果需要收集统计信息，请在语句命令中加上analyze关键字。例如，执行VACUUM FULL ANALYZE;命令。 VACUUM的语法请参见《SQL语法参考》中的VACUUM章节。 父主题： 集群管理

选择增加CN还是扩容集群 连接数不足：初次创建集群时，集群默认的CN节点数是3，能基本满足客户的连接需求。当集群属于高并发请求，各CN节点的连接数很大，或CN节点的CPU明显高于DN节点的CPU时，建议增加CN节点数量，具体参见管理CN节点章节。 存储容量和性能不足：随着您的业务规模扩张，对数据存储容量和性能有更高的要求时，或者集群整体CPU不足时，建议通过扩容集群进行集群节点的扩容。详情请参见扩容集群章节。 随着业务规模扩大，集群扩容到一定节点规模后，也必然需要增加相应的CN节点，满足DWS的分布式要求。简言之，增加CN，不一定需要扩容集群，但是扩容集群后，会伴随着增加CN节点的需求。

CN并发介绍 CN全称为：协调节点（Coordinator Node），是和用户关系最密切也是DWS内部非常重要的一个组件。它负责提供外部应用接口、优化全局执行计划、向Datanode分发执行计划，以及汇总、处理执行结果。CN是外部应用的接口，CN的并发能力直接决定了业务的并发度。 单CN的并发能力受如下几个参数控制： max_connections：允许和数据库连接的最大并发连接数。此参数会影响集群的并发能力。默认值与集群规格有关，具体参见管理数据库连接章节。 max_active_statements：设置全局的最大并发数量。此参数只应用到CN，且针对一个CN上的执行作业。默认值60，最多允许60个作业同时运行，其余作业将会排队。

为什么GaussDB(DWS)使用一段时间后执行SQL很慢？ 数据库在使用一段时间后，随着业务的增加使得表数据增加，或者对表数据经常进行增、删、改之后，引发数据膨胀和统计信息不准造成性能下降。 建议对于频繁增、删、改的表，定期执行vacuum full和analyze操作。操作步骤如下： 默认每30000条数据收集100条做统计信息，当数据量大的时候，发现sql时快时慢，一般是执行计划发生了变化，统计信息的需要调整采样率。set default_statistics_target可以提高采样率，对优化器生成最优计划有所帮助。 重新执行analyze。详细信息请参见ANALYZE | ANALYSE。 若用户想要知道是否是磁盘碎片的问题影响了数据库的性能，可以使用以下函数进行查询： SELECT * FROM pgxc_get_stat_dirty_tables(30,100000); 父主题： 数据库性能

8.1.3及以上版本 8.1.3及以上版本的集群，AUTO VACUUM默认是打开的（由GUC参数autovacuum控制），用户通过设置对应的GUC参数后，系统会自动触发所有系统表和用户的行存表进行VACUUM FULL，用户不需要手动执行vacuum。 autovacuum_max_workers = 0，系统表和普通表都不会触发。 autovacuum = off，普通表不会触发，但系统表会触发。 以上仅针对行存表的AUTO VACCUM触发，如果需要针对列存表做自动触发VACUUM，还需要用户在管理控制台上配置智能调度任务。具体参见运维计划。

操作步骤 使用系统管理员dbadmin连接DWS数据库。 执行以下SQL语句创建角色role1。 1 CREATE ROLE role1 PASSWORD disable; 执行以下SQL语句，为角色role1进行授权。 1 2 3 GRANT usage ON SCHEMA s1 TO role1; --赋予SCHEMA s1的访问权限; GRANT select ON ALL TABLES IN SCHEMA s1 TO role1; --赋予SCHEMA s1下所有表的查询权限; ALTER DEFAULT PRIVILEGES FOR USER tom IN SCHEMA s1 GRANT select ON TABLES TO role1; --赋予SCHEMA s1未来创建的表的权限，其中tom为SCHEMA s1的owner 执行以下SQL语句，将角色role1授权到实际用户user1。 1 GRANT role1 TO user1; 如果访问的SCHEMA s1中包含有外表，还需要对只读用户user1进行使用外表的授权，执行以下命令。 1 ALTER USER user1 USEFT; 否则以只读用户查询外表时会报以下错误：“ERROR: permission denied to select from foreign table in security mode”。 使用只读用户user1进行SCHAMA s1下所有表数据的日常读取。

以pg_toast_temp*或pg_temp*开头的Schema是什么？ 查询Schema列表的时候，发现查询结果存在pg_temp*或pg_toast_temp*的Schema，如下图所示。 1 SELECT * FROM pg_namespace; 这些Schema是在创建临时表时，该临时表通过每个会话独立的以pg_temp开头的Schema来保证只对当前会话可见，因此，不建议用户在日常操作中手动删除以pg_temp，pg_toast_temp开头的Schema。 临时表只在当前会话可见，本会话结束后会自动删除，这些相应的Schema也会被删除。 父主题： 数据库使用

场景二：存储过程中有DDL语句，该存储过程结束前，其他节点上DDL语句执行完后的状态是idle in transaction 先创建存储过程： 1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE FUNCTION public.test_sleep() RETURNS void LANGUAGE plpgsql AS $$ BEGIN truncate t1; truncate t2; EXECUTE IMMEDIATE 'select pg_sleep(6)'; RETURN; END$$; 再执行如下命令查看PGXC_STAT_ACTIVITY视图： 1 SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack'; 查看结果显示：truncate t2处于idle in transaction状态，coorname为coordinator2。说明cn2上该语句已经执行完成，该存储过程在执行下一条语句。 解决方法：此类场景是由于存储过程执行慢导致，等存储过程执行完成即可，也可考虑优化存储过程中执行时间较长的语句。

场景一：事务开启后没有提交，语句处于idle in transaction 手动BEGIN/START TRANSACTION开启事务，执行某语句后，不执行COMMIT/ROLLBACK，此时执行如下命令查看视图PGXC_STAT_ACTIVITY： 1 SELECT state, query, query_id FROM pgxc_stat_activity; 查看结果显示：该语句状态为idle in transaction。 解决方法：这种场景下需要手动对开启的事务执行COMMIT/ROLLBACK即可。

场景三：大量SAVEPOINT/RELEASE语句处于idle in transaction（8.1.0之前集群版本） 执行如下命令查看PGXC_STAT_ACTIVITY视图： 1 SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack'; 结果显示SAVEPOINT/RELEASE语句处于idle in transaction。

权限管理 GaussDB(DWS)中的权限管理分为三种场景： 系统权限 系统权限又称为用户属性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和 LOG IN。 系统权限一般通过CREATE/ALTER ROLE语法来指定。其中，SYSADMIN权限可以通过GRANT/REVOKE ALL PRIVILEGE授予或撤销。但系统权限无法通过ROLE和USER的权限被继承，也无法授予PUBLIC。 用户权限 将一个角色或用户的权限授予一个或多个其他角色或用户。在这种情况下，每个角色或用户都可视为拥有一个或多个数据库权限的集合。 当声明了WITH ADMIN OPTION，被授权的用户可以将该权限再次授予其他角色或用户，以及撤销所有由该角色或用户继承到的权限。当授权的角色或用户发生变更或被撤销时，所有继承该角色或用户权限的用户拥有的权限都会随之发生变更。 数据库系统管理员可以给任何角色或用户授予/撤销任何权限。拥有CREATEROLE权限的角色可以赋予或者撤销任何非系统管理员角色的权限。 数据对象权限 将数据库对象（表和视图、指定字段、数据库、函数、模式等）的相关权限授予特定角色或用户。GRANT命令将数据库对象的特定权限授予一个或多个角色。这些权限会追加到已有的权限上。

USER/ROLE 用户或角色是数据库服务器(集群)全局范围内的权限控制系统，是集群业务的所有者和执行者，用于各种集群范围内所有的对象权限管理。因此角色不特定于某个单独的数据库，但角色登录集群的时候必须要显式指定登录的用户名，以保证当前连接执行的操作者的透明性。同时数据库也会通过权限管理限定用户的访问和操作权限。 用户是权限的最终体现者，所有的权限管理最终都体现在用户对数据库对象的操作权限是否被允许。