Updated on 2025-05-29 GMT+08:00

DBE_COMPRESSION

API Description

Evaluates the sampling compression rate of a specified data object or obtains the compression type of a specified row of data based on the input parameters.

Table 1 DBE_COMPRESSION

API

Description

GET_COMPRESSION_RATIO

Evaluates the sampling compression rate of a specified data object based on input parameters.

GET_COMPRESSION_TYPE

Obtains the compression type of data in a specified row based on input parameters.

  • DBE_COMPRESSION.GET_COMPRESSION_RATIO

    Evaluates the sampling compression rate of a specified data object based on input parameters. The prototype is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    DBE_COMPRESSION.GET_COMPRESSION_RATIO(
    SCRATCHTBSNAME IN VARCHAR2,
    OWNNAME        IN VARCHAR2,
    OBJNAME        IN VARCHAR2,
    SUBOBJNAME     IN VARCHAR2,
    COMPTYPE       IN NUMBER,
    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);
    
    Table 2 DBE_COMPRESSION.GET_COMPRESSION_RATIO API parameters

    Parameter

    Description

    SCRATCHTBSNAME

    Tablespace to which a data object belongs.

    OWNNAME

    Data object owner (schema to which the data object belongs).

    OBJNAME

    Data object name.

    SUBOBJNAME

    Name of a data subobject.

    COMPTYPE

    Compression type. The options are as follows:
    • 1: uncompressed.
    • 2: advanced compression.

    BLKCNT_CMP

    Number of blocks occupied by compressed samples.

    BLKCNT_UNCMP

    Number of blocks occupied by uncompressed samples.

    ROW_CMP

    Number of rows that can be contained in a single block after samples are compressed.

    ROW_UNCMP

    Number of rows that can be contained in a single block when samples are not compressed.

    CMP_RATIO

    Compression ratio, which is obtained by dividing BLKCNT_UNCMP by BLKCNT_CMP.

    COMPTYPE_STR

    Character string that describes the compression type.

    SAMPLE_RATIO

    Sampling ratio. The value is an integer or floating-point number ranging from 0 to 100, corresponding to the sampling ratio of n percent. The default value is 20, indicating that 20% of the rows are sampled.

    OBJTYPE

    Object type. The options are as follows:
    • 1: table object.
  • DBE_COMPRESSION.GET_COMPRESSION_TYPE

    This API is used to obtain the compression type of a specified row based on input parameters. This API is an O&M API and does not check the visibility. That is, if the input CTID is a deleted row, this API still returns the latest status of the current row on the page. The prototype is as follows:

    1
    2
    3
    4
    5
    DBE_COMPRESSION.GET_COMPRESSION_TYPE(
    OWNNAME    IN VARCHAR2,
    TABNAME    IN VARCHAR2,
    CTID       IN TEXT,
    SUBOBJNAME IN VARCHAR2 DEFAULT NULL);
    
    Table 3 DBE_COMPRESSION.GET_COMPRESSION_TYPE API parameters

    Parameter

    Description

    OWNNAME

    Data object owner (schema to which the data object belongs).

    TABNAME

    Data object name.

    CTID

    CTID of the target row.

    SUBOBJNAME

    Name of a data subobject.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
gaussdb=# alter database set ilm = on;
gaussdb=# CREATE user user1 IDENTIFIED BY '********';
gaussdb=# CREATE user user2 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;
INSERT INTO TEST_DATA VALUES (1,'Snack gift basket A', NOW());

gaussdb=# SELECT DBE_COMPRESSION.GET_COMPRESSION_TYPE('user1', 'test_data', '(0,1)', NULL);
 get_compression_type 
----------------------
                    1
(1 row)

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	    : 1
INFO:  Number of blocks used by the uncompressed sample of the object	    : 1
INFO:  Number of rows in a block in compressed sample of the object	    : 1
INFO:  Number of rows in a block in uncompressed sample of the object	    : 1
INFO:  Estimated Compression Ratio of Sample                       	    : 1.0
INFO:  Compression Type							                        : Compress Advanced