The Commons

Back to Results

Patent Title: Enumerating projections in SQL queries containing outer and full outer joins in the presence of inner joins

Assignee: IBM
Patent Number: US5687362
Issue Date: 11-11-1997
Application Number:
File Date:01-30-1995


Abstract: The present invention discloses a method and apparatus for the enumeration of projections (i.e., "SELECT DISTINCT" operations) in SQL queries containing outer and full outer joins in the presence of inner joins without encountering any regression in performance. The present invention removes projections from a given user query by moving the projections to the top of an expression tree representation of the query, wherein the projection removal is performed using algebraic identities rather than rule-based transformations. The present invention also discloses several methods of enumerating different plans or schedules for projection operations and binary operations in the given user query. The present invention can significantly reduce the execution time of a query by selecting the optimal schedule for binary operations and projections between the binary operations. However, the present invention ensures that there is no regression in performance by comparing the cost of the query with the cost of enumerated plans or schedules, thereby ensuring that the optimizations or transformations do not introduce performance penalties.

Notes:

Link to USPTO

IBM Pledge dated 1/11/2005