示例 测试用例

示例

测试用例 数据压缩是当前数据库采用的主要技术。数据类型不同，适用于它的压缩算法不同。对于相同类型的数据，其数据特征不同，采用不同的压缩算法达到的效果也不相同。自适应压缩正是从数据类型和数据特征出发，采用相应的压缩算法，实现了良好的压缩比、快速的入库性能以及良好的查询性能。

-- 创建列存表。
openGauss=# CRETAE TABLE compress_low
(
    ID            INT PRIMARY KEY     NOT NULL,
    NAME          TEXT                NOT NULL,
    AGE           INT                 NOT NULL,
    ADDRESS       CHAR(50)                    ,
    SALARY        REAL
) with (orientation=column);

openGauss=# INSERT INTO compress_low 
openGauss-# SELECT select generate_series(1, 10000) as key,repeat( chr(int4(random()*26)+65),4), 
openGauss-# (random()*(6^2))::integer,null,(random()*(10^4))::integer;

-- 创建列存表，并设置COMPRESSION为NO。
openGauss=# CRETAE TABLE compress_no
(
    ID            INT PRIMARY KEY     NOT NULL,
    NAME          TEXT                NOT NULL,
    AGE           INT                 NOT NULL,
    ADDRESS       CHAR(50)                    ,
    SALARY        REAL
) with (orientation=column, compression=no);

openGauss=# INSERT INTO compress_no 
openGauss-# SELECT select generate_series(1, 10000) as key,repeat( chr(int4(random()*26)+65),4), 
openGauss-# (random()*(6^2))::integer,null,(random()*(10^4))::integer;

-- 创建列存表，并设置COMPRESSION为middle。
openGauss=# CRETAE TABLE compress_mid
(
    ID            INT PRIMARY KEY     NOT NULL,
    NAME          TEXT                NOT NULL,
    AGE           INT                 NOT NULL,
    ADDRESS       CHAR(50)                    ,
    SALARY        REAL
) with (orientation=column, compression=middle);

openGauss=# INSERT INTO compress_mid 
openGauss-# SELECT select generate_series(1, 10000) as key,repeat( chr(int4(random()*26)+65),4), 
openGauss-# (random()*(6^2))::integer,null,(random()*(10^4))::integer;

-- 创建列存表，并设置COMPRESSION为high。
openGauss=# CRETAE TABLE compress_high
(
    ID            INT PRIMARY KEY     NOT NULL,
    NAME          TEXT                NOT NULL,
    AGE           INT                 NOT NULL,
    ADDRESS       CHAR(50)                    ,
    SALARY        REAL
) with (orientation=column, compression=high);

openGauss=# INSERT INTO compress_high 
openGauss-# SELECT select generate_series(1, 10000) as key,repeat( chr(int4(random()*26)+65),4), 
openGauss-# (random()*(6^2))::integer,null,(random()*(10^4))::integer;

-- 查询表大小。
openGauss=# select pg_size_pretty(pg_relation_size('compress_low')) as size;
  size
--------
 152 kB
 (1 row)

openGauss=# select pg_size_pretty(pg_relation_size('compress_no')) as size;
  size
--------
 240 kB
 (1 row)

openGauss=# select pg_size_pretty(pg_relation_size('compress_mid')) as size;
  size
--------
 128 kB
 (1 row)

openGauss=# select pg_size_pretty(pg_relation_size('compress_high')) as size;
  size
--------
 112 kB
 (1 row)

**