性能优化案例课程 TPCH-Q21
select * from (
select
s_name,
count(*) as numwait
from
supplier,lineitem l1,orders,nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by
s_name
order by
numwait desc,
s_name
) where rownum<=100;
主查询是主子表和外键表的连接分组聚合运算，过滤条件比较复杂，有两个 exists 子查询。
1. 数据存储
两个 exists 子查询都是针对同一个 L_ORDERKEY 的 lineitem 记录的运算。
orders 和 lineitem 要按照主键 ORDERKEY 有序存储。这两个表连接时按 ORDERKEY 有序归并，连接的结果再按 ORDERKEY 有序分组但不聚合，保持分组子集。分组子集是由 L_ORDERKEY 相同的 lineitem 记录组成的小集合。用这些小集合去计算上述那两个 exists 条件就会比较简单。
继续使用题目 Q3 中的 orders.ctx、lineitem.ctx 和 Q2 的 supplier.ctx、nation.btx，复制到本题的主目录中。
2. 一般实现
计算代码：
A |
|
1 |
=now() |
2 |
>name="CHINA" |
3 |
=file("nation.btx").import@b().select@1(N_NAME==name).N_NATIONKEY |
4 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NAME;S_NATIONKEY==A3).fetch().keys@im(S_SUPPKEY) |
5 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY;O_ORDERSTATUS=="F") |
6 |
=(lineitemFile=file("lineitem.ctx").open()).news@r(A5,L_ORDERKEY:ORDERKEY;A4.find(L_SUPPKEY)) |
7 |
=lineitemFile.news(A6:ORDERKEY,L_ORDERKEY,L_SUPPKEY,L_RECEIPTDATE,L_COMMITDATE) |
8 |
=A7.group(L_ORDERKEY) |
9 |
=A8.conj(if((t=~.select(L_RECEIPTDATE>L_COMMITDATE)) && (s=t.L_SUPPKEY) && !t.pselect(L_SUPPKEY!=s) && ~.pselect(L_SUPPKEY!=s),t,null) ) |
10 |
=A9.switch@i(L_SUPPKEY,A4) |
11 |
=A10.groups@u(L_SUPPKEY.S_NAME:s_name;count(1):numwait) |
12 |
=A11.top(100;-numwait,s_name) |
13 |
=interval@ms(A1,now()) |
A6 用子表 lineitem 连接主表 orders，过滤出满足 L_SUPPKEY 条件的 ORDERKEY。
lineitem 有多条记录和一条 orders 记录对应时，news@r 不会复制 orders 记录。
打开文件也是需要时间的，这里将打开的文件赋值给变量 lineitemFile，避免 A7 再次打开。
A7 再用 lineitem 与 A6 连接，选出需要的字段。lineitem 有多条记录和一条 orders 记录对应时，news 会复制订单记录。
A8 对 A7 做有序分组，分组子集是由 L_ORDERKEY 相同的 lineitem 记录组成的小集合。
A9 用这些小集合计算，实现相当于 SQL 中两个 exists 的条件判断。
SQL 中的 exists 子查询，相当于是要求小集合中的 L_SUPPKEY 不能都一样。not exists 则相当于要求，把小集合按照 L_RECEIPTDATE>L_COMMITDATE 条件过滤后的子集中，L_SUPPKEY 必须都一样。
要判断一个集合中 L_SUPPKEY 是不是都一样，只要判断集合成员的 L_SUPPKEY 是否都和某个指定成员的 L_SUPPKEY 相同就可以了。A9 中的 s 就是这个指定成员。
后面的代码把其它外键表关联上再做常规分组运算即可。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
21 |
3. 数据转换
本题中要使用前面题目介绍的维表主键序号化方法和日期整数化优化方法，将 Q2 中的 supplier_2.ctx 和 Q12 中的 lineitem_3.ctx 复制到本题的主目录中。
在 Q3 的 orders.ctx 基础上，把枚举型字符串字段 O_ORDERSTATUS 进行数字化转换。
转换代码：
A |
|
1 |
=file("orders.ctx").open() |
2 |
=A1.cursor(O_ORDERSTATUS) |
3 |
=A2.id(O_ORDERSTATUS).sort() |
4 |
=file("o_orderstatus.btx").export@b(A3) |
5 |
=A1.cursor().run(O_ORDERSTATUS=A3.pos@b(O_ORDERSTATUS)) |
6 |
=file("orders_21_3.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT) |
7 |
>A6.append(A5) |
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
>name="CHINA" |
|
3 |
=file("o_orderstatus.btx").import@b().(_1).(~=="F") |
|
4 |
=file("nation_2.btx").import@b().(if(N_NAME==name,N_NATIONKEY,null)) |
|
5 |
=file("supplier_2.ctx").open() |
=A5.cursor@m().skip().(null) |
6 |
=A5.cursor@m(S_SUPPKEY,S_NAME;A4(S_NATIONKEY)).fetch().(B5(S_SUPPKEY)=S_NAME) |
|
7 |
=file("orders_21_3.ctx").open().cursor@m(O_ORDERKEY;A3(O_ORDERSTATUS)) |
|
8 |
=(lineitemFile=file("lineitem_3.ctx").open()).news@r(A7,L_ORDERKEY:ORDERKEY;B5(L_SUPPKEY)) |
|
9 |
=lineitemFile.news(A8:ORDERKEY,L_ORDERKEY,L_SUPPKEY,L_COMMITDATE,L_RECEIPTDATE) |
|
10 |
=A9.group(L_ORDERKEY) |
|
11 |
=A10.conj(if((t=~.select(L_RECEIPTDATE>L_COMMITDATE)) && (s=t.#2) && !t.pselect(#2!=s) && ~.pselect(#2!=s),t.select(B5(L_SUPPKEY)),null) ) |
|
12 |
=A11.groups@u(L_SUPPKEY:s_name;count(1):numwait).run@m(s_name=B5(s_name)) |
|
13 |
=A12.top(100;-numwait,s_name) |
|
14 |
=interval@ms(A1,now()) |
|
A3、A4、B5 是对位序列。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
21 |
数据转换 |
16 |
4. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
>name="CHINA" |
|
3 |
=file("o_orderstatus.btx").import@b().(_1).(~=="F") |
|
4 |
=file("nation_2.btx").import@b().(if(N_NAME==name,N_NATIONKEY,null)) |
|
5 |
=file("supplier_2.ctx").open() |
=A5.cursor@m().skip().(null) |
6 |
=A5.cursor@m(S_SUPPKEY,S_NAME;A4(S_NATIONKEY)).fetch().(B5(S_SUPPKEY)=S_NAME) |
|
7 |
=file("orders_21_3.ctx").open().cursor@mv(O_ORDERKEY;A3(O_ORDERSTATUS)) |
|
8 |
=(lineitemFile=file("lineitem_3.ctx").open()).news@r(A7,L_ORDERKEY:ORDERKEY,min(L_SUPPKEY):SK,count(1):cnt; B5(L_SUPPKEY) && L_RECEIPTDATE>L_COMMITDATE) |
|
9 |
=lineitemFile.news@r(A8:ORDERKEY,L_ORDERKEY,SK,cnt,count(L_RECEIPTDATE>L_COMMITDATE && SK!=L_SUPPKEY):c1,count(SK!=L_SUPPKEY):c2).select@v(c1==0 && c2!=0) |
|
10 |
=A9.groups@u(SK;sum(cnt):numwait).new(B5(SK):s_name,numwait) |
|
11 |
=A10.top(100;-numwait,s_name) |
|
12 |
=interval@ms(A1,now()) |
|
列式计算不能使用临时变量，A8 中定义了计算列 SK 和 cnt。
A9 用分组子集中记录的 L_SUPPKEY 与 SK 比较，直接汇总出计算列 c1、c2。lineitem 有多条记录和一条 orders 记录对应时，news@r 会直接汇总 lineitem 记录，不会复制订单记录。然后，用 select 函数过滤 c1 和 c2 的条件。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
21 |
数据转换 |
16 |
列式计算 |
8 |
