一、???? 查詢要求
Q4語句查詢得到訂單優(yōu)先級統(tǒng)計值。計算給定的某三個月的訂單的數(shù)量，在每個訂單中至少有一行由顧客在它的提交日期之后收到。
Q4語句的特點是：帶有分組、排序、聚集操作、子查詢并存的單表查詢操作。子查詢是相關(guān)子查詢。
二、???? Oracle執(zhí)行
Oracle編寫的查詢SQL語句如下：
select? /*+ parallel(n) */
???????? o_orderpriority,
???????? count(*) as order_count
from
???????? orders
where
???????? o_orderdate >= date '1995-10-01'
???????? and o_orderdate < date '1995-10-01' + interval '3' month
???????? and exists (
?????????????????? select * from lineitem
?????????????????? where l_orderkey = o_orderkey and l_commitdate < l_receiptdate
???????? )
group by
???????? o_orderpriority
order by
???????? o_orderpriority;
其中/*+ parallel(n) */ 是Oracle的并行查詢語法，n是并行數(shù)。
腳本執(zhí)行時間，單位：秒
并行數(shù)124812
Oracle504311270189207
三、???? SPL優(yōu)化
分析SQL語句，它有一個exists子查詢，找出子表lineitem中有滿足條件l_commitdate < l_receiptdate的記錄，對應(yīng)主表orders的記錄。
exists子句都可以改寫成join。事實上，SQL在實現(xiàn)時一般也是將exists轉(zhuǎn)換成join來計算，否則，如果按exists的計算邏輯，又會導致N*M的復雜度（N和M分別是主子表的記錄數(shù)），這種性能完全不能接受。
將exists改寫成join之后是這樣：
select? /*+ parallel(n) */
?????? o_orderpriority,
?????? count(*) as order_count
from (
?????? select distinct l_orderkey,o_orderpriority from
?????? orders,lineitem
?????? where
????????????? o_orderdate >= date '1995-10-01'
????????????? and o_orderdate < date '1995-10-01' + interval '3' month
????????????? and l_orderkey = o_orderkey
????????????? and l_commitdate < l_receiptdate
)
group by
?????? o_orderpriority
order by
?????? o_orderpriority;
改寫之后可以發(fā)現(xiàn)，這仍然是個主子表連接的問題，而且這里的連接運算是為了生成針對主表的某種過濾條件，中間結(jié)果集實際上是和orders表對應(yīng)的，而一般主子連接的結(jié)果集是和子表對應(yīng)的，所以還要對連接結(jié)果做針對orderkey的去重運算。
SPL一方面可以采用前面Q3所說的有序歸并方法實現(xiàn)連接，另一方面還可以利用結(jié)果集的有序性（連接結(jié)果集對orderkey有序），快速實現(xiàn)去重運算。
SPL腳本如下：
A
1=1
2=now()
3>date=date("1995-10-01")
4=elapse@m(date,3)
5=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=date ? && O_ORDERDATE < A4;A1)
6=file(path+"lineitem.ctx").create().news(A5,L_ORDERKEY,L_COMMITDATE,L_RECEIPTDATE,O_ORDERPRIORITY; ? L_COMMITDATE < L_RECEIPTDATE)
7=A6.group@1(L_ORDERKEY).groups(O_ORDERPRIORITY;count(1):order_count)
8=now()
9=interval@s(A2,A8)
A5,A6使用了前面解釋過的技巧。A7中的group@1即表示針對有序游標實現(xiàn)去重（僅分組后取第一條記錄）。
腳本執(zhí)行時間，單位：秒
并行數(shù)124812
Oracle504311270189207
SPL組表16081412420
并行性能仍然表現(xiàn)出色。
四、???? 進一步優(yōu)化
上面分析過程說過，這里的連接運算本質(zhì)上是為了過濾主表，我們還可以依照這樣思路優(yōu)化：把子表看成主表的集合字段，直接計算聚合值并用作過濾條件。這里可以把統(tǒng)計同一個L_ORDERKEY下滿足條件L_COMMITDATE < L_RECEIPTDATE的記錄數(shù)，如果等于0則將主表相應(yīng)記錄過濾掉，這樣將大大減少了連接結(jié)果集的記錄數(shù)，把分組去重的操作換成了效率更高的過濾操作，還能進一步提高性能。
SPL腳本如下：
A
1=1
2=now()
3>date=date("1995-10-01")
4=elapse@m(date,3)
5=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=date ? && O_ORDERDATE < A4;A1)
6=file(path+"lineitem.ctx").create().new(A5,O_ORDERPRIORITY,count():c;L_COMMITDATE ? < L_RECEIPTDATE).select(c>0)
7=A6.groups(O_ORDERPRIORITY;count(1):order_count)
8=now()
9=interval@s(A2,A8)
在A6的new函數(shù)中計算滿足條件的子表記錄數(shù)量，并過濾保留大于0的。
腳本執(zhí)行時間，單位：秒
并行數(shù)124812
Oracle504311270189207
SPL組表16081412420
優(yōu)化后15378392115
類似地，這個運算也很容易并行。
