openGauss每日一练第21天|课后作业

课程作业

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

CREATE TABLE test_t1
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
);

-创建列存表

CREATE TABLE test_t2
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
)
WITH (ORIENTATION = COLUMN);

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);
insert into test_t2 select * from test_t1;

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

\d+

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

analyze VERBOSE test_t1;
analyze VERBOSE test_t2;

explain analyze select distinct col1 from test_t1;
explain analyze select distinct col1 from test_t2;

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

4.清理数据

drop table test_t1;
drop table test_t2;

总结
成功打卡完成，嘿嘿嘿嘿嘿。
这学期学习数据库原理课程时接触到了opengauss数据库，打开了我数据库学习的大门。通过这次打卡活动让我巩固基础的同时也学习到了许多的新知识，非常地开心。最后也祝愿opengauss数据库越做越好，越来越牛！
2021.12.25