Learn performance optimization skills from TPCH tests - Q6

 

Query Requirement

The Q6 statement 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. Predicted Revenue Change Query takes into account all orders that have been shipped between "DISCOUNT-0.01" and "DISCOUNT+0.01" discounts in a specified year, and finds out the amount of total revenue increase after eliminating discounts for orders with l_quantity less than quantity.

Q6 statement is characterized by a single table query operation with aggregation operation. Query statements use BETWEEN-AND operators, and some databases can optimize BETWEEN-AND.

II Oracle Implementation

The query SQL statements written by Oracle are 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 Oracle's parallel query syntax, and n is the parallel number.

Script execution time, Unit: seconds

Number of parallel

1

2

4

8

12

Oracle

328

240

183

154

135

 

III SPL optimization

This is a routine aggregation query after filtering. The result set has only one record. There is no special optimization technique. Like Q1, it can make full use of parallel by using multiple cursors.

SPL script is as follows


A

1

=1

2

=now()

3

>date=date("1995-01-01")

4

>discount=0.05

5

>quantity=24

6

=elapse@y(date,1)

7

=file(path+"lineitem.ctx").create().cursor@m(L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=date && L_SHIPDATE < A6 && L_DISCOUNT >= discount - 0.01 && L_DISCOUNT <= discount + 0.01 && L_QUANTITY < quantity;A1)

8

=A7.groups(;sum(L_EXTENDEDPRICE*L_DISCOUNT):revenue)

9

=now()

10

=interval@s(A2,A9)

The technique of data filtering when creating cursors to reduce the amount of reads 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

Number of parallel

1

2

4

8

12

Oracle

328

240

183

154

135

SPL group table

110

58

34

21

12

Because of the large amount of data, the advantages of column storage of group tables are obvious.