I Query Requirement

Q7 queries the profit of sales between the supplier country and the sales country, which identifies the volume of goods shipped between the two countries to help renegotiate the freight contracts.

Q7 is characterized by multi-table query operation with grouping, sorting, aggregation and sub-query operation. The main query of the sub-query has no other query objects, and the sub-query is relatively simple in structure.

II Oracle Execution

The query SQL written in Oracle is as follows:

 select  /*+ parallel(n) */

         supp_nation,

         cust_nation,

         l_year,

         sum(volume) as revenue

from

         (

                   select

                            n1.n_name as supp_nation,

                            n2.n_name as cust_nation,

                            extract(year from l_shipdate) as l_year,

                            l_extendedprice * (1 - l_discount) as volume

                  from

                            supplier,

                            lineitem,

                            orders,

                            customer,

                            nation n1,

                            nation n2

                   where

                            s_suppkey = l_suppkey

                            and o_orderkey = l_orderkey

                            and c_custkey = o_custkey

                            and s_nationkey = n1.n_nationkey

                            and c_nationkey = n2.n_nationkey

                            and (

                                     (n1.n_name = 'CHINA' and n2.n_name = 'RUSSIA')

                                     or (n1.n_name = 'RUSSIA' and n2.n_name = 'CHINA')

                            )

                            and l_shipdate between date '1995-01-01' and date '1996-12-31'

         ) shipping

group by

         supp_nation,

         cust_nation,

         l_year

order by

         supp_nation,

         cust_nation,

         l_year;

Where /*+ 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	510	344	256	211	184

 

III SPL Optimization

The operation of the intermediate sub-query, as well as the optimization principle, is quite similar to that in Q3, which will not be repeated here.

The SPL script is as follows：

	A
1	=now()
2	1995-01-01
3	1996-12-31
4	>name1="CHINA"
5	>name2="RUSSIA"
6	=file("nation.btx").import@b().select(N_NAME==name1   || N_NAME==name2).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_CUSTKEY:A8)
10	=file("lineitem.ctx").open().news(A9,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE,O_CUSTKEY;L_SHIPDATE>=A2   && L_SHIPDATE <=A3,L_SUPPKEY:A7)
11	=A10.select(O_CUSTKEY.C_NATIONKEY!=L_SUPPKEY.S_NATIONKEY)
12	=A11.groups(    L_SUPPKEY.S_NATIONKEY.N_NAME:supp_nation,O_CUSTKEY.C_NATIONKEY.N_NAME:cust_nation,year(L_SHIPDATE):l_year;   sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):   volume)
13	return interval@ms(A1,now())

 

Note that the national table data A6 is used once in A7 and A8 respectively for the purpose of foreign key matching and filtering, which is different from the alias syntax in SQL.

 

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	510	344	256	211	184
SPL composite table	250	126	66	34	25