文档首页/ 数据仓库服务 GaussDB(DWS)/ 常见问题/ 数据库使用/ GaussDB(DWS)列存表的常用信息查询
更新时间：2024-06-21 GMT+08:00
查看PDF
分享

GaussDB(DWS)列存表的常用信息查询

使用列存表时，一些常用信息查询SQL示例：

先创建列存分区表my_table，并向表中插入数据。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE TABLE my_table
(
    product_id INT,
    product_name VARCHAR2(40),
    product_quantity INT
 )
WITH (ORIENTATION = COLUMN)
PARTITION BY range(product_quantity)
(
partition my_table_p1 values less than(600),
partition my_table_p2 values less than(800),
partition my_table_p3 values less than(950),
partition my_table_p4 values less than(1000));
 
INSERT INTO my_table VALUES(1011, 'tents', 720);
INSERT INTO my_table VALUES(1012, 'hammock', 890);
INSERT INTO my_table VALUES(1013, 'compass', 210);
INSERT INTO my_table VALUES(1014, 'telescope', 490);
INSERT INTO my_table VALUES(1015, 'flashlight', 990);
INSERT INTO my_table VALUES(1016, 'ropes', 890);

查看已创建的列存分区表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT * FROM my_table;
 product_id | product_name | product_quantity
------------+--------------+------------------
       1013 | compass      |              210
       1014 | telescope    |              490
       1011 | tents        |              720
       1015 | flashlight   |              990
       1012 | hammock      |              890
       1016 | ropes        |              890
(6 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
2
3
4
5
 
SELECT count(*) FROM ALL_TAB_COLUMNS where table_name='my_table';
 count
-------
     3
(1 row)

查询数据在各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)
父主题： 数据库使用