openGauss每日一练第21天

课程学习

连接数据库

su - omm

gsql -r

1.创建行存表

CREATE TABLE test_t1

(

col1 CHAR(2),

col2 VARCHAR2(40),

col3 NUMBER

);

–压缩属性为no

\d+ test_t1

insert into test_t1 select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 26) + 65), 2) as  col1, repeat(chr(int4(random() * 26) + 65), 30) as  col2, (random() * (10^4))::integer as col3);

2.创建列存表

CREATE TABLE test_t2

(

col1 CHAR(2),

col2 VARCHAR2(40),

col3 NUMBER

)

WITH (ORIENTATION = COLUMN);

–压缩属性为low

\d+ test_t2;

–插入和行存表相同的数据

insert into test_t2 select * from test_t1;

3.占用空间对比

\d+

4.对比读取一列的速度

analyze VERBOSE test_t1;

analyze VERBOSE test_t2;

–列存表时间少于行存表

explain analyze select distinct col1 from test_t1;

explain analyze select distinct col1 from test_t2;

5.对比插入一行的速度

–行存表时间少于列存表

explain analyze insert into test_t1 values('x', 'xxxx', '123');

explain analyze insert into test_t2 values('x', 'xxxx', '123');

6.清理数据

drop table test_t1;

drop table test_t2;

课程作业

1.  创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)

创建行存表

Create table test1(t_id int,t_name);

Insert into test1 values(generate_series(1, 10000);

Create table test2(t_id int ,t_name) WITH (ORIENTATION = COLUMN);

Insert into test1 values(generate_series(1, 10000);

2.  对比行存表和列存表空间大小

\d+

3.  对比查询一列和插入一行的速度

analyze VERBOSE test1;

analyze VERBOSE test2;

explain analyze insert into test1 values(10001,'a');

explain analyze insert into test2 values(10001,'a');

4.清理数据

Drop table test1;
Drop table test2;

总结

今天是openGauss的打卡最后一天，从openGauss当中我学到了很多。