Performing an efficient implicit join of multiple mixed-type records

• US 8,150,855 B2
• Filed: 12/30/2008
• Issued: 04/03/2012
• Est. Priority Date: 12/30/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chen, Shawfu, Hoyos, Adris E., Jones, Kevin T., Klos, Bernard, Krymer, Aleksandr
• Primary Examiner(s): SOMERS, MARC S
• Application Number:
  US12/345,663
• Publication Number:
  US 20100169307A1
• Time in Patent Office: 1,190 Days
• Field of Search: 707/705, 707/736, 707/737, 707/741, 707/755, 707/758, 707/769, 707/790, 707/791, 707/792, 707/802, 707/803, 707/812, 707/953, 707/956
• US Class Current: 707/741
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: Y10S 707/956 Hierarchical

Abstract

A method, system, method and computer program product for retrieving data. Records are retrieved from a hierarchical database. The records are categorized into a plurality of record types. Each record comprises a unique identifier field. A record map contains zero or more entries. Each entry comprises an identifying value, data from at least one record and a set of Boolean flags. Each flag corresponds to a record type. A computer iterates over the retrieved records. Data from each record is stored at an entry in the record map having an identifying value equal to the value included in the unique identifier field of the record. Moreover, the flag in this entry which corresponds to the record type of the record is set. Inner joins and outer joins are then performed using the contents of the record map.

Claims

1. A method for retrieving data, the method comprising:

retrieving a plurality of records from a hierarchical database, the plurality of records categorized into a plurality of record types, each record included in the plurality of records comprising a unique identifier field;

iterating over each record included in the retrieved plurality of records, wherein iterating over each record is performed at a computer;

for each record for which a value contained in the unique identifier field is not included in a record map, creating an entry in the record map, the created entry comprising the value contained in the unique identifier field, a subset of the record and a set of flags, each flag included in the set of flags corresponding to a record type, wherein each flag included in the set of flags is set to a first Boolean value if the record is of the type corresponding to the flag and a second Boolean value if the record is not of the type corresponding to the flag; and

for each record for which the value contained in the unique identifier field is included in an existing entry in the record map, adding a subset of the record to the existing entry and setting the flag included in the existing entry and corresponding to the record type of the record to the first Boolean value.

2. The method of claim 1, further comprising performing an inner join, wherein performing the inner join comprises outputting the subsets of records included in each entry in the record map for which all flags included in the set of flags contain the first Boolean value.

3. The method of claim 1, further comprising performing an outer join, wherein performing the outer join comprises outputting the subsets of records included in each entry in the record map for which at least one flag included in the set of flags contains the first Boolean value.

4. The method of claim 1, further comprising filtering the plurality of records to include only records matching at least one criterion specified by a query, wherein filtering the plurality of records is performed before iterating over each record included in the retrieved plurality of records.

5. The method of claim 1, wherein the plurality of records is included in a single subfile included in the database.

6. The method of claim 1, wherein a query is processed using a single pass through the database.

7. The method of claim 1, further comprising analyzing metadata about the plurality of records to determine a location of the unique identifier field.

8. The method of claim 7, wherein the metadata is expressed using Extensible Markup Language (XML).

9. A system for retrieving data, the system comprising:

a retrieving unit configured to retrieve a plurality of records from a hierarchical database, the plurality of records categorized into a plurality of record types, each record included in the plurality of records comprising a unique identifier field;

a record map, the record map containing zero or more entries, wherein each entry stored at the record map comprises an identifying value, a subset of at least one record and a set of flags, each flag included in the set of flags corresponding to a record type; and

a processor configured to;

iterate over each record included in the plurality of records retrieved by the retrieving unit;

for each record for which a value contained in the unique identifier field is not included in a record map, create an entry in the record map, the created entry comprising the value contained in the unique identifier field, a subset of the record and a set of flags, each flag included in the set of flags corresponding to a record type, wherein each flag included in the set of flags is set to a first Boolean value if the record is of the type corresponding to the flag and a second Boolean value if the record is not of the type corresponding to the flag; and

for each record for which the value contained in the unique identifier field is included in an existing entry in the record map, add a subset of the record to the existing entry and set the flag included in the existing entry and corresponding to the record type of the record to the first Boolean value.

10. The system of claim 9, further comprising a joining unit configured to perform an inner join, wherein performing the inner join comprises outputting the subsets of records included in each entry in the record map for which all flags included in the set of flags contain the first Boolean value.

11. The system of claim 9, further comprising a joining unit configured to perform an outer join, wherein performing the outer join comprises outputting the subsets of records included in each entry in the record map for which at least one flag included in the set of flags contains the first Boolean value.

12. The system of claim 9, further comprising a filtering unit configured to filter the plurality of records to include only records matching at least one criterion specified by a query, wherein filtering the plurality of records is performed before iterating over each record included in the retrieved plurality of records.

13. The system of claim 9, wherein the plurality of records is included in a single subfile included in the database.

14. The system of claim 9, wherein a query is processed using a single pass through the database.

15. The system of claim 9, further comprising an analyzing unit configured to analyze metadata about the plurality of records to determine a location of the unique identifier field.

16. A non-transitory computer-readable medium comprising:

computer readable program codes coupled to the non-transitory computer-readable medium for retrieving data, the computer readable program codes configured to cause a computer to;

retrieve a plurality of records from a hierarchical database, the plurality of records categorized into a plurality of record types, each record included in the plurality of records comprising a unique identifier field;

iterate over each record included in the retrieved plurality of records;

for each record for which a value contained in the unique identifier field is not included in a record map, create an entry in the record map, the created entry comprising the value contained in the unique identifier field, a subset of the record and a set of flags, each flag included in the set of flags corresponding to a record type, wherein each flag included in the set of flags is set to a first Boolean value if the record is of the type corresponding to the flag and a second Boolean value if the record is not of the type corresponding to the flag; and

for each record for which the value contained in the unique identifier field is included in an existing entry in the record map, add a subset of the record to the existing entry and set the flag included in the existing entry and corresponding to the record type of the record to the first Boolean value.

17. The non-transitory computer-readable of claim 16, wherein the computer readable program codes are further configured to cause the program to perform an inner join, wherein performing the inner join comprises outputting the subsets of records included in each entry in the record map for which all flags included in the set of flags contain the first Boolean value.

18. The non-transitory computer-readable of claim 16, wherein the computer readable program codes are further configured to cause the program to perform an outer join, wherein performing the outer join comprises outputting the subsets of records included in each entry in the record map for which at least one flag included in the set of flags contains the first Boolean value.

19. The non-transitory computer-readable of claim 16, wherein the computer readable program codes are further configured to cause the program to filter the plurality of records to include only records matching at least one criterion specified by a query, wherein filtering the plurality of records is performed before iterating over each record included in the retrieved plurality of records.

20. The non-transitory computer-readable of claim 16, wherein a query is processed using a single pass through the database.