Browse Prior Art Database

Evaluation of Quantified Predicates With Non-Correlated Subqueries

IP.com Disclosure Number: IPCOM000036831D
Original Publication Date: 1989-Nov-01
Included in the Prior Art Database: 2005-Jan-29
Document File: 4 page(s) / 15K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+3]

Abstract

This invention provides a method to evaluate certain predicates that involve subqueries without having to access the subquery or a temporary table containing the subquery. Background

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

Page 1 of 4

Evaluation of Quantified Predicates With Non-Correlated Subqueries

This invention provides a method to evaluate certain predicates that involve subqueries without having to access the subquery or a temporary table containing the subquery. Background

In a relational database management system, a predicate (a condition that specifies which rows of a table are to be retrieved) can contain a subquery. That is, data that is to be selected from a table is used in determining the result of the predicate. For example, a query could request the rows of the "employee" table for which the salary column is greater than every value in the salary column of the managers' rows in the "employee" table: select * from employee where

salary > all (select salary from employee where

job='Manager')

A predicate that uses the keyword ALL or ANY (or SOME which means the same thing as ANY) before a subquery is called a quantified predicate.

A straightforward method of evaluating a quantifed predicate would be to compare the value to the left of the comparison operator with each value from the subquery. If ALL is used in the predicate, the comparisons would continue until a false comparison was found -- then the predicate would be false. If all the comparisons were true, the predicate would be true. If none were false, but one or more were unknown (a comparison involved a null value), the predicate is unknown. If ANY is used, the comparisons would continue until a true comparison was found -- then the predicate would be true. If all the comparisons were false, the predicate would be false. If none were true, but one or more were unknown, the predicate is unknown.

This straightforward method can be very inefficient. It could require many accesses of the subquery for every evaluation of the predicate. The Invention

If a quantified predicate uses one of the comparison operators <, <=, >, or >=, and if the subquery is not correlated to the same level of the query as the predicate is used in, it can be evaluated when just the following information about the subquery result is known:
1. whether any non-null values were in the subquery,
2. whether any null values were in the subquery, and
3. if non-null values were found, what the highest or

lowest value was.

If the predicate is <ANY, <=ANY, >ALL, or >=ALL, the highest value is needed. If the predicate is >ANY, >=ANY, <ALL, or <=ALL, the lowest value is needed.

If a quantified predicate is =ALL or <>ANY, the following information about the subquery result is needed:
1. whether any non-null values were in the subquery,

1

Page 2 of 4

2. whether any null values were in the subquery,
3. if non-null values were found, whether there is more

than one unique value, and
4. the value, if only one value was found.

All of this information can be computed when the query is opened and saved in pre-allocated memory. Following is the pseudocode for computing this information: Reset all flags

Set the subquery result value (SRV) to...