一浸间、???? 查詢要求
Q11語句是查詢庫存中某個國家供應的零件的價值套蒂。
Q11語句的特點是：帶有分組钞支、排序、聚集操刀、子查詢操作并存的多表連接查詢操作烁挟。子查詢位于分組操作的HAVING條件中。
二骨坑、???? Oracle執(zhí)行
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是并行數(shù)。
腳本執(zhí)行時間欢唾，單位：秒
并行數(shù)? ? ? ? ? ? ?1? ? ? ? ? ? 2? ? ? ? ? ?4? ? ? ? ? ?8? ? ? ? ? 12
Oracle? ? ? ? ? ? 71? ? ? ? ? ?52? ? ? ? ? 43? ? ? ? ?36? ? ? ? 33
三且警、???? SPL優(yōu)化
我們把下面的子查詢看成為視圖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已經(jīng)是經(jīng)過分組后的結(jié)果集，數(shù)量較小礁遣，遍歷V比直接針對partsupp遍歷的計算量要小很多斑芜。
???????? 而我們還知道，partsupp表是按主鍵ps_partkey,ps_suppkey有序的祟霍，也就是對ps_partkey有序押搪，這時候針對該字段的分組可以使用有序分組方案，以提高計算V的性能浅碾。
SPL腳本如下：
A
1=1
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)
A10用groups@o執(zhí)行有序分組大州，相當于計算出視圖V，然后A11,A12對A10進行兩次遍歷計算出結(jié)果垂谢。
腳本執(zhí)行時間厦画，單位：秒
并行數(shù)? ? ? ? ?1? ? ? ? ? ? 2? ? ? ? ? 4? ? ? ? ? ? 8? ? ? ? ? ? ?12
Oracle? ? ? ? 71? ? ? ? ? ?52? ? ? ? ? 43? ? ? ? ?36? ? ? ? ? ?33
SPL組表? ? ?24? ? ? ? ? ?15? ? ? ? ? ?9? ? ? ? ? ? 6? ? ? ? ? ? 5
