International Business Machines Corporation

Everypath, Inc.

Piyush Goel

Gautam Bhargava

Balakrishna Raghavendra Iyer

Balakrishna Ragmavendra Iyer

Fen-Ling Lin

Jerry Mukai

Hong Sang Tie

Prakash Iyer

Paramesh Sampatrai Desai

Atul Chadha

Peter Hoa

Ronald Mak

Rajeev Mohindra

William Samuel Perlman

Prasad Krothapalli

Amitabh Sinha

Shashi Vittal

Ashish Gupta

Steven John Watts

Venkatachary Srinivasan

Vaishnavi Anjur

Venkatesh Harinarayan

Balakrishna Raghavandra Iyer

A system and method are provided for converting two-dimensional data, particularly information in HTML format, into a canonical representation for rendering in aural or other visual forms wherein each element of interest is selected based on it being part of a mathematically regular expression, namely any expression that can be parsed in a tree. To perform the selection, the container of the element is located, and then the local route of the element within the parsed tree is followed, such that a signature of the element built upon this identification can be constructed and stored for later use in retrieving and rendering the value of the element of interest. The element of interest can thereafter be rendered through an appropriate language translator of interest to the querier. As a further refinement of the invention, the method searches for repeated elements and then stores only one of the selected repeated elements, such as the first repeated element.

Method and system for representing a web element for storing and rendering into other formats

A method is provided for converting two-dimensional data, particularly information in HTML format, into a canonical representation for rendering in aural or other visual forms wherein each element of interest is selected based on it being part of a mathematically regular expression, namely any expression that can be parsed in a tree. To perform the selection, the container of the element is located, and then the local route of the element within the parsed tree is followed, such that a signature of the element built upon this identification can be constructed and stored for later use in retrieving and rendering the value of the element of interest. The element of interest can thereafter be rendered through an appropriate language translator of interest to the querier. As a further refinement of the invention. the method searches for repeated elements and then stores only one of the selected repeated elements, such as the first repeated element.

Method for converting two-dimensional data into a canonical representation

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.

Enumerating projection in SQL queries containing outer and full outer

Many web applications require access to a relational database. The requirements of such web applications are different from conventional applications that access a database. Web applications need to have selective recoverability and need to have support for different transaction structures, other than the flat transaction structure of conventional database applications, including nested and chained transactions. To achieve this, an interface between the application and database has the following functional features: 1) the interface to the database is connection oriented, i.e., the connection is retained across several invocations of the application; 2) the backend of the interface is long living, i.e., it exists across multiple invocations; 3) state information about a session in progress is maintained, and more specifically, it is distributed between the backend of the interface and the hidden fields of the HTML output document.

Interface to support state-dependent web applications accessing a

A method and apparatus for generating static, dynamic and hybrid sparse indices for use with workfiles used by SQL queries in a relational database management system.

Method and apparatus for generating dynamic and hybrid sparse indices

A method, apparatus, and article of manufacture for query simplification by applying generalized inference propagation and generalized transitive closure in SQL queries having selection, projection, join, outer join, and intersection operations. The disclosed transformations and enumeration method unify and solve the problems of 1) unnesting join aggregate queries, and 2) complete enumeration of queries containing outer joins, when the outer join predicate references an aggregated value, or the predicate references more than two base relations in a query subtree. The system first eliminates redundant sub-expressions and modifies expensive binary operations to inexpensive binary operations, then converts complex predicates to simple predicates by application of a generalized selection (GS) operator.

Reordering of complex SQL queries involving groupbys, joins, outer joins

A system and method of determining uniqueness properties of an expression. A root of the expression is first determined, where the root is one of a base relation, a unary operation or a binary operation. Once the root is determined, a first procedure of an augmented unique process is called to determine uniqueness properties of a child of that root. The procedure called is chosen based on the determined root. Where the root is a base relation, a first procedure of a uniqueness process is applied to determine the uniqueness properties of the base relation. Where the root is a unary or binary operation, the called procedure is suspended, a second procedure of the augmented unique process is called to determine the uniqueness properties of the child of the operation, and this process is repeated until a base relation is reached. Once a base relation is reached, the first procedure of the uniqueness process is applied to determine the uniqueness properties of the reached base relation. A next procedure of a uniqueness process is applied to determine the uniqueness properties of a parent operator of the based relation. The procedure applied is chosen based on a type of operation represented by the parent. The process then unwinds to determine the uniqueness properties for each ancestor of the base relation(s).

System and method for generating uniqueness information for optimizing

Reordering of complex SQL queries involving GROUPBYs, joins, outer joins

Enumerating projections in SQL queries containing outer and full outer

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, apparatus and article for manufacture for generating static, dynamic and hybrid sparse indices for use with workfiles used by SQL queries in a relational database management system. A workfile and a sparse index structure are temporarily created in the computer during execution of the query by the computer. The workfile stores intermediate relations resulting from execution of a portion of the SQL query, wherein the intermediate relations comprise sorted rows for an inner table referenced in the SQL query. The sparse index structure contains one or more entries indicating at least an approximate location for at least some of the rows in the workfile. As one or more rows from an outer table referenced in the SQL query are retrieved, the entries of the sparse index structure are searched for a closest matching entry for each retrieved row. The sorted workfile is then scanned for a row matching each retrieved row using the closest matching entry from the sparse index structure as a starting position. The sparse index structure is then updated with an entry corresponding to the row from the sorted workfile matching the retrieved row from the outer table.

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.

Growing community of inventors

Monte Sereno, CA, United States of America

Piyush Goel