System and method for performance complex heterogeneous database queries using a single SQL expression

• US 6,341,277 B1
• Filed: 04/09/1999
• Issued: 01/22/2002
• Est. Priority Date: 11/17/1998
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Coden, Anna Rosa, Schwartz, Michael Stephen, Brereton, JoAnn Piersa
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): TRUONG, CAM Y T
• Application Number:
  US09/289,020
• Time in Patent Office: 1,019 Days
• Field of Search: 707/2-5, 707/100
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

Query objects are created by a client process. The query objects have one or more sub-query objects and one or more execute methods that are capable of operating on their respective query object to produce one or more query expressions. All of the execute methods are capable of producing the respective query expression that is compatible with a structured query language. A compound query contains one or more boolean expressions of one or more of the query objects. The compound query has one or more compound execute methods which invoke one or more the execute methods of each of the query objects. Each of the execute methods returns their respective query expression and the compound execute method uses one or more common table expressions to combine the query expressions to form a single compound query expression that represents the boolean expression. This single expression can be execute against a database to return a result without executing any of the query expressions against the database individually.

Claims

1. A computer system having one or more central processing units, one or more memories, and one or more databases, the computer system further comprising:

one or more query objects created by a client process, the query objects having one or more sub-query objects and one or more execute methods that are capable of operating on their respective query object to produce one or more query expressions, all of the execute methods capable of producing the respective query expression that is compatible with a structured query language; and

a compound query containing one or more boolean expressions of one or more of the query objects, the compound query having one or more compound execute methods which invoke one or more of the execute methods of each of the query objects, each of the execute methods returning their respective query expression, the compound execute method using one or more common table expressions to combine the query expressions to form a single compound query expression that represents the boolean expression and can be executed against a database to return a result without executing any of the query expressions against the database individually.

2. A system as in claim 1, where the query objects are any one or more of the following types:

a Compound Free Text Query Object, a Compound Boolean Text Query Object, a Compound Parametric Query Object, a Compound Feature Query Object and a Boolean Compound Query Object.

3. A system, as in claim 1, where one or more of the query objects is a Compound Free Text Query Object, each Compund Free Text Query Object having one or more text atoms, and the compound execute method creating one or more common table expressions, one or more of common table expression corresponding to one of the text atoms.

4. A system, as in claim 3, where one or more of the common table expressions corresponds to one or more combinations of the text atoms.

5. A system, as in claim 1, where the results are ranked by a ranking process.

9. The computer system of claim 1, further comprising a step to submit the single compound query expression to the database for execution.

10. The computer system of claim 1, wherein each of two of the query expressions define a result set, and wherein the compound execute method normalizes one of the result sets.

11. The computer system of claim 10, wherein one result set comprises a first number of columns that is less than a second number of columns defined by the other of the two result sets, and wherein the compound execute method normalizes the one result set by adding an appropriate number of columns to the one result set so that the number of columns of the one result set after the addition of columns is equivalent to the second number of columns.

6. A computer system having one or more central processing units, one or more memories, and one or more databases, the computer system further comprising:

a means for creating one or more query objects, the query objects having one or more sub-query objects and one or more execute methods that are capable of operating on their respective query to produce one or more query expressions, all of the execute methods capable of producing the respective query expression that is compatible with a structured query language; and

means for invoking one or more of the execute methods of each of the query objects by using one or more compound execute methods in a compound query, the compound query containing one or more boolean expressions of one or more of the query objects, each of the execute methods returning their respective query expression, the compound execute method using one or more common table expressions to combine the query expressions to form a single compound query expression that represents the boolean expression and executes against a database to return a result without executing any of the query expressions against the database individually.

12. The computer system of claim 6, further comprising a means for submitting the single compound query expression to the database for execution.

7. A method executing on a computer system comprising the steps of:

creating one or more query objects, the query objects having one or more sub-query objects and one or more execute methods that are capable of operating on their respective query object to produce one or more query expressions, all of the execute methods capable of producing the respective query expression that is compatible with a structured query language; and

invoking one or more of the execute methods of each of the query objects by using one or more compound execute methods in a compound query, the compound query containing one or more boolean expressions of one or more of the query objects, each of the execute methods returning their respective query expression, the compound execute method using one or more common table expressions to combine the query expressions to form a single compound query expression that represents the boolean expression and executes against a database to return a result without executing any of the query expressions against the database individually.

13. The method of claim 7, further comprising a step of submitting the single compound query expression to the database for execution.

14. The method of claim 7, wherein each of two of the query expressions define a result set, and wherein the method further comprises the step of the compound execute method normalizing one of the result sets.

15. The method of claim 14, wherein one result set comprises a first number of columns that is less than a second number of columns defined by the other of the two result sets, and wherein the step of the compound execute method normalizing one of the result sets further comprises the step of normalizing the one result set by adding an appropriate number of columns to the one result set so that the number of columns of the one result set after the addition of columns is equivalent to the second number of columns.

8. A computer program product which performs the steps of:

creating one or more query objects, the query objects having one or more sub-query objects and one or more execute methods that are capable of operating on their respective query object to produce one or more query expressions, all of the execute methods capable of producing the respective query expression that is compatible with a structured query language; and

invoking one or more of the execute methods of each of the query objects by using one or more compound execute methods in a compound query, the compound query containing one or more boolean expressions of one or more of the query objects, each of the execute methods returning their respective query expression, the compound execute method using one or more common table expressions to combine the query expressions to form a single compound query expression that represents the boolean expression and executes against a database to return a result without executing any of the query expressions against the database individually.

16. The computer program product of claim 8, further comprising a step of submitting the single compound query expression to the database for execution.

17. The computer program product of claim 8, wherein each of two of the query expressions define a result set, and wherein the computer program product further comprises a step of the compound execute method normalizing one of the result sets.

18. The computer program product of claim 17, wherein one result set comprises a first number of columns that is less than a second number of columns defined by the other of the two result sets, and wherein the step of the compound execute method normalizing one of the result sets further comprises the step of normalizing the one result set by adding an appropriate number of columns to the one result set so that the number of columns of the one result set after the addition of columns is equivalent to the second number of columns.