Ⅰ SQL code and analysis

Below is the SQL query statement:

select * from (
    select
        c_custkey,c_name,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        c_acctbal,n_name,c_address,c_phone,c_comment
    from
        customer,orders,lineitem,nation
    where
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate >= date '1993-05-01'
        and o_orderdate < date '1993-05-01' + interval '3' month
        and l_returnflag = 'R'
        and c_nationkey = n_nationkey
    group by
        c_custkey,
        c_name,
        c_acctbal,
        c_phone,
        n_name,
        c_address,
        c_comment
    order by
        revenue desc
) where rownum <=20;

This is a grouping & aggregation operation on the filtered result set of multi-table association (which involves a primary-sub table association between orders table and lineitem table). The target is to sort records by grouped aggregates and get the first 20 ones.

Ⅱ SPL solution

The working principle is similar to that in Q3. In the SQL statement, there are 7 fields in group by clause. In SPL, we just need to perform grouping by the first field.

First, we perform grouping & aggregation on the result set of associating orders table and lineitem table; then based on the grouping & aggregation result set we perform the externally correlated foreign key table association. Since the target is to get 20 records, we can only perform the second association on the 20 records. There is no need to handle it before the grouping operation because computation will become more intensive that way.

After A7’s computation and as customer table is ordered by c_custkey field, we use A7.joinx@q to match records of customer table in order and get eligible ones quickly. Then we handle the other joins without the need to traverse customer table and with less data retrieved.

Ⅲ Further optimization

1. Optimization method

In this example, we will use the date-integer conversion optimization method explained in Q1 – orders table’s O_ORDERDATE field is already converted in Q3, the string-integer conversion method explained in Q1, where lineitem table’s L_RETRUNFLAG is already converted, and the dimension table primary key numberization method explained in Q2 – nation table’s N_NATIONKEY field, customer table’s C_CUSTKEY field and C_NATIONKEY field, orders table’s O_CUSTKEY field are already converted in the previous examples.

2. Code for data conversion

Copy nation_9.btx, customer_8.ctx, orders_9.ctx and lineitem_9.ctx, and rename them respectively as nation_10.btx, customer_10.ctx, orders_10.ctx andlineitem_10.ctx.

3. Code after data conversion

First, we need to preload dimension tables. Below is preloading code:

Before performing the query, we need to first run the preloading code to load small dimension tables into memory.

Computing code:

Ⅳ Using enterprise edition’s column-wise computation

1. Original data

2. Optimized data

First, we need to preload dimension tables. Below is preloading code:

Before performing the query, we need to first run the preloading code to load small dimension tables into memory.

Computing code:

Ⅴ Test result

Unit: Second