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

润乾软件

于 2020-04-28 10:12:46 发布

阅读量173

点赞数

CC 4.0 BY-SA版权

分类专栏：集算器

本文链接：https://blog.youkuaiyun.com/raqsoft/article/details/105807232

集算器专栏收录该内容

263 篇文章

订阅专栏

本文分析了TPCH查询Q11的执行过程，首先介绍了查询要求，展示Oracle SQL的执行方式及并行查询优化。然后通过将子查询转化为视图，并利用有序分组优化，在SPL中实现更高效的解决方案，显著减少了执行时间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、查询要求

Q11语句是查询库存中某个国家供应的零件的价值。

Q11语句的特点是：带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。

二、 Oracle执行

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

select /*+ parallel(n) */

ps_partkey,

sum(ps_supplycost * ps_availqty) as value

from

partsupp,

supplier,

nation

where

ps_suppkey = s_suppkey

and s_nationkey = n_nationkey

and n_name = 'CHINA'

group by

ps_partkey

having

sum(ps_supplycost * ps_availqty) > (

select

sum(ps_supplycost * ps_availqty) * 0.000001

from

partsupp,

supplier,

nation

where

ps_suppkey = s_suppkey

and s_nationkey = n_nationkey

and n_name = 'CHINA'

)

order by

value desc;

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	71	52	43	36	33

三、 SPL优化

我们把下面的子查询看成为视图V：

select

ps_partkey,

sum(ps_supplycost * ps_availqty) as value

from

partsupp,

supplier,

nation

where

ps_suppkey = s_suppkey

and s_nationkey = n_nationkey

and n_name = 'CHINA'

则原主体查询等价于

select

ps_partkey,

value

from V

where value>0.000001*(select sum(value) from V)

这个V已经是经过分组后的结果集，数量较小，遍历V比直接针对partsupp遍历的计算量要小很多。

而我们还知道，partsupp表是按主键ps_partkey,ps_suppkey有序的，也就是对ps_partkey有序，这时候针对该字段的分组可以使用有序分组方案，以提高计算V的性能。

SPL脚本如下：

	A
1	=1
2	=now()
3	>name="CHINA"
4	>percent=0.000001
5	=file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME;N_NAME == name).fetch().keys@i(N_NATIONKEY)
6	=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY;A5.find(S_NATIONKEY);A1)
7	=A6.fetch().keys@i(S_SUPPKEY)
8	=file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A7.find(PS_SUPPKEY);A1)
9	=A8.fetch()
10	=A9.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST * PS_AVAILQTY):value)
11	=A10.sum(value) * percent
12	=A10.select(value > A11)
13	=A12.sort@o(value:-1)
14	=now()
15	=interval@s(A2,A14)