Generating statistics for temporary tables during query optimization

• US 20070043697A1
• Filed: 08/18/2005
• Published: 02/22/2007
• Est. Priority Date: 08/18/2005
• Status: Active Grant

• Current Assignee: AirBNB Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Muras, Brian, Faunce, Michael, Edwards, John, Driesch, Robert
• Granted Patent: US 8,650,179 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination

Abstract

An apparatus, program product and method generate 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:

(a) for a first database query where at least one field is materialized into a first temporary table during execution of the first database query, generating a first statistic for the first temporary table prior to creation of the first temporary table; and

(b) optimizing the first database query using the first statistic.

2. The method of claim 1, wherein the field is associated with a second database query.

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

4. The method of claim 1, wherein generating the first statistic includes using a predicate of a second database query.

5. 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.

6. The method of claim 1, wherein generating the first statistic includes using a predicate of the first database query.

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

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

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

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

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

12. An apparatus, comprising:

(a) at least one processor;

(b) a memory; and

(c) program code resident in the memory and configured to be executed by the at least one processor to optimize a first database query where at least one field is materialized into a first temporary table during execution of the first database query by generating a first statistic for the first temporary table prior to creation of the first temporary table and optimizing the first database query using the first statistic.

13. The apparatus of claim 12, wherein the field is associated with a second database query.

14. The apparatus of claim 13, wherein the second database query is at least one abstraction away from a physical table associated with the second database query.

15. The apparatus of claim 12, wherein the program code is configured to generate the first statistic using a predicate of a second database query.

16. The apparatus of claim 12, 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.

17. The apparatus of claim 12, wherein the program code is configured to generate the first statistic using a predicate of the first database query.

18. The apparatus of claim 12, wherein the program code is configured to generate the first statistic using a physical table associated with the field.

19. The apparatus of claim 12, wherein the program code is configured to generate the first statistic using an access plan associated with the field.

20. The apparatus of claim 12, wherein the program code is configured to generate the first statistic using a second statistic of a second temporary table.

21. The apparatus of claim 12, wherein the program code is further configured to use the first statistic to generate a second statistic for a second temporary table.

22. The apparatus of claim 12, wherein the program code is further configured to save the first statistic in at least one of the first temporary table or an access plan.

23. A program product, comprising:

(a) program code configured to optimize a database query, where at least one field is materialized into a temporary table during execution of the database query, by generating a statistic for the temporary table prior to creation of the temporary table, and optimizing the database query using the statistic; and

(b) a computer readable signal bearing medium bearing the program code.

24. The program product of claim 23, wherein the computer readable signal bearing medium includes at least one of a transmission medium and a recordable medium.