I Query Requirement

Q6 queries the incremental revenue generated by changing discounts in a given year. This is a typical "what-if" judgment, used to find ways to increase income. Revenue Change Predict Query takes into account all shipped orders with discounts between "DISCOUNT-0.01" and "DISCOUNT+0.01" within a specified year, and finds out the amount of total revenue increase after eliminating the discounts of orders with l_quantity less than quantity.

Q6 is characterized by a single table query operation with aggregation operation. Query statements use BETWEEN-AND operators, which can be optimized by some databases.

II Oracle Execution

The query SQL written in Oracle is as follows:

select  /*+ parallel(n) */

         sum(l_extendedprice * l_discount) as revenue

from

         lineitem

where

         l_shipdate >= date '1995-01-01'

         and l_shipdate < date '1995-01-01' + interval '1' year

         and l_discount between 0.05 - 0.01 and 0.05 + 0.01

         and l_quantity < 24;

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

Script execution time, Unit: seconds

III SPL Optimization

This is a routine aggregation query after filtering with only one record returned. There is no special optimization technique, and using multiple cursors for parallel implementation as Q1 is just enough.

The SPL script is as follows：

The technique of filtering data while creating cursors to reduce the amount of read is also used here (this technique is commonly used in SPL, which is used in later examples and will not be elaborated later).

Script execution time, Unit: seconds

The large amount of data makes the columnar storage of composite tables become an obvious advantages.