I Query Requirement

Q9 is to query the total profit of all parts ordered in each country in one year.

Q9 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 is a multi-table join query in itself. LIKE operator is used in sub-queries, which may not be supported by some query optimizers.

II Oracle Execution

The query SQL written in Oracle is as follows:

 select  /*+ parallel(n) */

         nation,

         o_year,

         sum(amount) as sum_profit

from

         (

                   select

                            n_name as nation,

                            extract(year from o_orderdate) as o_year,

                            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount

                   from

                            part,

                            supplier,

                            lineitem,

                            partsupp,

                            orders,

                            nation

                   where

                            s_suppkey = l_suppkey

                            and ps_suppkey = l_suppkey

                            and ps_partkey = l_partkey

                            and p_partkey = l_partkey

                            and o_orderkey = l_orderkey

                            and s_nationkey = n_nationkey

                            and p_name like '%chocolate%'

         ) profit

group by

         nation,

         o_year

order by

         nation,

         o_year desc;

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	930	502	331	267	234

 

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	>name="chocolate"
3	=file("nation.btx").import@b().keys@i(N_NATIONKEY)
4	=file("part.ctx").open().cursor@m(P_PARTKEY, P_NAME;pos(P_NAME, name)).fetch().keys@im(P_PARTKEY)
5	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A3).fetch().keys@im(S_SUPPKEY)
6	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A4.find(PS_PARTKEY)).fetch().keys@i(PS_PARTKEY,PS_SUPPKEY)
7	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE)
8	=file("lineitem.ctx").open().news(A7,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE)
9	=A8.join@i(L_PARTKEY:L_SUPPKEY,A6,PS_SUPPLYCOST).switch(L_SUPPKEY,A5)
10	=A9.groups(L_SUPPKEY.S_NATIONKEY.N_NAME:nation,year(O_ORDERDATE):o_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit)
11	=A10.sort(nation,-o_year)
12	return interval@ms(A1,now())

 

In A9, the field PS_SUPPLYCOST of the foreign key table is joined to the lineitem cursor by the join@i method. This is a foreign key table with two field primary keys where the method of matching and converting while creating cursor can no longer be used.

 

Script execution time, Unit: seconds

Number of parallel	1	2	4	8	12
Oracle	930	502	331	267	234
SPL composite table	691	354	180	95	68