xugu数据库tpch测试

〇〇

文档中说
3.7 整理表
当系统运行一段时间后，数据库中各个表的数据的物理结构将会变得越来越混乱，
引起混乱的原因多种多样，主要的原因大致为：删除操作造成无效记录积累、更改操作
造成各个数据块中记录分布不平衡，且还会产生过多的溢出记录片断，更改记录以及插
入新记录造成数据的相关性聚集程度下降等等。没有任何数据库能自动保持其数据物理
布局始终保持在最佳状态，经过长时间运行后对数据进行重新整理总是需要的。

---
但Oracle基本上不需要整理

〇〇

时间太久，不适合用这种方法收集统计，而没有统计，查询时间又很长
SQL> VACUUM VERBOSE ANALYZE PARTSUPP

Use time:530588 ms.
ok.


SQL> -- 3
2    ;

Use time:0 ms.
Error:[-19132]  语法错误


SQL> select
2            l_orderkey,
3            sum(l_extendedprice * (1 - l_discount)) as revenue,
4            o_orderdate,
5            o_shippriority
6    from
7            customer,
8            orders,
9            lineitem
10   where
11           c_mktsegment = 'HOUSEHOLD'
12           and c_custkey = o_custkey
13           and l_orderkey = o_orderkey
14           and o_orderdate < date '1995-03-09'
15           and l_shipdate > date '1995-03-09'
16   group by
17           l_orderkey,
18           o_orderdate,
19           o_shippriority
20   order by
21           revenue desc,
22           o_orderdate
23   limit 10
24   ;

Use time:307383 ms.

LINEITEM.L_ORDERKEY(INTEGER)  | REVENUE(NUMERIC(38,6))  | ORDERS.O_ORDERDATE(DATE)  | ORDERS.O_SHIPPRIORITY(INTEGER)  |


-------------------------------------L1-------------------------------------
21679558 | 451192.3341 | 1995-01-17 AD | 0 |

-------------------------------------L2-------------------------------------
57978848 | 450250.4648 | 1995-02-19 AD | 0 |

-------------------------------------L3-------------------------------------
24295076 | 437186.7777 | 1995-01-05 AD | 0 |

-------------------------------------L4-------------------------------------
23679299 | 428116.4351 | 1995-02-28 AD | 0 |

-------------------------------------L5-------------------------------------
492164 | 423270.2092 | 1995-02-19 AD | 0 |

-------------------------------------L6-------------------------------------
35312039 | 422403.532 | 1995-03-05 AD | 0 |

-------------------------------------L7-------------------------------------
820962 | 418407.3101 | 1995-02-11 AD | 0 |

-------------------------------------L8-------------------------------------
26731940 | 417950.7043 | 1995-02-27 AD | 0 |

-------------------------------------L9-------------------------------------
36065633 | 417908.8665 | 1995-02-19 AD | 0 |

-------------------------------------L10-------------------------------------
51262022 | 413274.3434 | 1995-02-11 AD | 0 |

Total 10 records.


SQL>

〇〇

sql文档居然没有介绍察看计划的explain命令，被我胡测出来了
SQL> explain select 1 from dual;

Use time:16 ms.

plan_path(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
<NVL> |

Total 1 records.


SQL> explain
2    select
3            l_orderkey,
4            sum(l_extendedprice * (1 - l_discount)) as revenue,
5            o_orderdate,
6            o_shippriority
7    from
8            customer,
9            orders,
10           lineitem
11   where
12           c_mktsegment = 'HOUSEHOLD'
13           and c_custkey = o_custkey
14           and l_orderkey = o_orderkey
15           and o_orderdate < date '1995-03-09'
16           and l_shipdate > date '1995-03-09'
17   group by
18           l_orderkey,
19           o_orderdate,
20           o_shippriority
21   order by
22           revenue desc,
23           o_orderdate
24   limit 10
25   ;

Use time:0 ms.

plan_path(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
Sort[cost=321852777,result_num=2400000]
   (HashGroup[cost=269052777,result_num=2400000]
     (HashJoin[cost=259452777,result_num=2400000]
        (SeqScan[cost=59986052,result_num=23994421](table=LINEITEM),
        HashJoin[cost=53100100,result_num=600000]
          (SeqScan[cost=15000000,result_num=6000000](table=ORDERS),
          SeqScan[cost=1500000,result_num=150000](table=CUSTOMER))))) |

Total 1 records.


SQL>

〇〇

普通的not in/not exists还是不错的
SQL> truncate table a;

Use time:16 ms.
ok.


SQL> truncate table b;

Use time:0 ms.
ok.


SQL> exec ptest(1000000)

Use time:13644 ms.
ok.


SQL> create or replace procedure ptesta (n integer ) IS
2    i integer := 0;
3    num integer:=0;
4    begin
5    for i in 1..n loop
6    num := num+1;
7    insert into a(id,name) values(num,'a'||num);
8    end loop;
9    commit;
10   end;

Use time:16 ms.
ok.


SQL>

SQL> exec ptesta(1000000)

Use time:19943 ms.
ok.


SQL> VACUUM VERBOSE ANALYZE a;

Use time:15 ms.
ok.


SQL> VACUUM VERBOSE ANALYZE b;

Use time:31 ms.
ok.


SQL> explain select max(a.name)from a where a.id not in(select b.id from b where b.id<5000);

Use time:0 ms.

plan_path(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
Aggr[cost=13000000,result_num=60000]
   (SeqScan[cost=1000000,result_num=600000](table=A)) |

Total 1 records.


SQL> explain select max(a.name)from a where a.id not in(select b.id from b where b.id<50000);

Use time:0 ms.

plan_path(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
Aggr[cost=13000000,result_num=60000]
   (SeqScan[cost=1000000,result_num=600000](table=A)) |

Total 1 records.


SQL> explain select max(a.name)from a where not exists(select 1 from b where b.id<50000 and a.id=b.id);

Use time:0 ms.

plan_path(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
Aggr[cost=13000000,result_num=60000]
   (SubLink[cost=1000000,result_num=600000]
     (SeqScan[cost=1000000,result_num=600000](table=A)
     SeqScan[cost=1000000,result_num=400000](table=B))) |

Total 1 records.


SQL> select max(a.name)from a where a.id not in(select b.id from b where b.id<50000);

Use time:594 ms.

EXPR1(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
a999999 |

Total 1 records.


SQL> select max(a.name)from a where not exists(select 1 from b where b.id<50000 and a.id=b.id);

Use time:437 ms.

EXPR1(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
a999999 |

Total 1 records.


SQL>

〇〇

反向也很快
SQL> select max(a.name)from a where not exists(select 1 from b where b.id>50000 and a.id=b.id);

Use time:453 ms.

EXPR1(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
a9999 |

Total 1 records.


SQL> select max(a.name)from a where a.id not in(select b.id from b where b.id>50000);

Use time:531 ms.

EXPR1(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
a9999 |

Total 1 records.

〇〇

加上NULL的判断也还很快，以前oracle都很慢。。。
SQL> alter table b alter column a drop not null;

Use time:15 ms.
Error:[-5025]  存在对表(B)依赖的对象


SQL> drop procedure ptest;

Use time:0 ms.
ok.



SQL> alter table b alter column id drop not null;

Use time:0 ms.
ok.


SQL> insert into b(id,info) values(NULL,'a');

Use time:0 ms.
Total 1 records inserted.


SQL> commit;

Use time:0 ms.
ok.


SQL> select max(a.name)from a where a.id not in(select b.id from b where b.id>50000);

Use time:610 ms.

EXPR1(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
a9999 |

Total 1 records.


SQL> select max(a.name)from a where a.id not in(select b.id from b where b.id>50000 or b.id is null);

Use time:594 ms.

EXPR1(VARCHAR(-1))  |

-------------------------------------L1-------------------------------------
<NVL> |

Total 1 records.


SQL>

〇〇

还有外部表，这个不多见
但实验不成功
SQL> CREATE TABLE FILE_TABLE(
2    F_KEY integer,
3    F_NAME char(25),
4    F_COMMENT varchar(152))
5    ORGANIZATION EXTERNAL
6    (
7    TYPE text
8    DEFAULT DIR '/data2'
9    ACCESS PARAMETERS
10   (
11   RECORDS DELIMITED BY NEWLINE
12   FIELDS TERMINATED BY '|'
13   )
14   LOCATION 'FILE_TABLE.tbl'
15   );

Use time:0 ms.
Error:[-3057]  文件路径(/data2/FILE_TABLE.tbl)错误

〇〇

第2种方式倒是不抱错，但表存在哪里？
SQL> CREATE TABLE SYSDBA.SYS_BACKUP_LOG2
2    (
3    DB_NAME VARCHAR(32),
4    PLAN_NAME VARCHAR(32),
5    ITEM_NO INTEGER,
6    START_T DATETIME,
7    SUCCESS BOOLEAN
8    )
9    ORGANIZATION EXTERNAL
10   (
11   TYPE ROW
12   DEFAULT DIR '/SYSLOG'
13   LOCATION 'BACKUP_LOG.DAT'
14   );

Use time:16 ms.
ok.


SQL> select * from SYS_BACKUP_LOG2;

Use time:0 ms.

SYS_BACKUP_LOG2.DB_NAME(VARCHAR(32))  | SYS_BACKUP_LOG2.PLAN_NAME(VARCHAR(32))  | SYS_BACKUP_LOG2.ITEM_NO(INTEGER)  | SY
S_BACKUP_LOG2.START_T(DATETIME)  | SYS_BACKUP_LOG2.SUCCESS(BOOLEAN)  |

Total 0 records.

〇〇

第2种插入数据后找到了文件
SQL> insert into sys_backup_log2 values(1,2,3,NULL,NULL);

Use time:0 ms.
Total 1 records inserted.
C:\Program Files\xugu5.2\XuguServer32\的 'SYSLOG'目录下
是一个二进制文件