Generating statistics for temporary tables during query optimization

• US 8,650,179 B2
• Filed: 08/18/2005
• Issued: 02/11/2014
• Est. Priority Date: 08/18/2005
• Status: Active Grant

• Current Assignee: AirBNB Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Edwards, John Francis, Faunce, Michael S., Muras, Brian Robert, Driesch, Robert Douglas Jr.
• Primary Examiner(s): Ruiz, Angelica
• Application Number:
  US11/207,055
• Publication Number:
  US 20070043697A1
• Time in Patent Office: 3,099 Days
• Field of Search: None
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination

Abstract

A method generates at least one statistic for a temporary table during optimization of a database query before the temporary table is created. By doing so, optimization can proceed on a database query irrespective of the fact that the temporary table may not be materialized until the database query is ultimately executed.

Claims

1. A method of optimizing a database query, the method comprising:

receiving a first database query where at least one field is materialized into a first temporary table during execution of the first database query, wherein the field is associated with a second database query;

merging at least one predicate from the first database query with at least one predicate from the second database query;

generating a first statistic for the first temporary table prior to creation of the first temporary table based upon the merged predicates from the first and second database queries, wherein the first statistic includes a cardinality for at least one column of the first temporary table; and

optimizing the first database query using the first statistic.

2. The method of claim 1, wherein the second database query is at least one abstraction away from a physical table associated with the second database query.

3. The method of claim 1, wherein the first statistic includes at least one of a selectivity of a predicate, a cardinality of a field, a number of records in a table, a number of distinct values, a number of records selected, a number of bytes, a frequent value list, a histogram, a distribution, a predicate, a record, a field, or a value.

4. The method of claim 1, wherein generating the first statistic includes using a physical table associated with the field.

5. The method of claim 1, wherein generating the first statistic includes using an access plan associated with the field.

6. The method of claim 1, wherein generating the first statistic includes using a second statistic of a second temporary table.

7. The method of claim 1, further comprising using the first statistic to generate a second statistic for a second temporary table.

8. The method of claim 1, further comprising saving the first statistic in at least one of the first temporary table or an access plan.

9. The method of claim 1, wherein the first database query references at least one logical table, and wherein the at least one field is a field of the logical table.