Deriving session level statistics for temporary tables

• US 7,860,833 B1
• Filed: 11/28/2007
• Issued: 12/28/2010
• Est. Priority Date: 11/28/2007
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Korlapati, Ramakrishna
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): DANG, THANH HA T
• Application Number:
  US11/946,640
• Time in Patent Office: 1,126 Days
• Field of Search: 707/2, 707/10, 707/637, 707/660, 707/713, 707/714, 707/715, 707/716, 707/717, 707/718, 707/731, 707/999.002, 707/999, 707/101
• US Class Current: 707/637
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 16/2477 Temporal data queries

Abstract

Statistics for temporary tables are used in optimizing the execution of a database request. A first temporary table is created using data from a data source as the result of performing an operation while executing the request. A subset of the demographics of the data source is saved in a session level memory as the demographics of the first temporary table. The saved demographics are used in optimizing the execution of the portion of the request involving the first temporary table.

Claims

1. A method for using statistics for temporary tables in optimizing the execution of a database request, the method comprising:

creating a first temporary table using data from a data source as the result of performing an operation while executing the database request;

after creating the first temporary table, saving a subset of demographics of the data source in a session level memory, which is memory available to a session with which the database request is associated for a life of the session, as the demographics of the first temporary table; and

using the saved demographics of the first temporary table in optimizing the execution of a portion of the database request involving the first temporary table.

2. The method of claim 1, wherein the data source is a single table without any filtering conditions and saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

saving histograms from the single table as the demographics of the first temporary table.

3. The method of claim 1, wherein the data source is a single table with filter conditions and wherein saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

saving summary demographics of columns of the single table as the demographics of the first temporary table.

4. The method of claim 1, wherein the data source is a plurality of tables accessed when executing the database request and wherein saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

saving summary demographics of columns of the plurality of the tables as the demographics of the first temporary table.

5. The method of claim 1, wherein the data source is a plurality of sources for inserts and selects and wherein saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

merging individual demographics from the plurality of sources as the demographics of the first temporary table.

6. The method of claim 1, further comprising:

creating a second temporary table using data from the second temporary table as a result of performing an operation on the first temporary table while executing the database request;

saving a subset of the demographics of the first temporary table in a session level memory as the demographics of the second temporary table; and

using the saved demographics of the second temporary table in optimizing the execution of a portion of the database request involving the second temporary table.

7. A database system comprising:

one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of virtual processes, each of the one or more CPUs providing access to one or more virtual processes;

each virtual process configured to manage data, including rows from the set of database table rows, stored in one of a plurality of data-storage facilities;

a process configured to use statistics for temporary tables in optimizing the execution of a database request by;

creating a first temporary table using data from a data source as the result of performing an operation while executing the database request;

after creating the first temporary table, saving a subset of demographics of the data source in a session level memory, which is memory available to a session with which the database request is associated for a life of the session, as the demographics of the first temporary table; and

using the saved demographics of the first temporary table in optimizing the execution of a portion of the database request involving the first temporary table.

8. The database system of claim 7, wherein the data source is a single table without any filtering conditions and saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

saving histograms from the single table as the demographics of the first temporary table.

9. The database system of claim 7, wherein the data source is a single table with filter conditions and wherein saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

saving summary demographics of columns of the single table as the demographics of the first temporary table.

10. The database system of claim 7, wherein the data source is a plurality of tables accessed when executing the database request and wherein saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

saving summary demographics of columns of the plurality of the tables as the demographics of the first temporary table.

11. The database system of claim 7, wherein the data source is a plurality of sources for inserts and selects and wherein saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table comprises:

merging individual demographics from the plurality of sources as the demographics of the first temporary table.

12. The database system of claim 7, wherein the process further comprises:

creating a second temporary table using data from the second temporary table as a result of performing an operation on the first temporary table while executing the database request;

saving a subset of the demographics of the first temporary table in a session level memory as the demographics of the second temporary table; and

using the saved demographics of the second temporary table in optimizing the execution of a portion of the database request involving the second temporary table.

13. A computer program, stored in a tangible medium, for using statistics for temporary tables in optimizing the execution of a database request, the program comprising executable instructions that cause a computer to:

create a first temporary table using data from a data source as the result of performing an operation while executing the database request;

after creating the first temporary table, save a subset of demographics of the data source in a session level memory, which is memory available to a session with which the database request is associated for a life of the session, as the demographics of the first temporary table; and

use the saved demographics of the first temporary table in optimizing the execution of a portion of the database request involving the first temporary table.

14. The computer program of claim 13, wherein the data source is a single table without any filtering conditions and when saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table, the computer:

saves histograms from the single table as the demographics of the first temporary table.

15. The computer program of claim 13, wherein the data source is a single table with filter conditions and when saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table, the computer:

saves summary demographics of columns of the single table as the demographics of the first temporary table.

16. The computer program of claim 13, wherein the data source is a plurality of tables accessed when executing the database request and when saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table, the computer:

saves summary demographics of columns of the plurality of the tables as the demographics of the first temporary table.

17. The computer program of claim 13, wherein the data source is a plurality of sources for inserts and selects and when saving the subset of demographics of the data source in the session level memory as the demographics of the first temporary table, the computer:

merges individual demographics from the plurality of sources as the demographics of the first temporary table.

18. The computer program of claim 13, the program further comprising executable instructions that cause the computer to:

create a second temporary table using data from the second temporary table as a result of performing an operation on the first temporary table while executing the database request;

save a subset of the demographics of the first temporary table in a session level memory as the demographics of the second temporary table; and

use the saved demographics of the second temporary table in optimizing the execution of a portion of the database request involving the second temporary table.