I Query Requirement

Q8 queries the change in market share of a given part type in a certain area of a country over the past two years.

Q8 is characterized by query operations with grouping, sorting, aggregation and sub-query operations. The main query of the sub-query has no other query objects. The sub-query is relatively simple in structure and a multi-table join query itself.

II Oracle Execution

The query SQL written in Oracle is as follows：

select  /*+ parallel(n) */

         o_year,

         sum(case

                   when nation = 'CHINA' then volume

                   else 0

         end)/ sum(volume) as mkt_share

from

         (

                   select

                            extract(year from o_orderdate) as o_year,

                            l_extendedprice * (1 - l_discount) as volume,

                            n2.n_name as nation

                   from

                            part,

                            supplier,

                            lineitem,

                            orders,

                            customer,

                            nation n1,

                            nation n2,

                            region

                   where

                            p_partkey = l_partkey

                            and s_suppkey = l_suppkey

                            and l_orderkey = o_orderkey

                            and o_custkey = c_custkey

                            and c_nationkey = n1.n_nationkey

                            and n1.n_regionkey = r_regionkey

                            and r_name = 'ASIA'

                            and s_nationkey = n2.n_nationkey

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

                            and p_type = 'STANDARD POLISHED BRASS'

         ) all_nations

group by

         o_year

order by

         o_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	472	362	277	216	192

 

III SPL Optimization

The optimization principle of JOIN between orders and lineitem primary-sub tables here is similar to that in Q3.

 The SPL script is as follows：

	A
1	=now()
2	1995-01-01
3	1996-12-31
4	>nation="CHINA"
5	>name="ASIA"
6	>type="STANDARD POLISHED   BRASS"
7	=file("region.btx").import@b().select(R_NAME==name).derive@o().keys@i(R_REGIONKEY)
8	=file("nation.btx").import@b().select(N_NAME==nation).switch@i(N_REGIONKEY,A7).derive@o().keys@i(N_NATIONKEY)
9	=file("nation.btx").import@b().switch@i(N_REGIONKEY,A7).derive@o().keys@i(N_NATIONKEY)
10	=file("supplier.ctx").open().cursor@m(S_SUPPKEY;A8.find(S_NATIONKEY)).fetch().keys@im(S_SUPPKEY)
11	=file("part.ctx").open().cursor@m(P_PARTKEY;P_TYPE==type).fetch().keys@im(P_PARTKEY)
12	=file("customer.ctx").open().cursor@m(C_CUSTKEY;A9.find(C_NATIONKEY)).fetch().keys@im(C_CUSTKEY)
13	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE;O_ORDERDATE>=A2   && O_ORDERDATE <=A3 && A12.find(O_CUSTKEY))
14	=file("lineitem.ctx").open().news(A13,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE;A11.find(L_PARTKEY))
15	=A14.switch(L_SUPPKEY,A10)
16	=A15.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT))
17	=A16.groups(  year(O_ORDERDATE):o_year;sum(if(L_SUPPKEY,L_EXTENDEDPRICE,0)):s1,sum(L_EXTENDEDPRICE):s2)
18	=A17.new(o_year,s1/s2:mkt_share)
19	return interval@ms(A1,now())

 

The nation table has been used twice as a foreign key table, being generated in A8 and A9 respectively.  

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	472	362	277	216	192
SPL composite table	315	162	92	46	37