Joined table expression optimization by query transformation

Details Joined table expression optimization by query transformation Joined table expression optimization by query transformation

1999-06-14

2002-05-07

Mizrahi, Diane D. (Department: 2171)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06385603

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to the optimization of joined table expressions by query transformation in database management systems.
2. Description of Related Art
Computer systems incorporating Relational DataBase Management System (RDBMS) software using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
There are a number of well-known and widely-applied optimization methods for queries available in the prior art. Some examples of these optimization methods include Dynamic Programming, Greedy Algorithm, etc. Generally, these optimization methods attempt to find the best execution strategy for a query based on a cost estimation over a range of possible execution strategies in the search space.
On the other hand, optimization has not been successfully applied to joined table expressions. Joined table expressions are found in FROM clauses of SELECT statements, and generally take the form of “table-reference join-operator table-reference ON join-condition.” The join-operator can be INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN, the table-reference represents a base table or intermediate result table, and the join-condition is a boolean expression that results in a true, false, or unknown value. More complex joined table expressions can be a sequence of simple joined table expressions combined by join operators, parentheses, and nested table expressions.
There is a need in the art for improved methods of optimizing joined table expressions and complex join operations. Specifically, there is a need in the art for improved methods of optimizing joined table expressions and complex join operations by query transformations.
SUMMARY OF THE INVENTION
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for optimizing queries, wherein the query is analyzed and a sequence of transformations is triggered to achieve the optimal performance for joined table expressions therein. The transformations include predicates push down for joined table expressions and non-null predicates push down to simplify join operators for joined table expressions.


REFERENCES:
patent: 4614841 (1986-09-01), Babecki et al.
patent: 5057998 (1991-10-01), Hirokawa
patent: 5179665 (1993-01-01), Roslund et al.
patent: 5418913 (1995-05-01), Fujimoto
patent: 5446841 (1995-08-01), Kitano et al.
patent: 5584024 (1996-12-01), Shwartz
patent: 5590324 (1996-12-01), Leung et al.
patent: 5659725 (1997-08-01), Levy et al.
patent: 5713015 (1998-01-01), Goel et al.
patent: 5761422 (1998-06-01), Westin et al.
patent: 5848241 (1998-12-01), Misinai et al.
patent: 6021405 (2000-02-01), Celis et al.
patent: 6112198 (2000-08-01), Lohman et al.
patent: 6275818 (2001-08-01), Subramanian et al.

Affiliated with

Also associated with

Rating

Joined table expression optimization by query transformation does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Joined table expression optimization by query transformation, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Joined table expression optimization by query transformation will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2824954