System and methods for performing multi-source searches over heterogeneous databases

• US 5,511,186 A
• Filed: 11/18/1992
• Issued: 04/23/1996
• Est. Priority Date: 11/18/1992
• Status: Expired due to Fees

• Current Assignee: MDL Information Systems, Inc. (Dassault Systemes SA)
• Original Assignee: MDL Information Systems, Inc. (Dassault Systemes SA)
• Inventors: Carhart, Raymond E., Schaller, Anthony J., Grier, David L.
• Primary Examiner(s): Black, Thomas C.
• Assistant Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US07/978,016
• Time in Patent Office: 1,252 Days
• Field of Search: 395/600, 364/DIG. 1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

System and methods for performing search and retrieval operations over multiple databases, the system and methods being suitable for use in combination with databases of different kinds managed by different database management systems, the system and methods also being suitable for use in combination with databases resident on different computers in a computer network environment. The system and methods in their simplest application search each of a pair of database segments incrementally, using the incremental results of one search to direct the processing of the other search. The methods may be nested, and the system may be used in multiple instances, to effect searches on combinations of any number of database segments.

Claims

1. A method for performing a multiple-segment search over a first database segment with a search predicate requiring values from a second database segment, where the first and the second databases are managed by a first and a second database management system, respectively, where the search predicate is a top-level AND conjunction of independent factors, and where the search predicate contains an INCURSOR operator, the method comprising the computer-implemented steps of:

(a) Dividing the top-level factors into incursor factors, being those that contain INCURSOR operators, and local factors, being those that do not contain INCURSOR operators, whereby the local factors contain no references to the second database segment; and

(b) Iterating the following steps over those tuples produced by a search of the first database where all the local factors are satisfied;

i. evaluating the INCURSOR predicates in the incursor factors;

ii. then evaluating the incursor factors using the results of the foregoing step;

iii. then, while the multiple-segment search is ongoing, if the result of the preceding step is TRUE, making the record from the first database segment available at that time as an incremental part of the result of the multiple-segment search.

2. The method recited in claim 1, wherein the first database management system is a relational database management system.

3. The method recited in claim 1, wherein the first database management system is a file management system.

4. The method recited in claim 1, wherein the first and the second database management systems are different from each other.

5. The method recited in claim 1, wherein the step (b)(i) of evaluating an INCURSOR predicate comprises the steps of:

(a) Using the first (field list) operand of the INCURSOR predicate to create a second tuple of values from the tuple produced by the search of the first database; and

(b) Validating the second tuple against the second (foreign search) operand of the INCURSOR predicate, whereby a value of TRUE or FALSE is yielded as the value of the INCURSOR predicate.

6. A method for performing a multiple-segment search over a first database segment with a search predicate requiring values from a second database segment, where the first and the second databases are managed by a first and a second database management system, respectively, where the search predicate is a top-level AND conjunction of independent factors, and where at least one of the top-level factors is an INCURSOR predicate, the method comprising the computer-implemented steps of:

(a) Selecting a driving clause from among the top-level INCURSOR predicates;

(b) Then dividing the remaining top-level factors into incursor factors, being those that contain INCURSOR operators, and local factors, being those that do not contain INCURSOR operators, whereby the local factors contain no references to the second database segment;

(c) Then iterating the following steps over those tuples produced by a search of the second database segment using the driving clause;

i. Passing on to the next iteration if the tuple from the driving clause contains any NULL values;

ii. Otherwise, iterating the following steps over each record produced by a search of the first database segment using a filter predicate that conjoins the local factors with a predicate that is TRUE only if all the values in the tuple from the driving clause are equal to the values from the corresponding fields in the first database segment;

A. evaluating the INCURSOR base predicates in the incursor factors;

B. then evaluating the incursor factors using the results of the foregoing step; and

C. then, while the multiple-segment search is ongoing, if the result of the preceding step is TRUE, making the record from the first database segment available at that time as an incremental part of the result of the multiple-segment search.

7. The method recited in claim 6, wherein the first database management system is a relational database management system.

8. The method recited in claim 6, wherein the first database management system is a file management system.

9. The method recited in claim 6, wherein the first and the second database management systems are different from each other.

10. The method recited in claim 6, wherein the steps (b)(i) of evaluating an INCURSOR predicate comprises the steps of:

(a) Creating a second tuple of values from the tuple produced by the search of the first database to use as the first (field list) operand of the INCURSOR predicate; and

(b) Validating the second tuple against the second (foreign search) operand of the INCURSOR predicate, whereby a value of TRUE or FALSE is yielded as the value of the INCURSOR predicate.

11. A method for performing a theta-join over a first and a second table managed by a first and a distinct second database management system to produce a select list of field expressions, with a filter predicate and with a set of linking fields between the first and the second table, the method comprising the computer-implemented steps of:

(a) Reducing the filter predicate into a conjunctive form having a top-level conjunction of factors;

(b) Dividing the top-level factors into first-table factors, being those that refer only to fields in the first table, second-table factors, being those that refer only to fields in the second table, and mixed factors, being those that refer to fields in the both the first and the second tables;

(c) Invoking a query on the first table by the first database management system to retrieve incrementally records that satisfy the first-table factors, and for each record retrieved that has no NULL linking field, performing the following steps;

i. If no linking field in the record is NULL, invoking a query on the second table by the second database management system to retrieve records that satisfy the second-table factors and that have linking field values related by the theta operator to the linking field values of the record retrieved from the first table;

ii. For each record retrieved from the second table in the preceding step (i), evaluating the mixed factors against the current first table record and the second table record;

iii. If the result of the evaluation of the preceding step (ii) is TRUE, calculating the values of the field expressions in the select list and producing the select list as a result of the present iteration.

12. The method recited in claim 11, wherein the step (a) of reducing the filter predicate further completes the reduction of the filter predicate to conjunctive normal form.

13. The method recited in claim 11, wherein the first table is a relation managed by a relational database management system.

14. The method recited in claim 11, wherein the steps of invoking a query on a table comprise the steps of:

(a) Formulating a dynamic SQL statement that embodies the query; and

(b) Invoking the dynamic SQL statement in the table'"'"'s database management system.

15. The method recited in claim 11, wherein the step (c)(i) of invoking a query on the second table invokes the query so as to retrieve records incrementally.

16. A method for performing a theta-join over a first and a second table managed by a first and a distinct second database management system to produce a select list of field expressions, with a filter predicate, and with a set of linking fields between the first and the second table, the method comprising the computer-implemented steps of:

(a) Reducing the filter predicate to conjunctive normal form, whereby a top-level conjunction of factors is created;

(b) Dividing the top-level factors into first-table factors, being those that refer only to fields in the first table, second-table factors, being those that refer only to fields in the second table, and mixed factors, being those that refer to fields in the both the first and the second tables;

(c) Invoking a query on the first table by the first database management system to retrieve incrementally records that satisfy the first-table factors, and for each record retrieved that has no NULL linking field, performing the following steps;

i. Transforming the mixed factors into NULL-reduced mixed factors based on the values in the record retrieved from the first table, whereby all the consequences of any NULL values in the retrieved record are propagated through the mixed factors;

ii. Invoking a query on the second table by the second database management system to retrieve records that satisfy the second-table factors, that satisfy the NULL-reduced mixed factors, and that have linking field values related by the theta operator to the linking field values of the record retrieved from the first table;

iii. Then calculating the values of the field expressions in the select list and producing the select list as a result of the present iteration.

17. A gateway system for performing a multiple-operand search over a database for those records that satisfy a first search predicate that includes an INCURSOR predicate having a field list operand and a foreign cursor operand, where the database is managed by a database management system, the gateway system comprising:

(a) means for receiving the first search predicate;

(b) means for dividing the first search predicate into a conjunction of top-level factors;

(c) means for opening the database through the database management system;

(d) means for incrementally retrieving those records that satisfy the local factors (being those top-level factors that do not contain an INCURSOR operand at any level) from the database through the database management system;

(e) means for invoking the foreign cursor operand to validate a local tuple of values taken from a record retrieved from the database; and

(f) means for incrementally producing records retrieved from the database while the multiple-operand search is ongoing as incremental parts of the result of the multiple-operand search.

18. The gateway system as recited in claim 17, wherein the field list identifies one or more fields in the database, further comprising:

(a) means for incrementally obtaining a distinct foreign tuple from the foreign cursor operand;

(b) means for forming a driving tuple having values taken from the distinct foreign tuple; and

(c) means for incrementally retrieving through the database management system those records in the database that satisfy the local factors and that have values in the field list operand fields equal to corresponding values in the driving tuple.

19. The gateway system as recited in claim 17, further comprising:

(a) means for receiving a cursor, the cursor having a second search predicate to be performed over the database management system to validate a validation tuple of values;

(b) means for querying the database management system to determine whether the validation tuple appears in some record in the database; and

(c) means for providing the result of the preceding querying step.