Ⅰ SQL code and analysis

Below is the SQL query statement:

select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate >= date '1995-10-01'
    and o_orderdate < date '1995-10-01' + interval '3' month
    and exists (
        select * from lineitem
        where l_orderkey = o_orderkey and l_commitdate < l_receiptdate
    )
group by
    o_orderpriority
order by
    o_orderpriority;

This code contains an exists clause for finding records meeting l_commitdate < l_receiptdate from the sub table lineitem. We can always rewrite the exists clause as a join query, as shown below:

select
    o_orderpriority,
    count(*) as order_count
from (
    select 
        distinct l_orderkey,o_orderpriority
    from orders
    join lineitem on 
        l_orderkey = o_orderkey 
    where
        o_orderdate >= date '1995-10-01'
        and o_orderdate < date '1995-10-01' + interval '3' month
        and l_commitdate < l_receiptdate
)
group by
    o_orderpriority
order by
    o_orderpriority;

The rewritten statement still focuses on the primary-sub table association. And the association operation aims to generate a filtering condition on the primary table. The intermediate result set, actually, corresponds to orders table, so we need to perform distinct operation on the association result according to orderkey.

Ⅱ SPL solution

Perform association using the order-based merge explained in essay Q3, and get the filtering condition on the primary table:

A4 and A5 use the above-mentioned technique. A5 uses news@r() to group lineitem table by L_ORDERDEY while filtering away groups that do not meet L_COMMITDATE<L_RECEIPTDATE and retaining one record in each group.

A6 groups A5’s result by O_ORDERPRIORITY and counts records in each group.

Ⅲ Further optimization

1. Optimization method

In this example, we need to use the “date-integer conversion” optimization method explained in essay Q1. The orders table’s O_ORDERDATE field is already converted in essay Q3. Here we just need to convert lineitem table’s L_COMMITDATE field and L_RECEIPTDATE field.

Another optimization method involved in the example is string-integer conversion, which will convert values of O_ORDERPRIORITY into integers.

2. Code for data conversion

2.1 Conversion on orders table

2.2 Conversion on lineitem table

A2 converts values of L_COMMITDATE field and L_RECEIPTDATE field into small integers.

3. Code after data conversion

A9 restores the integer type O_ORDERPRIORITY field to the original string type one.

Ⅳ Using enterprise edition’s column-wise computation

1. Original data

The cursor()function in A4 works with @v option to enable retrieving data using column-wise cursor. A5 uses news() function to create a column-wise, synchronously grouped cursor according to A4.

2. Optimized data

Ⅴ Test result

Unit: Second