I.   Query Requirement

  Q17 queries for small qualities of orders that are lower than 20% of the average supply. This query considers parts of a specified brand and container type and determines the average quantity of such parts for all orders (past and pending) in the seven-year database. If orders that are less than 20% of the average supply are no longer taken, what is the average annual loss? So this query can be used to calculate how much the average annual revenue would be lost if there were no small-quantity orders (because the shipping of parts in large quantities would reduce management costs).

  Q17 is characterized by two tables join operations with aggregation and aggregation on sub-query.

II.  Oracle Execution

  The query SQL written in Oracle is as follows:

    select  /*+ parallel(n) */
         sum(l_extendedprice) / 7.0 as avg_yearly
    from
         lineitem,part
    where
         p_partkey = l_partkey
         and p_brand = 'Brand#33'
         and p_container = 'LG DRUM'
         and l_quantity < (
               select
                    0.2 * avg(l_quantity) 
               from
                    lineitem
               where
                    l_partkey = p_partkey
         );

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

  Script execution time, Unit: seconds

III. SPL Optimization

  The equivalent condition association with the primary table in the sub-query can be converted to JOIN to calculate, so that the optimization skill of JOIN can be utilized.

    select  /*+ parallel(n) */
         sum(l_extendedprice) / 7.0 as avg_yearly
    from
         lineitem,part,
         (select l_partkey lp, 0.2*avg(l_quantity) lq
               from lineitem
               group by l_partkey
         ) lpq
    where
         p_partkey = l_partkey
         and p_brand = 'Brand#33'
         and p_container = 'LG DRUM'
         and l_partkey=lp
    and l_quantity < lq

  This is equivalent to matching and filtering lineitem table twice with foreign key table, one of which is the intermediate table lpq calculated by the sub-query. Because it is an inner join, the partkey involved in the lpq is only in the range of the filtered PART table, so the filtered PART table can be reused.

  The SPL script is as follows： 

  Script execution time, Unit: seconds