性能优化案例课程 TPCH-Q18
select * from (
select
c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
sum(l_quantity)
from
customer, orders, lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey
having
sum(l_quantity) > 314
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
) where rownum<=100;
分析这个查询,如果将下面的子查询
select
l_orderkey, sum(l_quantity) lq
from
lineitem
group by
l_orderkey
l_orderkey
命名为视图 lo,则原查询的主体等价于:
select * from (
select
c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, lq
from
customer, orders, lo
where
c_custkey = o_custkey
and o_orderkey = l_orderkey
and lq > 314
order by
o_totalprice desc,
o_orderdate
) where rownum<=100;
视图 lo 是分组聚合运算,结果可以看成以 l_orderkey 为主键的表。这样,主查询就相当于是事实表 orders、lo 关联后,再关联维表 customer,最后计算条件过滤和排序。
1. 数据存储
orders、lineitem 按照主键有序存储,可以实现有序归并。customer 表也按照主键有序存储。
继续使用题目 Q3 中的 orders.ctx、lineitem.ctx 和 customer.ctx,复制到本题的主目录中。
2. 一般实现
orders 和 lo 都对主键 orderkey 有序,相当于同维表,可以做有序归并连接。
有序归并、汇总后取前 100 名的结果集相当于小事实表,最后和 customer 连接,要采用前面题目介绍过的,大维表查找的计算方法。
计算代码:
A |
|
1 |
=now() |
2 |
>quantity=314 |
3 |
=file("lineitem.ctx").open().cursor@m(L_ORDERKEY,L_QUANTITY) |
4 |
=A3.group@s(L_ORDERKEY;sum(L_QUANTITY):quantities).select(quantities>quantity) |
5 |
=file("orders.ctx").open().new(A4,O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE,O_ORDERDATE,quantities) |
6 |
=A5.total(top(100;-O_TOTALPRICE,O_ORDERDATE)) |
7 |
=file("customer.ctx").open() |
8 |
=A6.joinx@q(O_CUSTKEY,A7:C_CUSTKEY,C_NAME) |
9 |
=interval@ms(A1,now()) |
A8 中用前面题目介绍过的 joinx@q 函数,对 A6 结果的 O_CUSTKEY 去重,在 customer 表中批量查找。
测试结果:
测试项目 |
执行时间(秒) |
一般实现 |
9 |
需要说明的是,维表 customer 在 A7 之前的事实表有序归并计算中并不出现。A7 之后的连接则是大维表查询计算。整个过程中都不能利用维表主键序号化手段,这个题目就没有必要做数据转换方面的性能提速了。
3. 列式计算
在事实表有序归并、大维表查找基础上,实施列式计算。
计算代码:
A |
|
1 |
=now() |
2 |
>quantity=314 |
3 |
=file("lineitem.ctx").open().cursor@mv(L_ORDERKEY,L_QUANTITY) |
4 |
=A3.group@s(L_ORDERKEY;sum(L_QUANTITY):quantities).select@v(quantities>quantity) |
5 |
=file("orders.ctx").open().new(A4,O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE,O_ORDERDATE,quantities) |
6 |
=A5.total(top(100;-O_TOTALPRICE,O_ORDERDATE)) |
7 |
=file("customer.ctx").open() |
8 |
=A6.joinx@q(O_CUSTKEY,A7:C_CUSTKEY,C_NAME) |
9 |
=interval@ms(A1,now()) |
A3 得到列式游标。
A4 中 group 函数进行列式计算后,select 函数加上 @v 选项也使用列式计算,结果返回列式游标。
测试结果:
测试项目 |
执行时间(秒) |
一般实现 |
9 |
列式计算 |
2 |