International Business Machines Corporation

Lori G Strain

Mir Hamid Pirahesh

Ting Yu Leung

David Everett Simmen

Bernhard K Schiefer

Sanjai Tiwari

Weipeng P Yan

The present invention provides a method for estimating cardinalities for query processing in a relational database management system. The present method is suitable for use with a query optimizer for estimating cardinalities for sets of columns or keys resulting from a grouping operation or a duplicate removal operation.

Method for estimating cardinalities for query processing in a relational

The present invention optimizes SQL queries by exploiting uniqueness properties. In identifying whether the generalized 1-tuple condition exists, the query is first analyzed to determine whether any columns referenced in a predicate of the query are bound. According to the present invention, columns may be bound to constant values or correlated columns or columns that are already bound. The bound columns, if any, are then analyzed to determine whether any of the bound columns comprise a key for its associated table. If these conditions exist, then the query satisfies the 1-tuple condition, in that it returns at most one tuple. Once the generalized 1-tuple condition has been identified to exist for the query, important query transformations can be performed for optimization purposes. These query transformations comprise the transformation of scalar subqueries into joins, or the elimination of distinctiveness requirements (i.e., DISTINCT keywords) from SELECT clauses.

Exploitation of uniqueness properties using a 1-tuple condition for the

Growing community of inventors

Etobicoke, Canada

Lori G Strain