从 TPCH 测试学习性能优化技巧之 Q18

原创于 2020-04-28 10:34:30 发布 · 292 阅读

0 ·

CC 4.0 BY-SA版权

集算器专栏收录该内容

263 篇文章

订阅专栏

本文分析了TPCH基准测试中的Q18查询，探讨了Oracle SQL的执行策略，并通过SPL进行了优化，利用并行查询和视图提升查询效率，减少了执行时间。

一、查询要求

Q18语句查询获得比指定供货量大的供货商信息。可用于决定在订单量大，任务紧急时，验证是否有充足的供货商。

Q18语句的特点是：带有分组、排序、聚集、IN子查询操作并存的三表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。

二、 Oracle执行

Oracle编写的查询SQL语句如下：

select * from (

select /*+ parallel(n) */

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;

其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	1248	739	533	412	344

三、 SPL优化

分析这个查询，如果我们将下面的子查询

select l_orderkey,sum(l_quantity) lq

from lineitem

group by l_orderkey

命名为视图lo，则原查询的主体等价于：

select /*+ parallel(n) */

c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,

sum(lq)

from

customer,orders,lo

where

c_custkey = o_custkey

and o_orderkey = l_orderkey

and lq>314

group by

c_name,

c_custkey,

o_orderkey,

o_orderdate,

o_totalprice

这是一个有外键关联的表orders与其同维表lo的连接运算，而我们知道lineitem是orders的子表，也是按l_orderkey排序的，用lineitem计算出来的lo仍然可以保证按l_orderkey有序，这样可以与orders做高速归并连接。

customer作为外键表在结果集上与orders关联即可，前面运算可以不参与。

SPL脚本如下：

	A
1	=1
2	=now()
3	>quantity=314
4	=file(path+"lineitem.ctx").create().cursor@m(L_ORDERKEY,L_QUANTITY;;A1)
5	=A4.group(L_ORDERKEY;sum(L_QUANTITY):quantities).select(quantities>quantity).fetch()
6	=file(path+"orders.ctx").create()
7	=A5.joinx@q(L_ORDERKEY,A6:O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE,O_ORDERDATE)
8	=A7.groups@u(;top(100,[-O_TOTALPRICE,O_ORDERDATE],~):v).v
9	=A8.id(O_CUSTKEY)
10	=file(path+"customer.ctx").create().cursor@m(C_CUSTKEY,C_NAME;A9.contain(C_CUSTKEY);A1)
11	=A10.fetch()
12	=A8.switch(O_CUSTKEY,A11:C_CUSTKEY)
13	=A12.new(O_CUSTKEY.C_NAME:c_name,O_CUSTKEY.C_CUSTKEY:c_cuskey,L_ORDERKEY:o_orderkey,O_ORDERDATE,O_TOTALPRICE,quantities)
14	=now()
15	=interval@s(A2,A14)