Ⅰ SQL code and analysis

Below is the SQL query statement:

select
    l_shipmode,
    sum(case
        when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1
        else 0 end) as high_line_count,
    sum(case
        when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1
        else 0 end) as low_line_count
from
    orders,
    lineitem
where
    o_orderkey = l_orderkey
    and l_shipmode in ('TRUCK', 'MAIL')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1995-01-01'
    and l_receiptdate < date '1995-01-01' + interval '1' year
group by
    l_shipmode
order by
    l_shipmode;

This is a grouping & aggregation operation on the filtered result set of association between primary table orders and sub table lineitem.

Ⅱ SPL solution

The optimization principle for the primary-sub table association between orders and lineitem is similar to that in Q9.

In the above SQL code, conditions for high_line_count and low_line_count are opposite. One can be computed from the other using a simpler formula.

Ⅲ Further optimization

1. Optimization method

In this example, we will use the date-integer conversion method explained in Q1 – lineitem table’s L_SHIPDATE field, L_COMMITDATE field and L_RECEIPTDATE have been converted in the previous examples, and the string-integer conversion method explained in the same essay – orders table’s O_ORDERPRIORITY is already converted. Here we just need to convert lineitem table’s L_SHIPMODE field.

2. Code for data conversion

2.1 Conversion on orders table

Copy orders_10.ctx and rename it orders_12.ctx.

2.2 Conversion on lineitem table

3. Code after data conversion

Computing code:

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

Ⅳ Using enterprise edition’s column-wise computation

1. Original data

2. Optimized data

Ⅴ Test result

Unit: Second