Browse Prior Art Database

Improving the Performance of Non-Correlated Subqueries in SQL

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

Publishing Venue

IBM

Related People

Couch, BT: AUTHOR [+2]

Abstract

An algorithm is disclosed whereby some subqueries of the SQL language that return a set of values can be evaluated more efficiently by reducing the set of values to a single one. The algorithm disclosed applies to subqueries that meet the following conditions: (1) subqueries must be non-correlated, (2) the comparison type between the subquery and its outer query must be a range comparison of either >, >=, <, or <=, and (3) the comparison must have a quantifier of either ANY or ALL.

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

Improving the Performance of Non-Correlated Subqueries in SQL

       An algorithm is disclosed whereby some subqueries of the
SQL language that return a set of values can be evaluated more
efficiently by reducing the set of values to a single one. The
algorithm disclosed applies to subqueries that meet the following
conditions:
      (1)  subqueries must be non-correlated,
      (2)  the comparison type between the subquery and its outer
query must be a range comparison of either >, >=, <, or <=, and
      (3)  the comparison must have a quantifier of either ANY or
ALL.

      The following example will be used to describe the subquery
processing:
      SELECT    *
      FROM      EMP
      WHERE     SALARY > ANY (SELECT AVG (SALARY)
                FROM EMP
                GROUP BY DEPTNO);

      The SQL statement returns all rows where the salary is above
any one of the departments' average salaries.

      According to prior methods, the relevant portion of the
statement would be evaluated as follows: The subquery is evaluated
first and all subquery results (in the example above, the average
salary of each department) are sorted in either ascending or
descending order depending on the comparison and quantifier
combination (in the example, ascending). The sorted subquery results
are stored in a temporary workfile.
      For each row of the outer query, a subquery result is retrieved
from the workfile to perform the comparison until the predicate
outcome is determined or the workfile is empty.

      The disclosed algorithm evaluates such subqueries more
efficiently, as follows.
      The subquery is evaluated first and the subquery results are
reduced to either a single maximum or minimum value depending on the
quantifier and comparison type combination as illustrated by the
following matrix:
      The single value is obtained by keeping only the maximum or
minimum value each time a new subquery result is obtained.
      The process of saving a single maximum or minimum subquery
result during the subquery processing eliminates the need for the
temporary workfile and associated sort required by the prior-art
method.
 ...