Optimization of correlated SQL queries in a relational database

Details Optimization of correlated SQL queries in a relational database Optimization of correlated SQL queries in a relational database

1997-06-30

1998-10-13

Kulik, Paul V.

Data processing: database and file management or data structures

Database design

Data structure types

707 3, G06F 1730

Patent

active

058227504

ABSTRACT:
A relational database management system (RDBMS) efficiently evaluates correlated subqueries by decorrelating them and taking care of the so-called SQL count bug and yet avoid using the expensive outer join operation. When there is no tuple match from a correlated subquery, the RDBMS query processor returns a tuple of null(s) from a scalar derived table, and then uses COALESCE function to generate a proper count value of zero from the null. The correlation level remains one. The query processor also performs a pass-through optimization to eliminate a join operation for floating SELECT operation by removing a join operation involving the magic operation, so that the correlation bindings are received from the correlation source table rather than the magic operation.

REFERENCES:
patent: 5548755 (1996-08-01), Leung et al.
patent: 5548758 (1996-08-01), Pirahesh et al.
Praveen Seshadri et al., "Filter Joins: Cost-Based Optimization for Magic Sets," 18 pages @ http://www.hzdl.org., Jun. 1995.
"Complex Query Deccorrelation", P. Seshadri et al., 1996 IEEE, pp. 450-458.
"On Optimizing an SQL-like Nested Query", Won Kim, ACM Transactions on Database Systems, vol. 7, No. 3, Sep. 1982, pp. 443-469.
"Of Nests and Trees: A Unified Approach to Processing Queries that Contain Nested Subqueries, Aggregates, and Quantifiers", U. Dayal, Proceedings of the 13th VLDB Conference, Brighton 1987, pp. 197-208.
"Optimization of Nested SQL Queries Revisited", R. A. Ganski, 1987 ACM, pp. 23-33.

Affiliated with

Also associated with

Rating

Optimization of correlated SQL queries in a relational database does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Optimization of correlated SQL queries in a relational database , we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Optimization of correlated SQL queries in a relational database will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-327113