一、 問題背景與適用場(chǎng)景
通常分組計(jì)算都采用hash方案，即先計(jì)算分組字段的hash值，hash值相同的記錄被分揀到一個(gè)小集合里，然后在這個(gè)小集合中遍歷找分組字段值相同的聚合成一組。分組的復(fù)雜度(比較次數(shù))，取決于hash函數(shù)的重碼率。在hash空間比較小時(shí)，重碼率就高，比較次數(shù)就會(huì)多，性能會(huì)受較大影響。為了提高性能，就需要分配較大的內(nèi)存來存放 hash 表。另外，有些數(shù)據(jù)類型（長(zhǎng)字串）的 hash 計(jì)算也比較慢，這也會(huì)影響性能。
如果分組字段是有序的，在分組的時(shí)候，每條記錄只與上一條記錄比較，發(fā)現(xiàn)有不同時(shí)則新建一個(gè)分組，相同則聚合到當(dāng)前組中。這樣的分組運(yùn)算的復(fù)雜度為n(被分組集合的長(zhǎng)度)，而且沒有 hash 計(jì)算和重碼率的問題，可以獲得比 hash 分組更快的性能，而且并不需要太多內(nèi)存用于存放 hash 表。
SPL提供了這種分組方法，我們實(shí)例測(cè)試一下，并且與使用hash分組算法的Oracle對(duì)比。
二、 測(cè)試環(huán)境
測(cè)試機(jī)有兩個(gè)Intel2670 CPU，主頻2.6G，共16核，內(nèi)存64G，SSD固態(tài)硬盤。在此機(jī)上安裝虛擬機(jī)來測(cè)試，設(shè)置虛擬機(jī)為16核、8G內(nèi)存。
三、 小數(shù)據(jù)量小結(jié)果集測(cè)試
在虛擬機(jī)上創(chuàng)建數(shù)據(jù)表orderdetail_1，共三個(gè)字段：orderid(整數(shù))、detailid(整數(shù))、amount(實(shí)數(shù))，前兩個(gè)字段是主鍵，生成數(shù)據(jù)記錄8千萬(wàn)行。將此表數(shù)據(jù)導(dǎo)入Oracle數(shù)據(jù)庫(kù)，同時(shí)用它生成集算器SPL組表來進(jìn)行測(cè)試。
orderid字段數(shù)據(jù)升序排列，按orderid進(jìn)行分組，共有50組，統(tǒng)計(jì)每張訂單的總金額和明細(xì)條數(shù)。
1. Oracle測(cè)試
編寫查詢測(cè)試SQL如下：
select /*+ parallel(n) */
orderid, sum(amount) as amount, count(detailid) as details
from orderdetail_1
group by orderid;
其中/*+ parallel(n) */ 用于并行測(cè)試，n為并行數(shù)。
2. SPL測(cè)試
編寫SPL腳本如下：
groups分組時(shí)加選項(xiàng)@o就適用分組字段有序時(shí)，只比較相鄰行的值進(jìn)行有序分組。
3. 測(cè)試結(jié)果
測(cè)試結(jié)果如下，單位(秒)：
在8千萬(wàn)行數(shù)據(jù)的情況下，SPL有序分組的性能提高了一倍左右，并且并行的效果非常好，性能呈線性上升。而使用hash分組的Oracle并行提速效果并不明顯。
性能提高程序與數(shù)據(jù)量有關(guān)，當(dāng)數(shù)據(jù)量很小時(shí)，分組時(shí)間占整個(gè)查詢時(shí)間的比例很小，對(duì)整體性能的提高也就不明顯。但隨著數(shù)據(jù)量的增加，提升效果就會(huì)越來越顯著。
下面我們?cè)賮砜纯创髷?shù)據(jù)量測(cè)試的情況。
四、 大數(shù)據(jù)量大結(jié)果集測(cè)試
在虛擬機(jī)上創(chuàng)建數(shù)據(jù)表orderdetail_2，共三個(gè)字段：orderid(字符串)、detailid(整數(shù))、amount(實(shí)數(shù))，前兩個(gè)字段是主鍵，生成數(shù)據(jù)記錄24億行。將此表數(shù)據(jù)導(dǎo)入Oracle數(shù)據(jù)庫(kù)，同時(shí)用它生成集算器SPL組表來進(jìn)行測(cè)試。
orderid字段數(shù)據(jù)升序排列，按orderid進(jìn)行分組，共有8億組，統(tǒng)計(jì)每張訂單的總金額和明細(xì)條數(shù)。由于查詢出的大結(jié)果集在Oracle輸出需要很長(zhǎng)的時(shí)間，所以對(duì)分組結(jié)果再進(jìn)行一次過濾，只輸出訂單總金額小于35元的訂單，結(jié)果只有12條，輸出就幾乎不占時(shí)間了。
1. Oracle測(cè)試
編寫查詢測(cè)試SQL如下：
select * from (
select /*+ parallel(n) */
orderid, sum(amount) sum_amount, count(detailid) as details
from orderdetail_2
group by orderid
)
where sum_amount<35;
其中/*+ parallel(n) */ 用于并行測(cè)試，n為并行數(shù)。
2. SPL測(cè)試
編寫SPL腳本如下：
由于分組結(jié)果集很大，無法全部裝載到內(nèi)存，所以使用group函數(shù)進(jìn)行有序分組，返回分組結(jié)果集對(duì)應(yīng)的游標(biāo)，再對(duì)游標(biāo)過濾后取得需要的查詢結(jié)果。
3. 測(cè)試結(jié)果
測(cè)試結(jié)果如下，單位(秒)：
在不并行的情況下，SPL有序分組比Oracle性能提升了近6倍左右。因SPL有序分組方法很適合并行，隨著并行數(shù)的增加，性能提升的效果就越好。
