Statistics mechanisms in multitenant database environments

• US 9,760,594 B2
• Filed: 02/17/2016
• Issued: 09/12/2017
• Est. Priority Date: 07/03/2012
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Kim, Hoon, Collins, Jesse, Rajan, Chirag
• Primary Examiner(s): LE, HUNG D
• Application Number:
  US15/046,338
• Publication Number:
  US 20160162536A1
• Time in Patent Office: 573 Days
• Field of Search: None
• CPC Class Codes: G06F 16/16 File or folder operations, e.g. details of user interfaces specifically adapted to file systems
• CPC Class Codes: G06F 16/2246 Trees, e.g. B+trees
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/285 Clustering or classification

Abstract

Statistics mechanisms in multitenant database environments. A master statistics file is maintained in a multitenant database system. The master statistics file has statistics corresponding to multiple tenants within the multitenant database system. Statistics for a selected table within the multitenant database system are generated. The selected table corresponding to a selected tenant of the multitenant database system. The master statistics file is updated based on the generated statistics for the selected table.

Claims

1. A method comprising for providing statistical information within a database environment having one or more hardware computing devices coupled with at least one memory device to provide multiple instances of a database within the database environment, the method comprising:

maintaining, within the database environment, a master statistics file having statistics corresponding to multiple tenants within the database environment;

determining, with the one or more hardware computing devices, a selected table from the database based on one or more parameters by parsing the master statistics file into a sorted list in which each key can map to at most one value;

generating, with the one or more hardware computing devices, one or more statistical values for the selected table;

modifying the master statistics file with the one or more hardware computing devices to rewrite statistical values in the master statistics file with generated statistical values for the selected table to generate an updated master statistics file; and

utilizing, with the one or more hardware computing devices, the updated master statistics file for operation of the database environment.

2. The method of claim 1 wherein the one or more statistical values generated for the selected table comprises at least an average number of leaf blocks per key.

3. The method of claim 1 wherein the one or more statistical values generated for the selected table comprises at least an average number of data blocks per key.

4. The method of claim 1 wherein the one or more statistical values generated for the selected table comprises at least a clustering factor.

5. The method of claim 1 wherein the one or more statistical values generated for the selected table comprises at least a B-tree level.

6. The method of claim 1 wherein the one or more statistical values generated for the selected table comprises at least a number of distinct keys for one or more columns within the selected table, a number of nulls in the one or more columns within the selected table, and/or an average column length for the selected table.

7. The method of claim 1 wherein the one or more statistical values generated for the selected table comprises at least a highest value for one or more columns within the selected table, and/or a lowest value for one or more columns within the selected table.

8. A non-transitory computer-readable medium having stored thereon instructions that, when executed by one or more hardware computing devices, are configurable to cause the one or more hardware computing devices to provide statistical information within a database environment having multiple instances of a database within the database environment, the instructions configurable to cause the one or more hardware computing devices to:

maintain a master statistics file having statistics corresponding to multiple tenants within the database environment;

determine a selected table from the database based on one or more parameters by parsing the master statistics file into a sorted list in which each key can map to at most one value;

generate one or more statistical values for the selected table;

modify the master statistics file to rewrite statistical values in the master statistics file with generated statistical values for the selected table to generate an updated master statistics file; and

utilize the updated master statistics file for operation of the multitenant environment.

9. The non-transitory computer-readable medium of claim 8 wherein the one or more statistical values generated for the selected table comprises at least an average number of leaf blocks per key.

10. The non-transitory computer-readable medium of claim 8 wherein the one or more statistical values generated for the selected table comprises at least an average number of data blocks per key.

11. The non-transitory computer-readable medium of claim 8 wherein the one or more statistical values generated for the selected table comprises at least a clustering factor.

12. The non-transitory computer-readable medium of claim 8 wherein the one or more statistical values generated for the selected table comprises at least a B-tree level.

13. The non-transitory computer-readable medium of claim 8 wherein the one or more statistical values generated for the selected table comprises at least a number of distinct keys for one or more columns within the selected table, a number of nulls in the one or more columns within the selected table, and/or an average column length for the selected table.

14. The non-transitory computer-readable medium of claim 8 wherein the one or more statistical values generated for the selected table comprises at least a highest value for one or more columns within the selected table, and/or a lowest value for one or more columns within the selected table.

16. The system of claim 8 wherein the one or more statistical values generated for the selected table comprises at least an average number of leaf blocks per key.

17. The system of claim 8 wherein the one or more statistical values generated for the selected table comprises at least an average number of data blocks per key.

18. The system of claim 8 wherein the one or more statistical values generated for the selected table comprises at least a clustering factor.

19. The system of claim 8 wherein the one or more statistical values generated for the selected table comprises at least a B-tree level.

15. A system configurable to cause the one or more hardware computing devices to provide statistical information within a database environment having multiple instances of a database within the database environment, the system comprising:

a memory device;

one or more hardware computing devices coupled with the memory device, the one or more hardware computing devices configurable to maintain a master statistics file having statistics corresponding to multiple tenants within the database environment, to determine a selected table from the database based on one or more parameters by parsing the master statistics file into a sorted list in which each key can map to at most one value, to generate one or more statistical values for the selected table, to modify the master statistics file to rewrite statistical values in the master statistics file with generated statistical values for the selected table to generate an updated master statistics file, and to utilize the updated master statistics file for operation of the database environment.

20. The system of claim 15 wherein the one or more statistical values generated for the selected table comprises at least a number of distinct keys for one or more columns within the selected table, a number of nulls in the one or more columns within the selected table, and/or an average column length for the selected table.

21. The system of claim 15 wherein the one or more statistical values generated for the selected table comprises at least a highest value for one or more columns within the selected table, and/or a lowest value for one or more columns within the selected table.