International Business Machines Corporation

Balakrishna Ragmavendra Iyer

Piyush Goel

Gautam Bhargava

A method and apparatus for query simplification by applying generalized inference propagation and generalized transitive closure in SQL queries having selection, projection, join, outer join, and intersection operations.

Simplification of SQL queries using generalized inference propagation

A method and apparatus for reordering complex SQL queries containing joins, outer and full outer joins. The method and apparatus first translates the query into a hypergraph representation. Required sets, conflict sets and preserved sets are then generated for the query hypergraph. Using the required sets, a plurality of plans are enumerated, wherein the plans represent associative re-orderings of relations in the query. SQL operators are selectively assigned to each of the enumerated plans using the conflict sets and/or preserved sets, so that the results from the plans are identical to the original query. A novel Modified General Outer Join (MGOJ) operator may be assigned to the root of a sub-tree, wherein the MGOJ operator is a compensation operator. The operator assignment is performed recursively for the root of each sub-tree in the plan. One of the enumerated plans (generally the most optimal) is then selected for execution.

Method and apparatus for reordering complex SQL queries containing inner

A method and apparatus for reordering complex SQL queries containing joins, outer and full outer joins. The method and apparatus first translates the query into a hypergraph representation. Required sets, conflict sets and preserved sets are then generated for the query hypergraph. Using the required sets, a plurality of plans are enumerated, wherein the plans represent associative reorderings of relations in the query. SQL operators are selectively assigned to each of the enumerated plans using the conflict sets and/or preserved sets, so that the results from the plans are identical to the original query. A novel Modified General Outer Join (MGOJ) operator may be assigned to the root of a sub-tree, wherein the MGOJ operator is a compensation operator. The operator assignment is performed recursively for the root of each sub-tree in the plan. One of the enumerated plans (generally the most optimal) is then selected for execution.

Method and apparatus for executing complex SQL queries using projection

Reordering complex SQL queries containing inner and outer join

Method and apparatus for reordering complex SQL queries using a modified

A method and apparatus for query simplification by applying generalization inference propagation and transitive closure in SQL queries having intersection operations combined with inner and outer join operations further combined with selection and/or projection operations. First a strong set of predicates is calculated, typically a selection or join predicate which is null-intolerant. Second the strong set of attributes is used to select and replace a full, left, or right outer join operation found as the operand of an intersection operation in the query. A less expensive outer or inner join replaces the selected join and the process is repeated until no further simplification if possible. Inferences are propagated from one operand to the other of an intersection operation. Selection operators are applied through transitive closure to reduce the size of intermediate results. The transformations are applied to the query to produces a set of operations that perform more efficiently than that of the original query while providing the same results.

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.

Growing community of inventors

San Jose, CA, United States of America

Balakrishna Ragmavendra Iyer