I Query Requirement

Q5 queries the statistics of the revenue (calculated by sum (l_extended price * (1-l_discount)) obtained from the suppliers of parts in a certain area, which can be used to determine whether a local distribution center needs to be established in the given area.

Q5 is characterized by multi-table join query operation with grouping, sorting and aggregation.

II Oracle Execution

The query SQL written in Oracle is as follows:

select /*+ parallel(n) */

         n_name,

         sum(l_extendedprice * (1 - l_discount)) as revenue

from

         customer,

         orders,

         lineitem,

         supplier,

         nation,

         region

where

         c_custkey = o_custkey

         and l_orderkey = o_orderkey

         and l_suppkey = s_suppkey

         and c_nationkey = s_nationkey

         and s_nationkey = n_nationkey

         and n_regionkey = r_regionkey

         and r_name = 'ASIA'

         and o_orderdate >= date '1995-01-01'

         and o_orderdate < date '1995-01-01' + interval '1' year

group by

         n_name

order by

         revenue desc;

/*+ parallel(n) */ is the parallel query syntax of Oracle, and n is the parallel number.

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	672	368	301	224	225

III SPL Optimization

The optimization principle is similar to Q3 except for more foreign key tables involved.

The SPL script is as follows：

	A
1	=now()
2	1995-1-1
3	=elapse@y(A2,1)
4	>name="ASIA"
5	=file("region.btx").import@b().select(R_NAME==name).derive@o().keys@i(R_REGIONKEY)
6	=file("nation.btx").import@b().switch@i(N_REGIONKEY,A5).derive@o().keys@i(N_NATIONKEY)
7	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A6).fetch().keys@im(S_SUPPKEY)
8	=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY:A6).fetch().keys@im(C_CUSTKEY)
9	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE <A3,O_CUSTKEY:A8)
10	=file("lineitem.ctx").open().news(A9,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A7)
11	=A10.select(O_CUSTKEY.C_NATIONKEY==L_SUPPKEY.S_NATIONKEY)
12	=A11.groups@u(L_SUPPKEY.S_NATIONKEY.N_NAME;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
13	=A12.sort@z(revenue)
14	return interval@ms(A1,now())

The techniques mentioned in the previous query are used extensively here, which are filtering while creating cursor and converting join fields to pointers of foreign key table respectively.

However, unlike Q3, the field used for grouping in the last is not an ordered L_ORDERKEY, so groups@o can no longer be used.

 

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	672	368	301	224	225
SPL composite table	353	177	91	49	34