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.
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 |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot