GaussDB-DBE_COMPRESSION

jerrywang1983
78 阅读3分钟

GaussDB-DBE_COMPRESSION

接口介绍

根据输入的参数，评估指定数据对象的采样压缩率或者获取指定行数据的压缩类型。

接口名称描述
GET_COMPRESSION_RATIO根据输入参数评估指定数据对象的采样压缩率。
GET_COMPRESSION_TYPE根据输入参数获取指定行数据的压缩类型。

GET_COMPRESSION_TYPE接口只支持分布式数据库中数据节点（DN），其他节点暂不支持。

  • DBE_COMPRESSION.GET_COMPRESSION_RATIO

    根据输入参数评估指定数据对象的采样压缩率，原型为：

    | ``` DBE_COMPRESSION.GET_COMPRESSION_RATIO ( scratchtbsname IN TEXT, ownname IN TEXT, objname IN TEXT, subobjname IN TEXT, comptype IN INTEGER, blkcnt_cmp OUT INTEGER, blkcnt_uncmp OUT INTEGER, row_cmp OUT INTEGER, row_uncmp OUT INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2, sample_ratio IN NUMBER DEFAULT 20, objtype IN INTEGER DEFAULT 1);

    | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

| 参数             | 描述                            |
| :------------- | :---------------------------- |
| scratchtbsname | 数据对象所属表空间。                    |
| ownname        | 数据对象所有者（所属模式）。                |
| objname        | 数据对象名称。                       |
| subobjname     | 数据子对象名称。                      |
| comptype       | 压缩类型，支持：-   1：未压缩
-   2：高级压缩。 |
| blkcnt_cmp     | 采样行行压缩后占页面数。                  |
| blkcnt_uncmp   | 采样行未压缩时占页面数。                  |
| row_cmp        | 单页面容纳压缩行数。                    |
| row_uncmp      | 单页面容纳非压缩行数量。                  |
| cmp_ratio      | 压缩率。                          |
| comptype_str   | 压缩类型字符串。                      |
| sample_ratio   | 采样率。                          |
| objtype        | 对象类型，支持：-   1：表对象。            |

  • DBE_COMPRESSION.GET_COMPRESSION_TYPE

    根据输入参数获取指定行数据的压缩类型，该接口属于运维类接口，不做可见性判断，即传入的ctid为已删除的行时，该接口依然会返回当前行在页面上最新的状态，原型为：

    | ``` DBE_COMPRESSION.GET_COMPRESSION_TYPE ( ownname IN TEXT, tablename IN TEXT, ctid IN INTEGER, subobjname IN TEXT DEFAULT NULL, comptype OUT INTEGER);

    | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

| 参数         | 描述                            |
| :--------- | :---------------------------- |
| ownname    | 数据对象所有者（所属模式）。                |
| tablename  | 数据对象名称。                       |
| ctid       | 目标行ctid。                      |
| subobjname | 数据子对象名称。                      |
| comptype   | 压缩类型，支持：-   1：未压缩
-   2：高级压缩。 |
示例

| ``` gaussdb=# CREATE DATABASE ilmtabledb WITH dbcompatibility = 'ORA'; gaussdb=# \c ilmtabledb gaussdb=# ALTER DATABASE set ilm = on; gaussdb=# CREATE user user1 IDENTIFIED BY ''; gaussdb=# SET ROLE user1 PASSWORD ''; gaussdb=# CREATE TABLE TEST_DATA (ORDER_ID INT, GOODS_NAME TEXT, CREATE_TIME TIMESTAMP) with (storage_type=astore) ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION; NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'order_id' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. gaussdb=# INSERT INTO TEST_DATA VALUES (1, '零食大礼包A', NOW()); gaussdb=# DECLARE o_blkcnt_cmp integer; o_blkcnt_uncmp integer; o_row_cmp integer; o_row_uncmp integer; o_cmp_ratio number; o_comptype_str varchar2; begin dbe_compression.get_compression_ratio( SCRATCHTBSNAME => NULL, OWNNAME => 'user1', OBJNAME => 'test_data', SUBOBJNAME => NULL, COMPTYPE => 2, BLKCNT_CMP => o_blkcnt_cmp, BLKCNT_UNCMP => o_blkcnt_uncmp, ROW_CMP => o_row_cmp, ROW_UNCMP => o_row_uncmp, CMP_RATIO => o_cmp_ratio, COMPTYPE_STR => o_comptype_str, SAMPLE_RATIO => 100, OBJTYPE => 1); RAISE INFO 'Number of blocks used by the compressed sample of the object : %', o_blkcnt_cmp; RAISE INFO 'Number of blocks used by the uncompressed sample of the object : %', o_blkcnt_uncmp; RAISE INFO 'Number of rows in a block in compressed sample of the object : %', o_row_cmp; RAISE INFO 'Number of rows in a block in uncompressed sample of the object : %', o_row_uncmp; RAISE INFO 'Estimated Compression Ratio of Sample : %', o_cmp_ratio; RAISE INFO 'Compression Type : %', o_comptype_str; end; / INFO: Number of blocks used by the compressed sample of the object : 0 INFO: Number of blocks used by the uncompressed sample of the object : 0 INFO: Number of rows in a block in compressed sample of the object : 0 INFO: Number of rows in a block in uncompressed sample of the object : 0 INFO: Estimated Compression Ratio of Sample : 1 INFO: Compression Type : Compress Advanced gaussdb=# CREATE DATABASE ilmtabledb WITH dbcompatibility = 'ORA'; gaussdb=# \c ilmtabledb gaussdb=# ALTER DATABASE set ilm = on; gaussdb=# CREATE USER user1 IDENTIFIED BY ''; gaussdb=# SET ROLE user1 PASSWORD ''; gaussdb=# CREATE TABLE TEST_DATA (ORDER_ID INT, GOODS_NAME TEXT, CREATE_TIME TIMESTAMP) ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION; gaussdb=# INSERT INTO TEST_DATA VALUES (1, '零食大礼包A', NOW()); gaussdb=# SELECT DBE_COMPRESSION.GET_COMPRESSION_TYPE('user1', 'test_data', '(0,1)', NULL); get_compression_type ---------------------- 1

| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

更多详情请参考GaussDB 文档中心：<https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html>
avatar
文章
阅读
粉丝