课程作业

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

需要自己建表！！

CREATE TABLE t_test_row
(
r1 CHAR(2),
r2 VARCHAR2(40),
r3 NUMBER
);
--压缩属性为no

\d+ t_test_row
insert into t_test_row select r1, r2, r3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 26) + 65), 2) as  r1, repeat(chr(int4(random() * 26) + 65), 30) as  r2, (random() * (10^4))::integer as r3);


--创建列存表
CREATE TABLE t_test_column
(
c1 CHAR(2),
c2 VARCHAR2(40),
c3 NUMBER
)
WITH (ORIENTATION = COLUMN);
--压缩属性为low

\d+ t_test_column;

insert into t_test_column select * from t_test_row;

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

omm=# \d+
                                                List of relations
 Schema |         Name         | Type  | Owner |    Size    |               Storage                | 
Description 
--------+----------------------+-------+-------+------------+--------------------------------------+-
------------
 public | company              | table | omm   | 8192 bytes | {orientation=row,compression=no}     | 
 public | cur_test             | table | omm   | 8192 bytes | {orientation=row,compression=no}     | 
 public | t_test_column        | table | omm   | 1112 kB    | {orientation=column,compression=low} | 
 public | t_test_row           | table | omm   | 6760 kB    | {orientation=row,compression=no}     | 
 public | test_t1              | table | omm   | 8192 bytes | {orientation=row,compression=no}     | 
 public | test_trigger_des_tbl | table | omm   | 8192 bytes | {orientation=row,compression=no}     | 
 public | test_trigger_src_tbl | table | omm   | 8192 bytes | {orientation=row,compression=no}     | 
(7 rows)

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

analyze VERBOSE t_test_row;
analyze VERBOSE t_test_column;
-- 列存表时间少于行存表

explain analyze select distinct r1 from t_test_row;
explain analyze select distinct c1 from t_test_column;

explain analyze insert into t_test_row values('z', 'zzzz', '345');
explain analyze insert into t_test_column values('z', 'zzzz', '345');

4.清理数据

drop table t_test_row;
drop table t_test_column;