一、 查询要求
Q13语句查询获得消费者的订单数量,包括过去和现在都没有订单记录的消费者。
Q13语句的特点是:带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。
二、 Oracle执行
Oracle编写的查询SQL语句如下:
select /*+ parallel(n) */
c_count,
count(*) as custdist
from (
select
c_custkey,
count(o_orderkey) c_count
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%special%accounts%'
group by
c_custkey
) c_orders
group by
c_count
order by
custdist desc,
c_count desc;
其中/*+ parallel(n) */ 是Oracle的并行查询语法,n是并行数。
脚本执行时间,单位:秒
![eec63d35ec56c2c0053790413e2f2701.png](https://i-blog.csdnimg.cn/blog_migrate/1108573ceacf87a30926db020c1d8089.jpeg)
三、 SPL优化
这个查询简单看是对orders做两轮常规分组,第一轮按custkey分组计算出每个顾客的下单数,第二轮再按下单数分组计算出每种下单数各有多少顾客。
注意到原SQL中有个左连接,会将没有下单过的顾客(下单数为0)也统计在内,而上述二轮分组过程则会遗漏掉这些数据,需要事后再补充一下。
SPL脚本如下:
![5801a68856d50faeefa2753c225b313c.png](https://i-blog.csdnimg.cn/blog_migrate/c635240bb8d159b88037a7597d65f3f7.jpeg)
A5做第一轮分组,A7做第二轮;A8计算所有客户数,减去已下单的就是没下单的客户数,补充到A7上再一起排序。
脚本执行时间,单位:秒
![e81073da1745f7cdedd607cb71b657c5.png](https://i-blog.csdnimg.cn/blog_migrate/1598997e40edf70cef4fcdd4d7576394.jpeg)
这个查询SPL没有跑过Oracle,绝大部分时间消耗在第一轮分组(A5)。主要原因在于这里的分组结果集较大,会占用很多内存,而SPL目前还是Java开发,JVM对内存的管理较差,占用内存较多时就会频繁发起垃圾收集动作,消耗很多时间。而C++开发的数据库则没有这个问题。
四、 进一步优化
SPL中groups函数在分组时,如果分组字段是序号,那么可以用@n选项直接定位,避免hash计算。本例中第一轮按O_CUSTKEY分组的,而在数据表中O_CUSTKEY都是整数,可以尝试@n选项。但本例中O_CUSTKEY的值较大,也就是分组数多,占用内存大,并行线程多时如果每个线程中都分一个大组,内存将不够用,所以并行数多时,要减少groups@n的并行数。本例中使用的方法是并行数小于3时就用原并行数,大于3时就用它整除4所得的商。
SPL脚本如下:
![72dde4187c7695add6c60ad57dae1453.png](https://i-blog.csdnimg.cn/blog_migrate/f85c6798951bbb9389b8d03b4398f1ac.jpeg)
脚本执行时间,单位:秒
![1178c67b3f96954e9b361fcd938598f4.png](https://i-blog.csdnimg.cn/blog_migrate/b5d7c5608ab9dabcd1e06a1104501692.jpeg)
测试结果可见,效率确实有所提高,在低并行时性能超过了Oracle,高并行时因为内存占用过大导致垃圾收集时间消耗过多而又被Oracle反超。