Statistics management for database querying

• US 9,189,521 B2
• Filed: 10/02/2013
• Issued: 11/17/2015
• Est. Priority Date: 06/21/2011
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Rajan, Chirag, Collins, Jesse, Yang, Seung-Yeoul, Kim, Hoon
• Primary Examiner(s): Nguyen, Loan T
• Application Number:
  US14/044,657
• Publication Number:
  US 20140040235A1
• Time in Patent Office: 776 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2455 Query execution

Abstract

Methods and systems are provided for querying a database. One exemplary method for obtaining data from an on-demand database supporting one or more tenants involves obtaining model database statistics based on expected utilization information for a tenant and providing the model database statistics to the on-demand database. The on-demand database utilizes the model database statistics to generate a query plan and executes the query plan to obtain data from the model database statistics.

Claims

1. A method of obtaining data from a database, the database including a query optimizer to generate a query plan based on on-demand database statistics, the method comprising:

obtaining model database statistics representative of an expected state of the database in the future, the model database statistics being generated based on a subset of expected utilization information for a tenant supported by the database, the expected utilization information representing the tenant'"'"'s likely usage of a table in the database at a time in the future, wherein generating the model database statistics comprises;

obtaining a subset of database statistics that are most likely to influence the query optimizer; and

generating the model database statistics that are likely to influence the query optimizer based on the subset of database statistics and the subset of the expected utilization information; and

modifying on-demand database statistics maintained by the database to reflect the model database statistics, wherein the query plan generated by the database to obtain the data based on the on-demand database statistics is influenced by the model database statistics.

2. The method of claim 1, wherein the expected utilization information includes an expected amount of data entries for the table, an expected number of distinct values for a column of the table, a minimum value for a column of the table, a maximum value for a column of the table, a percentage of values in a particular column that are null, a number of data blocks in the table, a depth of a B-tree, or a clustering factor.

3. The method of claim 1, wherein the table comprises a future table.

4. The method of claim 3, wherein the expected utilization information includes an expected amount of data entries for the future table, an expected number of distinct values for a column of the future table, a minimum value of a column of the future table, a maximum value for a column of the future table, or a number of data blocks in the future table.

5. The method of claim 1, wherein the expected utilization information corresponds to a new table to be created in the database.

6. The method of claim 1, further comprising obtaining the expected utilization information for the database from the tenant.

7. The method of claim 1, further comprising determining the expected utilization information for the database based at least in part on existing utilization information for the tenant.

8. The method of claim 7, wherein:

determining the expected utilization information comprises projecting the expected utilization information at the time in the future based on the tenant'"'"'s utilization history for the table.

9. The method of claim 1, further comprising:

obtaining a subset of database statistics determined by a database management system based on the expected utilization information; and

creating the model database statistics based at least in part on the obtained subset of database statistics determined by the database management system.

10. The method of claim 9, wherein creating the model database statistics comprises creating an extensible markup language (XML) file including the obtained subset of database statistics determined by the database management system.

11. The method of claim 10, further comprising:

maintaining the XML file including the obtained subset of database statistics determined by the database management system; and

maintaining a second XML file containing reference database statistics for the database, wherein;

obtaining the model database statistics comprises modifying the second XML file to reflect the XML file; and

providing the model database statistics comprises automatically modifying the on-demand database statistics maintained by the database to reflect the second XML file after modifying the second XML file, the database generating the query plan based on the on-demand database statistics maintained by the database.

12. The method of claim 1, the database maintaining database statistics for a first table of the database, wherein:

obtaining the model database statistics for the database comprises obtaining the model database statistics for the first table based on the expected utilization information for the tenant for the first table; and

providing the model database statistics comprises modifying the database statistics to reflect the model database statistics.

13. The method of claim 1, wherein the model database statistics are representative of an expected state of the database in the future.

14. The method of claim 1, wherein generating the model database statistics comprises augmenting the subset of database statistics utilizing the subset of the expected utilization information.

15. The method of claim 1, wherein providing the model database statistics comprises an application server modifying the on-demand database statistics maintained by the database to match reference database statistics after updating the reference database statistics in response to identifying a difference between the model database statistics and the reference database statistics.

16. The method of claim 1, wherein obtaining the model database statistics comprises generating the model database statistics based on the expected utilization information without consuming processing resources of the database.

17. A method of obtaining data from a database, the method comprising:

generating model database statistics having values representative of an anticipated state of the database in the future based on expected utilization information for a tenant supported by the database, wherein generating the model database statistics comprises;

obtaining a subset of database statistics determined by a database management system based on the expected utilization information; and

creating the model database statistics having the values representative of the anticipated state of the database based at least in part on the obtained subset of database statistics determined by the database management system; and

providing the model database statistics to the database by modifying on-demand database statistics maintained by the database to reflect the model database statistics, wherein;

a query optimizer of the database generates a query plan based on the on-demand database statistics; and

the query plan generated by the database to obtain the data is influenced by the model database statistics.

18. A system comprising:

a database to maintain on-demand database statistics, the database including a query optimizer to generate a query plan based on the on-demand database statistics; and

a server coupled to the database to obtain model database statistics for the database and provide the model database statistics to the database by modifying the on-demand database statistics maintained by the database to reflect the model database statistics, wherein;

the model database statistics are representative of an expected state of the database in the future and are generated based on a subset of expected utilization information for a tenant supported by the database and a subset of database statistics that are most likely to influence the query optimizer;

the expected utilization information represents the tenant'"'"'s likely usage of a table in the database at a time in the future; and

the query plan to obtain data that is generated by the database based on the on-demand database statistics is influenced by the model database statistics.

19. The system of claim 18, wherein the table comprises a future table.

20. The system of claim 18, wherein the expected utilization information corresponds to a new table to be created in the database.