Systems and methods for optimizing database queries

• US 8,583,593 B1
• Filed: 11/18/2011
• Issued: 11/12/2013
• Est. Priority Date: 04/11/2005
• Status: Active Grant

• Current Assignee: Experian Information Solutions Incorporated (Experian plc)
• Original Assignee: Experian Information Solutions Incorporated (Experian plc)
• Inventors: Achanta, Venkat R.
• Primary Examiner(s): Bhatia, Ajay
• Assistant Examiner(s): Mina, Fatima
• Application Number:
  US13/300,414
• Time in Patent Office: 725 Days
• Field of Search: 707/602, 707/607, 707/640, 707/661, 707/759, 707/763, 707/769, 707/770, 707/803
• US Class Current: 707/602
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A relational database warehouse system with query optimization capabilities is described that allows for speedy identification of sets of records of interest from amongst tens of millions of records. The records of interest may include complex derived attributes, generated, at least in part, by aggregating data from a plurality of records in base data tables. In various embodiments, the query optimization capabilities allow the database warehouse system to identify conditions under which normal query execution may be replaced by one or more optimized execution methods, including, for example, eliminating unnecessary inner join operations on base data tables specified by a query, re-ordering the execution of group-by operations and left-outer join operations to greatly reduce the size of join tables produced while processing a query, and/or consolidating a set of segmentation queries for execution in one pass over the records of the database.

Claims

1. A method of dynamically revising a query before processing by a relational database, the method comprising:

receiving, with a query management system, a query comprising one or more attributes, each of the one or more attributes related to data stored in two or more base tables of a relational database;

accessing, with the query management system, an attribute view definition that defines an attribute view, the attribute view definition referencing a plurality of base tables of the relational database, including the two or more base tables, wherein the attribute view definition references more base tables than necessary for deriving the one or more attributes comprised in the query, wherein the one or more attributes in the query are a subset of attributes available in the attribute view;

identifying, with the query management system, the one or more attributes in the query, wherein the identified one or more attributes are the one or more attributes in the query;

determining, with the query management system, a set of unreferenced base tables from the plurality of base tables, wherein the unreferenced base tables do not store data used for calculating the one or more attributes of interest, wherein the accessing is performed prior to the determining;

generating, with the query management system, a modified definition of the attribute view definition, the modified definition defining a temporary attribute view for processing the query, the modified definition excludes references to the set of unreferenced base tables; and

electronically submitting, with the query management system, the query to the relational database for generation of the temporary attribute view using the modified definition and for execution of the query on the temporary attribute view,

wherein the query management system comprises a computer processor and an electronic memory storage.

2. The method of claim 1, wherein the one or more attributes of the query comprises at least one derived attribute that is calculated from at least one attribute using at least one of the base tables.

3. The method of claim 1, wherein at least a portion of one of the base data tables stores data related to at least one of:

credit accounts, credit inquiries or property-related public data records.

4. The method of claim 2, wherein the at least one derived attribute is calculated in real time in response to receiving the query.

5. The method of claim 1, wherein the query is a batch query.

6. A query management system with query optimization capabilities, the query management system comprising:

one or more computer processors;

a computer readable electronic memory storage in communication with the one or more computer processors, the computer readable electronic memory storage storing software modules including instructions that are executable by the one or more hardware processors, the software modules including at least;

a query processing module configured to;

electronically communicate with a database warehouse system which logically stores data about a general population as a plurality of base data tables;

a query comprising one more attributes, each of the one or more attributes related to data stored in one or more base data tables of the plurality of base data tables;

access a virtual table definition that defines a virtual table, the virtual table definition referencing a subset of the plurality of base data tables, wherein the virtual table definition references more base data tables than necessary for deriving the one or more attributes comprised in the query, and wherein the one or more attributes in the query are a subset of attributes available in the virtual table;

identify the one or more attributes in the query, wherein the identified one or more attributes are the one or more attributes in the query;

determine, after the accessing of the virtual table definition, a set of unreferenced base data tables from the subset of the plurality of base data tables, wherein the unreferenced base data tables do not store data used for calculating the one or more attributes in the query;

generates a modified definition of the virtual table definition, the modified definition defining a temporary virtual table for processing the query and excluding references to the set of unreferenced base data tables; and

electronically submit the query to the database warehouse system for generation of the temporary virtual table using the modified definition and for execution of the query using the temporary virtual table.

7. The query management system of claim 6, wherein the database warehouse system is a relational database system.

8. The query management system of claim 6, wherein the one or more attributes of the query comprises at least one derived attribute that is calculated from at least one attribute using at least one of the plurality of base data tables.

9. The query management system of claim 6, wherein at least a portion of one of the base data tables stores data related to at least one of:

credit accounts, credit inquiries, or property-related public data records.

10. The query management system of claim 8, wherein the at least one derived attribute is calculated in real time in response to receiving the query.

11. The query management system of claim 6, wherein the query is a batch query.

12. Non-transitory computer storage that stores executable program instructions that direct a computer system to at least:

receive a query comprising one or more attributes, each of the one or more attributes related to data stored in two or more base tables of a relational database;

access an attribute view definition that defines an attribute view, the attribute view definition referencing a plurality of base tables of the relational database, including the two or more base tables, wherein the attribute view definition references more base tables than necessary for deriving the one or more attributes comprised in the query, wherein the one or more attributes in the query are a subset of attributes available in the attribute view;

identify the one or more attributes in the query, wherein the identified one or more attributes are the one or more attributes in the query;

determine a set of unreferenced base tables of from the plurality of base tables, wherein the unreferenced base tables do not store data used for calculating the one or more attributes of interest, wherein the accessing is performed prior to the determining;

generate a modified definition of the attribute view definition, the modified definition defining a temporary attribute view for processing the query, the modified definition excludes references to the set of unreferenced base tables; and

electronically submit the query to the relational database for generation of the temporary attribute view using the modified definition and for execution of the query on the temporary attribute view.

13. The non-transitory computer storage of claim 12, wherein the one or more attributes of the query comprises at least one derived attribute that is calculated from at least one attribute using at least one of the base tables.

14. The non-transitory computer storage of claim 12, wherein at least a portion of one of the base data tables stores data related to at least one of:

credit accounts, credit inquiries or property-related public data records.

15. The non-transitory computer storage of claim 12, wherein the at least one derived attribute is calculated in real time in response to receiving the query.

16. The non-transitory computer storage of claim 12, wherein the query is a batch query.