Method for performing joins between different record types in a database system

• US 5,625,813 A
• Filed: 06/27/1995
• Issued: 04/29/1997
• Est. Priority Date: 08/27/1994
• Status: Expired due to Term

• Current Assignee: International Computers Limited (Fujitsu Limited)
• Original Assignee: International Computers Limited (Fujitsu Limited)
• Inventors: Venn, John L.
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US08/495,353
• Time in Patent Office: 672 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A database system comprises a database storing a number of records of different types. The system handles database queries, involving joins between a number of different record types. Each query is first preprocessed to identify optimal starting points for accessing the records. The system then generates a data structure comprising a number of nodes, each node representing one of the record types in the query and indicating an access method for that record type. The nodes are organized into a number of chains, each having a head node representing one of the optimal starting points, and each successive node in each chain representing a record type that can be accessed from the record type represented by a preceding node in the chain by an index mechanism, a hashed access mechanism or an ownership mechanism. A virtual row of the join is constructed by accessing each of the nodes and reading a corresponding record using the specified access method. Further virtual rows are constructed by tracing a backward path through the nodes, starting from the last node in the last chain until a node is encountered for which at least one further record is available for reading from the database. Then, a forward path is traced, starting from this node, reading from the database one record of each record type represented by each node in the forward path, using the access method indicated by each node, until the last node in the last chain is reached.

Claims

1. In a database system comprising a database storing a plurality of records of a plurality of different record types, a method of executing a database query involving a join between a plurality of said record types, the method comprising the steps:

(a) processing the query to create a data structure comprising a plurality of nodes, linked together to form a plurality of chains, each of said nodes representing one of said data types, said processing comprising;

(i) identifying a starting set of record types each of which contains a key that is equated to a literal in said query, or contains a field that is equated to a key of another record type in said query;

(ii) creating a node in said data structure for each of said starting set of record types;

(iii) identifying a further set of record types each of which can be accessed from a record type represented by an existing node; and

(iv) creating a new node in said data structure for each of said further set of record types, and linking said new node to said existing node;

(b) constructing a virtual row of said join by accessing said nodes in sequence and, for each of said nodes, reading a record of the record type represented by that node; and

(c) testing said virtual row to determine whether it satisfies said database query.

2. A method according to claim 1 including constructing a further virtual row of said join, by further steps as follows:

(a) tracing a backward path through said nodes, starting from a last node in a last of said chains and regressing backwards up each chain and from each chain to a preceding chain, until a node is encountered that represents a record type for which at least one further record is available for reading from the database; and

then

(b) tracing a forward path through said nodes, starting from said node that represents a record type for which at least one further record is available for reading from the database, and advancing forwards down each chain and from each chain to a next chain, reading from the database one record of each record type represented by each node in said forward path, until the last node in the last of said chains is reached.

3. A method according to claim 1 wherein, when each record is read, a condition specified by the query is partially applied to the virtual row being constructed, to determine whether to continue construction of that row.

4. In a database system comprising a database storing a plurality of records of a plurality of different record types, a method of executing a database query involving a join between a plurality of said record types, the method comprising the steps:

(a) processing the query to create a data structure comprising a plurality of nodes, linked together to form a plurality of chains, each of said nodes representing one of said data types, said processing comprising;

(i) identifying a first set of record types each of which contains a key that is equated to a literal in said query, and creating a node in said data structure for each of said first set of record types;

(ii) identifying a second set of record types each of which contains a field that is equated in said query to the key of another record type, and creating a node in said data structure for each of said second set of record types;

(iii) identifying a third set of record types each of which contains a key that is equated in said query to a field of a record type represented by an existing node, and creating a new node in said data structure for each of said third set of record types and linking said new node to said existing node;

(b) constructing a virtual row of said join by accessing said nodes in sequence and, for each of said nodes, reading a record of the record type represented by that node; and

(c) testing said virtual row to determine whether it satisfies said database query.

5. A method according to claim 4 including constructing a further virtual row of said join, by further steps as follows:

(a) tracing a backward path through said nodes, starting from a last node in a last of said chains and regressing backwards up each chain and from each chain to a preceding chain, until a node is encountered that represents a record type for which at least one further record is available for reading from the database; and

then

(b) tracing a forward path through said nodes, starting from said node that represents a record type for which at least one further record is available for reading from the database, and advancing forwards down each chain and from each chain to a next chain, reading from the database one record of each record type represented by each node in said forward path, until the last node in the last of said chains is reached.

6. A method according to claim 4 wherein the step of processing the query further includes:

(i) identifying a fourth set of record types each of which can be accessed from a record type identified by an already-created node by means of a set connection, and

(ii) for each of said fourth set of record types, creating a new node in said data structure representing that record type and linking said new node to said already-created node.

7. A database system comprising:

(a) a database storing a plurality of records of a plurality of different record types;

(b) means for generating a database query involving a join between a plurality of said record types;

(c) means for processing the query to create a data structure comprising a plurality of nodes, linked together to form a plurality of chains, each of said nodes representing one of said data types, said processing means comprising;

(i) means for identifying a starting set of record types each of which contains a key that is equated to a literal in said query, or contains a field that is equated to a key of another record type in said query;

(ii) means for creating a node in said data structure for each of said starting set of record types;

(iii) means for identifying a further set of record types each of which can be accessed from a record type represented by an existing node; and

(iv) means for creating a new node in said data structure for each of said further set of record types, and linking said new node to said existing node;

(d) means for constructing a virtual row of said join by accessing said nodes in sequence and, for each of said nodes, reading a record of the record type represented by that node; and

(e) means for testing said virtual row to determine whether it satisfies said database query.