GaussDB(DWS) 列存表一些常用信息查询使用

列存表一些常用信息查询使用

• 列存表一些常用信息查询SQL示例

• 建列存分区表

CREATE TABLE column_table

(

    state_ID INT,

    state_NAME VARCHAR2(40),

    area_ID INT,

    ready BOOLEAN

)

WITH (ORIENTATION = COLUMN)

PARTITION BY range(area_ID)

(

partition column_table_p1 values less than(60),

partition column_table_p2 values less than(80),

partition column_table_p3 values less than(95),

partition column_table_p4 values less than(100));

insert into column_table values(1, 'Hong', 72);

insert into column_table values(2, 'Eason', 89);

insert into column_table values(3, 'Jolin', 21);

insert into column_table values(4, 'Lily', 49);

insert into column_table values(5, 'Sinco', 99);

insert into column_table values(6, 'Rain', 89);

• 查询分区边界

select relname, partstrategy, boundaries from pg_partition where parentid=(select parentid from pg_partition where relname='column_table');

     relname     | partstrategy | boundaries

-----------------+--------------+------------

 column_table_p1 | r            | {60}

 column_table_p2 | r            | {80}

 column_table_p3 | r            | {95}

 column_table_p4 | r            | {100}

 column_table    | r            |

(5 rows)

• 查询列存表列数

select count(*) from ALL_TAB_COLUMNS where table_name='column_table';

 count

-------

     4

(1 row)

• 查询数据在各DN分布

select table_skewness('column_table');

           table_skewness

------------------------------------

 ("dn_6009_6010        ",3,50.000%)

 ("dn_6003_6004        ",1,16.667%)

 ("dn_6005_6006        ",1,16.667%)

 ("dn_6007_6008        ",1,16.667%)

 ("dn_6001_6002        ",0,0.000%)

 ("dn_6011_6012        ",0,0.000%)

(6 rows)

• 查询某一有数据分布DN上分区P1所对应的cudesc和delta表名称

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=''column_table_p1''';

       relname

----------------------

 pg_cudesc_part_16455

 pg_delta_part_16455

(2 rows)

• 查询某一有数据分布DN上分区P1所对应的cudesc数据

连接该DN

select * from cstore.pg_cudesc_part_16455;

 col_id | cu_id | min  | max  | row_count | cu_mode |    size    | cu_pointer | magic  | extra

--------+-------+------+------+-----------+---------+------------+------------+--------+-------

      1 |  1001 | \x04 | \x04 |         1 |       3 |          0 |            | 210126 |

      2 |  1001 | Lily | Lily |         1 |       3 |          0 |            | 210126 |

      3 |  1001 | 1    | 1    |         1 |       3 |          0 |            | 210126 |

      4 |  1001 |      |      |         1 |       2 |          0 |            | 210126 |

    -10 |  1001 |      |      |         1 |         | 1625474927 |            | 210126 |

(5 rows)

• 查询cudesc存储位置

select * from pg_relation_filepath('cstore.pg_cudesc_part_16455');

 pg_relation_filepath

----------------------

 base/16393/16459

(1 row)