Browse Prior Art Database

Evaluation of Column Functions On Grouped Data During Data Ordering

IP.com Disclosure Number: IPCOM000100268D
Original Publication Date: 1990-Mar-01
Included in the Prior Art Database: 2005-Mar-15
Document File: 2 page(s) / 66K

Publishing Venue

IBM

Related People

Haderle, DJ: AUTHOR [+2]

Abstract

The processing of an SQL statement which uses the column functions AVG, SUM, MIN, MAX, and/or COUNT, with or without the DISTINCT attribute on one of the column functions, in conjunction with the GROUP BY clause may require that a sort be used to order the data according to the group and possibly the distinct criteria. To reduce the number of rows of data handled and the processing time required to process such a request, the column functions can be evaluated during the sort process.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Evaluation of Column Functions On Grouped Data During Data Ordering

       The processing of an SQL statement which uses the column
functions AVG, SUM, MIN, MAX, and/or COUNT, with or without the
DISTINCT attribute on one of the column functions, in conjunction
with the GROUP BY clause may require that a sort be used to order the
data according to the group and possibly the distinct criteria.  To
reduce the number of rows of data handled and the processing time
required to process such a request, the column functions can be
evaluated during the sort process.

      There are four general forms of queries upon which this
invention can be used effectively.
Case #1:  An SQL statement having:
           -    column function(s) all without DISTINCT; and
           -    GROUP BY;
           -    will observe the largest degree of IMPROVEMENT.
Case #2:  An SQL statement having:
           -    column function with DISTINCT, with or without other
column function(s);
           -    GROUP BY; and
           -    the input to the sort process preordered such that
the first column of the ordering corresponds to the column specified
in the column function with DISTINCT, and the subsequent columns of
the ordering (2 through n) correspond to the columns specified in the
GROUP BY clause in no particular order;
           -    will experience a large degree of improvement but not
to the degree of Case #1.  Additional ordering columns (n+1 through
m) may or may not exist and need not correspond to any part of the
statement.  The preordering will typically occur from either an
index, a previous...