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.

• 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