达梦数据库——压缩

DM8压缩表

0、结论

  • 行表(普通表)不支持压缩但是语法支持。建表之后,查询到的占用空间会比普通表小一半。

    • 经过测试,装10万数据(两个字段),压缩的、未压缩,占用空间一样大。
  • 列表(huge表)支持压缩。可以压缩表(就是压缩所有列),也可以选择压缩列。但是建表的时候就要设置,否则建好表之后修改不成压缩表或压缩列。

    • 经过测试,装10万数据(两个字段),压缩级别9(最高),压缩所有字段。压缩的、未压缩的相差600MB左右。

!

-- 查询dm数据库信息(包含版本)
select * from v$instance;
-- 查询dm数据库版本
select * from v$version;
-- 查询版本时间
select id_code;

1、需求:

2、压缩表、压缩列

COMPRESS关键字

  • 建表:普通表

(见官方文档:https://eco.dameng.com/document/dm/zh-cn/pm/definition-statement.html#3.5 管理表)

  • 建表:huge表

(见官方文档:https://eco.dameng.com/document/dm/zh-cn/pm/definition-statement.html#3.5.1.3 定义 HUGE 表)

2.1、普通表(行式存储表)

  • 语法支持,功能已经取消
  • 但是设置了压缩的表,初始占用空间会变小。但是放同样的数据,大小没有区别。

  • 建表语句
-- 压缩表
create table "CS_YT1"."TABLE_3"
(
	"COLUMN_1" CHAR(10) not null ,
	"COLUMN_2" CHAR(10),
	"COLUMN_3" CHAR(10),
	primary key("COLUMN_1")
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
compress;

-- 压缩列
create table "CS_YT1"."TABLE_3"
(
	"COLUMN_1" CHAR(10) not null ,
	"COLUMN_2" CHAR(10),
	"COLUMN_3" CHAR(10),
	primary key("COLUMN_1")
)
storage(initial 1, next 1, minextents 1, fillfactor 0)
compress ("COLUMN_1","COLUMN_2","COLUMN_3");
  • 测试建表,查看大小

2.2、HUGE表——列式存储表(大表)

  • 压缩级别、压缩类型
1. <压缩级别> 指定列的压缩级别,有效值范围为:0~10,分别代表不同的压缩算法和压缩级别。有两种压缩算法:SNAPPY 和 ZIP。10 采用 SNAPPY 算法轻量级方式压缩。2~9 采用 ZIP 算法压缩,2~9 代表压缩级别,值越小表示压缩比越低、压缩速率越快;值越大表示压缩比越高、压缩速度越慢。0 和 1 为快捷使用,默认值为 0。0 等价于 LEVEL 2;1 等价于 LEVEL 9;

2. < 压缩类型 > 指定列压缩类型。FOR 'QUERY [LOW]'表示进行规则压缩;FOR 'QUERY HIGH'表示结合进行规则压缩与通用压缩结合,前者的压缩比一般在 1:1 至 1:3 之间,后者一般为 1:3 至 1:5 之间。规则压缩方式一般适用于具有一定的数据规则的数据的压缩,例如重复值较多等。若某列的类型为字符串类型且定义长度超过 48,则即使指定规则压缩也无效,实际只进行通用压缩;
  • huge表
    • 不能:新增字段、修改字段(类型、长度、精度)
    • 可以:修改字段名字、给字段加注释、给表加注释

  • 建表语句
-- 1压缩列
CREATE HUGE TABLE "CS_YT1"."T_HUGE_COMPRESS_FIELD"
(
  "ID" VARCHAR2(50) not null ,
	"NAME" VARCHAR2(50),
	primary key("ID")
) 
STORAGE(STAT ASYNCHRONOUS EXCEPT(ID), WITH DELTA, SECTION(65536), FILESIZE(64), ON "HMAIN") 
  COMPRESS ("ID" LEVEL 3 FOR 'QUERY',"NAME" LEVEL 4 FOR 'QUERY LOW') LOG LAST ;

comment on table "CS_YT1"."T_HUGE_COMPRESS_FIELD" is '压缩列';

-- 2压缩表
CREATE HUGE TABLE "CS_YT1"."T_HUGE_COMPRESS"
(
  "ID" VARCHAR2(50) not null ,
	"NAME" VARCHAR2(50),
	primary key("ID")
) 
STORAGE(STAT ASYNCHRONOUS EXCEPT(ID), WITH DELTA, SECTION(65536), FILESIZE(64), ON "HMAIN") 
COMPRESS LEVEL 3 FOR 'QUERY' LOG LAST ;

comment on table "CS_YT1"."T_HUGE_COMPRESS" is '压缩表';

-- 3不压缩的huge表
CREATE HUGE TABLE "CS_YT1"."T_HUGE_NOCOMPRESS"
(
  "ID" VARCHAR2(50) not null ,
	"NAME" VARCHAR2(50),
	primary key("ID")
) 
STORAGE(STAT ASYNCHRONOUS EXCEPT(ID), WITH DELTA, SECTION(65536), FILESIZE(64), ON "HMAIN") ;

comment on table "CS_YT1"."T_HUGE_NOCOMPRESS" is '未压缩的表';
  • 修改列为压缩列

    • DM不支持
  • 新增列为压缩列

    • 原来未压缩的表已压缩的表,都可以新增压缩列
    • 达梦版本低了不支持:DM Database Server 64 V8(1-1-172-21.03.05-135967-ENT Pack1)(我们现在用的,就不行)
    • 可以去下个最新的dm试一下。
ALTER TABLE T1 ADD COLUMN COL_TEST VARCHAR(10) COMPRESS LEVEL 9;
  • 修改表压缩表(oracle支持,dm不支持
ALTER TABLE SALES_HISTORY_COMP COMPRESS;

3、查询

  • 查询表信息
-- 查询表信息:(COMPRESSION 字段,是否被压缩):ENABLED被压缩;DISABLED未被压缩。
SELECT *
FROM DBA_TABLES
WHERE OWNER = 'CS_YT1'
--AND TABLE_NAME = 'TABLE_333333' OR TABLE_NAME = 'TABLE_1';

  • ☆☆查询所有类型的表的空间占用☆☆
-- 查询表的实际占用大小、占用表空间大小(huge表都为0)
SELECT 
TABLE_USED_PAGES('CS_YT1','T_PT') * PAGE / 1024  AS "实际大小(KB)" ,
TABLE_USED_SPACE('CS_YT1','T_PT') * PAGE / 1024  AS "占用表空间大小(KB)"

3.1、普通表

3.1.1、查询表占用大小
  • 可以使用通用查询
-- 查看表的占用大小(单表)1(模式,表名)
SELECT TABLE_USED_SPACE('CS_YT1', 'T_HUGE_COMPRESS') * PAGE() / 1024  "占用大小(KB)";
-- 查询表的占用大小(单表)2(模式,表名)
SELECT TABLE_USED_SPACE('CS_YT1', 'T_HUGE_COMPRESS') * PARA_VALUE / 1024 / 1024 "表占用(MB)"
  FROM V$DM_INI
 WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
 
 -- 查询所有表所占的大小及所在空间(所有表)
SELECT 
A.OWNER AS "模式",
A.SEGMENT_NAME AS "表名",
A.BLOCKS*2 AS "对象大小(KB)",
A.BYTES/1024 AS "占用空间(KB)",
A.TABLESPACE_NAME AS "所属表空间",
B.COMMENTS AS "表注释" 
FROM DBA_SEGMENTS A,DBA_TAB_COMMENTS B 
WHERE 
A.OWNER=B.OWNER 
AND A.SEGMENT_NAME = B.TABLE_NAME 
AND A.OWNER='CS_YT1' 
ORDER BY SEGMENT_NAME ASC;
3.1.2、查询表空间占用大小
  • 新建表后,已使用空间会变大、剩余空间会变小

https://blog.csdn.net/u011595939/article/details/131168337

-- 查看数据库文件总大小
select (select TOTAL_SIZE from V$DATABASE) *(select page())/1024/1024 as 占用大小单位MB;

-- 查看表空间使用情况
SELECT
    t.tablespace_name AS "表空间名称",
    t.total_space AS "总空间(MB)",
    t.total_space - f.free_space AS "已使用空间(MB)",
    f.free_space AS "剩余空间(MB)",
    ((t.total_space - f.free_space) / t.total_space) * 100 AS "已使用百分比"
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS total_space
    FROM
        dba_data_files
    GROUP BY
        tablespace_name) t
JOIN
    (SELECT
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS free_space
    FROM
        dba_free_space
    GROUP BY
        tablespace_name) f ON t.tablespace_name = f.tablespace_name;

-- 查询所有表空间的占用大小2
SELECT 

F.TABLESPACE_NAME AS 表空间名称,
(T.TOTAL_SPACE - F.FREE_SPACE) "使用 (MB)",
F.FREE_SPACE "剩余 (MB)",
T.TOTAL_SPACE "总大小 (MB)",
(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || '%' "使用率(使用/总)"

FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS *
(SELECT PARA_VALUE / 1024
FROM V$DM_INI
WHERE PARA_NAME =
'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES /
1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

3.2、HUGE表

3.2.1、查询表空间
-- huge表(列存储表)
-- HUGE表存储在HTS(HUGE TABLESPACE)表空间上,最多可以创建32767个HUGE表空间。默认的HUGE表空间是HMAIN。查看HUGE表空间的SQL语句如下 
select * from v$HUGE_TABLESPACE;

3.2.2、查询表空间大小

可以参考问答:https://eco.dameng.com/community/question/224bbb1e97def662c0b9a7701162cef6

-- 查询HUGE列存储空间
SELECT ID,NAME,PATHNAME
      ,ROUND(GET_DISK_SIZE(PATHNAME) / 1024/1024/1024, 2) AS "总空间(GB)"
      ,ROUND(GET_DISK_SIZE(PATHNAME) * GET_DISK_RATIO(PATHNAME) / 1024/1024/1024, 2) AS "使用(GB)"
      ,ROUND(GET_DISK_SIZE(PATHNAME) * (1 - GET_DISK_RATIO(PATHNAME)) / 1024/1024/1024, 2) AS "剩余(GB)"
FROM V$HUGE_TABLESPACE

4、测试

4.1、准备表

  • 所有设置了压缩的表,压缩率都设置为9(最大压缩率)

①T_PT: 普通表/行式表,未设置压缩

②T_PT_COMPRESS : 普通表/行式表,设置了压缩

③T_HUGE_NOCOMPRESS: huge表/列式表,未压缩

④T_HUGE_COMPRESS_FIELD:huge表/列式表,压缩一个字段

⑤T_HUGE_COMPRESS: huge表/列式表,压缩整个表(所有字段)

  • 以上表,都是两个字段:
	"ID"   VARCHAR2(50),
	"NAME" VARCHAR2(8188)
  • 查看表是否是压缩表
-- 查询表信息:(COMPRESSION 字段,是否被压缩):ENABLED被压缩;DISABLED未被压缩。
SELECT TABLE_NAME,TABLESPACE_NAME,COMPRESSION
FROM DBA_TABLES
WHERE OWNER = 'CS_YT1'
AND TABLE_NAME = 'T_PT' 
OR TABLE_NAME = 'T_PT_COMPRESS'
OR TABLE_NAME = 'T_HUGE_NOCOMPRESS'
OR TABLE_NAME = 'T_HUGE_COMPRESS_FIELD'
OR TABLE_NAME = 'T_HUGE_COMPRESS'
;

4.2、测试条件

  • 每张表循环插入10万条数据
-- 循环执行一条sql
DECLARE
    i NUMBER := 1;
BEGIN
    WHILE i <= 100000 LOOP
        -- 在这里编写你要执行的SQL语句
        -- 例如:EXECUTE IMMEDIATE 'INSERT INTO table_name VALUES (' || i || ')';

insert into "CS_YT1"."T_PT"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_PT_COMPRESS"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_HUGE_NOCOMPRESS"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_HUGE_COMPRESS_FIELD"("ID", "NAME") 
VALUES(i, 'dddd……');
insert into "CS_YT1"."T_HUGE_COMPRESS"("ID", "NAME") 
VALUES(i, 'dddd……');

        i := i + 1;
    END LOOP;
END;

  • 查看每张表数据量
select '普通表' as table_name,count(1) as 数量 from T_PT
union all
select '普通压缩表' as table_name,count(1) as 数量 from T_PT_COMPRESS
union all
select 'huge未压缩表' as table_name,count(1) as 数量 from T_HUGE_NOCOMPRESS
union all
select 'huge压缩字段表' as table_name,count(1) as 数量 from T_HUGE_COMPRESS_FIELD
union all
select 'huge压缩表' as table_name,count(1) as 数量 from T_HUGE_COMPRESS

4.3、测试结果

  • 查询每张表的占用空间大小
-- 查询表的实际占用大小、占用表空间大小
SELECT 
ROUND(TABLE_USED_PAGES('CS_YT1','T_PT') * PAGE / 1024 / 1024, 2)  AS "普通表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_PT') * PAGE / 1024 / 1024, 2)  AS "普通表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_PT_COMPRESS') * PAGE / 1024 / 1024, 2) AS "普通压缩表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_PT_COMPRESS') * PAGE / 1024 / 1024, 2) AS "普通压缩表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_HUGE_NOCOMPRESS') * PAGE / 1024/ 1024, 2) AS "huge未压缩表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_HUGE_NOCOMPRESS') * PAGE / 1024/ 1024, 2) AS "huge未压缩表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_HUGE_COMPRESS_FIELD') * PAGE / 1024/ 1024, 2) AS "huge压缩字段表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_HUGE_COMPRESS_FIELD') * PAGE / 1024/ 1024, 2) AS "huge压缩字段表-占用表空间大小(MB)",

ROUND(TABLE_USED_PAGES('CS_YT1','T_HUGE_COMPRESS') * PAGE / 1024/ 1024, 2) AS "huge压缩表-实际大小(MB)" ,
ROUND(TABLE_USED_SPACE('CS_YT1','T_HUGE_COMPRESS') * PAGE / 1024/ 1024, 2) AS "huge压缩表-占用表空间大小(MB)"
;
  • 结果

4.4、结论

  • 普通表,也不压缩占用空间都一样

  • huge表,占用空间从小到大为:

    压缩huge表(压缩所有字段) < 压缩字段huge表 < 未压缩的huge表

ps:压缩字段表、压缩表差别不大是因为未设置压缩的字段值很小,但是结果已经能说明,压缩了的表占用空间小。

5、总结

①、普通表:不支持压缩,就算建表时增加压缩关键字也没有意义。

②、huge表(列式存储表):修改huge列式存储表为压缩表的方案

  • 先修改原来表的名字(原表名A,改为B),新建压缩表A(很慢,3-8秒)
  • 将B表中的数据重新插入到压缩表A中
  • 最后删除B表

热门相关:开局签到荒古圣体   试婚100天:夜少,轻轻宠   星界游民   韩娱之影帝   苍穹龙骑