Browse Prior Art Database

Performance enhancement for ‘FETCH FIRST N ROWS ONLY’ used in conjunction with CUBE or ROLLUP operation

IP.com Disclosure Number: IPCOM000246911D
Publication Date: 2016-Jul-14
Document File: 5 page(s) / 98K

Publishing Venue

The IP.com Prior Art Database

Abstract

Performance of Cube/RollUp operation used in conjunction with 'Fetch First N Rows Only' and ORDER BY clause could be optimized by: 1. Scope values of GROUP BY keys which could generate top N rows according to ORDER BY clause prior to actual Rollup/Cube operation. 1.1 During above process, values of super GROUPING SET is materialized for subsequent children GROUPING SET reuse to accelerate execution speed. 2. Then use GROUP BY keys identified from step #1 to filter out unnecessary rows to avoid redundant GROUP BY or aggregate functions calculation..

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 57% of the total text.

Page 01 of 5

Performance enhancement for 'FETCH FIRST N ROWS ONLY' used in conjunction with CUBE or ROLLUP operation

Cube and Rollup operations are widely used in business intelligence application to perform complex aggregate calculation from multiple dimensions at different level of granularities. Meanwhile, 'FETCH FIRST N ROWS ONLY' and ORDER BY clause are always used in conjunction Cube or Rollup operation to return rows with specific number (N rows). There would be a performance issue when multiple aggregate/SET functions are used, since aggregate/SET functions are costly and time are wasted on processing the rows which are not in FIRST N ROWS but are involved in the calculation of aggregate/SET functions.

Here, a new way is introduced to enhance the performance of an Structured Query Language (SQL) with above pattern (containing clause: FETCH FIRST N ROWS ONLY, ORDER BY and Cube/Rollup operation). First of all, find out those values of GROUP BY key which could generate top N rows according to ORDER BY clause after Rollup operation. Then use these values to filter out unnecessary row within each GROUPING SET to avoid redundant calculation against aggregate/SET functions.

1. Detect the Structured Query Language (SQL) pattern to check whether it contains clause FETCH FIRST N ROWS ONLY, ORDER BY and Cube/Rollup operation.

2. Estimate cost against the original Structured Query Language (SQL). Assume the result is: cost#1.

3. Estimate cost against the Structured Query Language (SQL) which is applied the following method. Assume the result is: cost#2.

a) First of all, find out those values of GROUP BY key which could generate top N rows according to ORDER BY clause after Rollup/Cube operation. During these process, parent GROUPING SET could be materialized for subsequent children GROUPING SET reuse to accelerate execution speed.

b) Then use these values to filter out unnecessary rows to avoid redundant calculation, and proceed to do aggregate function calculation, under specific GROUP SET key values.


4. Make comparison between cost#1 and cost#2, and choose the better plan.

5. Generate run-time structure according to above better plan, and execute the Structured Query Language (SQL) in terms of the run-time structure.

1



Page 02 of 5

2



Page 03 of 5

Example:

SELECT T.C1 + 1, AVG (T.C3),
SUM (T.C4 + T.C5), STDDEV (T.C6), COVARIANCE (T.C7) …...