DBE_COMPRESSION

Interface 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 the 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 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);

  Table 2 DBE_COMPRESSION.GET_COMPRESSION_RATIO 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 pages occupied by sampled rows after compression.
  blkcnt_uncmp	Number of pages occupied by sampled rows before compression.
  row_cmp	Number of compressed rows on a single page.
  row_uncmp	Number of uncompressed rows on a single page.
  cmp_ratio	Compression ratio.
  comptype_str	Character string of the compression type.
  sample_ratio	Sampling rate.
  objtype	Object type. The options are as follows: 1: table object.

• DBE_COMPRESSION.GET_COMPRESSION_TYPE

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

  1 2 3 4 5 6

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

  Table 3 DBE_COMPRESSION.GET_COMPRESSION_TYPE parameters

  Parameter	Description
  ownname	Data object owner (schema to which the data object belongs).
  tablename	Data object name.
  ctid	CTID of the target row.
  subobjname	Name of a data subobject.
  comptype	Compression type. The options are as follows: 1: uncompressed. 2: advanced compression.

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 'Gauss_zzy123';
gaussdb=# CREATE user user2 IDENTIFIED BY 'Gauss_zzy123';
gaussdb=# SET ROLE user1 PASSWORD 'Gauss_zzy123';
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	    : 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