I.   Query Requirement

  Q18 queries to obtain the information of suppliers that can supply more parts than the specified quantities, which can be used to testify whether there are sufficient suppliers for large orders and urgent tasks.

  Q18 is characterized by three tables join operations with grouping, sorting, aggregation and IN sub-query. Query statements do not grammatically limit how many tuples are returned, but the TPC-H standard stipulates that only the first 100 rows of the query results are returned (usually depending on the application).

II.  Oracle Execution

  The query SQL written in Oracle is as follows:

    select * from (
	select  /*+ parallel(n) */
	    c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,
	    sum(l_quantity)
	from
	    customer,orders,lineitem
	where
	    o_orderkey in (
		select
		    l_orderkey
		from
		    lineitem
		group by
		    l_orderkey
                having
		    sum(l_quantity) > 314
	    )
	    and c_custkey = o_custkey
	    and o_orderkey = l_orderkey
	group by
	    c_name,
	    c_custkey,
	    o_orderkey,
	    o_orderdate,
	    o_totalprice
	order by
	    o_totalprice desc,
	    o_orderdate
    ) where rownum<=100;

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

  Script execution time, Unit: seconds

III. SPL Optimization

  Analyze this query, if we name the following sub-query

    select l_orderkey,sum(l_quantity) lq
    from lineitem
    group by l_orderkey

  as view V, the main body of the original query is equivalent to：

    select  /*+ parallel(n) */
	c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,
	sum(lq)
    from
	customer,orders,lo
    where
	c_custkey = o_custkey
	and o_orderkey = l_orderkey
	and lq>314
    group by
	c_name,
	c_custkey,
	o_orderkey,
	o_orderdate,
	o_totalprice

  This is a JOIN operation between the ORDERS table, which has a foreign key association, and its homo-dimension table LO. We know that the lineitem table is a sub-table of ORDERS and ordered to l_orderkey as well, and the LO table calculated by lineitem can still ensure ordered to l_orderkey. Thus it can perform high-speed merge join with ORDERS.

  CUSTOMER table, as a foreign key table, can join with ORDERS in the result and may not participate in the previous operation.

  The SPL script is as follows：

  Script execution time, Unit: seconds