Static query optimization

Patent metadata

Field	Value
Publication number	US-10901991-B2
Application number	US-201615011277-A
Country	US
Kind code	B2
Filing date	Jan 29, 2016
Priority date	Jun 23, 2010
Publication date	Jan 26, 2021
Grant date	Jan 26, 2021

Abstract

Techniques and structures for tuning database queries. Actual statistics associated with data stored are retrieved from at least one memory device. The data is associated with one or more tenants of the multi-tenant database system. The actual statistics include at least one type of statistic maintained for the data. A subset of the actual statistics is selected based on a data trait targeted for optimization. A statistical value is calculated for each actual statistic from the subset of actual statistics across one or more tenants having the data trait targeted for optimization. An optimal query plan is determined based on the original query and the calculated statistical values.

First claim

What is claimed is:

1. A method comprising:
retrieving, by a query tuning and processing device in a multi-tenant database environment, actual statistics associated with data stored on at least one memory device, wherein the data is associated with tenants in the multi-tenant database environment, wherein the tenants include one or more tenants having one or more data traits targeted for query optimization, wherein a data trait includes at least one of: (1) a high volume of transactions; (2) a high number of transactions involving large file sizes; (3) a high number of resource-intensive transactions; (4) a high utilization of a rarely-used column or table; and (5) a high utilization of a rarely-used resource;
selecting, by the query tuning and processing device, a subset of the actual statistics based on a portion of the data associated with the one or more tenants, wherein the subset includes actual statistical values associated with the one or more tenants;
computing, by the query tuning and processing device, synthetic statistics for the one or more tenants based on the actual statistical values and the data traits such that a subset of the synthetic statistics is computed based on a predetermined value associated with the subset of the actual statistics, wherein the predetermined value corresponds to an exemplary value based on a historical association of a data trait with a tenant of the one or more tenants, wherein the tenant is regarded as an exemplary tenant based on the data trait; and
in response to receiving one or more queries from the one or more tenants, based on the synthetic statistics, determining, by the query tuning and processing device, one or more query plans suitable for optimally processing the one or more queries on behalf of the one or more tenants.

2. The method of claim 1 wherein the synthetic statistics are further based on an average value associated with the subset of the actual statistics.

3. The method of claim 1 wherein the exemplary value is computed based on multiple values comprising a highest value associated with the exemplary tenant having the data trait targeted for optimization.

4. The method of claim 3 wherein the multiple values further comprise a lowest value associated with the exemplary tenant having the data trait targeted for optimization.

5. A non-transitory computer-readable medium having stored thereon instructions that, when executed by one or more processors coupled with a memory device, cause the one or more processors to tune queries in a multi-tenant database environment, the one or more processors to perform operations comprising:
retrieving actual statistics associated with data stored on at least one memory device, wherein the data is associated with one or more tenants of tenants in the multi-tenant database environment, wherein the tenants include one or more tenants having one or more data traits targeted for query optimization, wherein a data trait includes at least one of: (1) a high volume of transactions; (2) a high number of transactions involving large file sizes; (3) a high number of resource-intensive transactions; (4) a high utilization of a rarely-used column or table; and (5) a high utilization of a rarely-used resource; selecting a subset of the actual statistics based on a portion of the data associated with the one or more tenants, wherein the subset includes actual statistical values associated with the one or more tenants; computing synthetic statistics for the one or more tenants based on the actual statistical values and the data traits such that a subset of the synthetic statistics is computed based on a predetermined value associated with the subset of the actual statistics, wherein the predetermined value corresponds to an exemplary value based on a historical association of a data trait with a tenant of the one or more tenants, wherein the tenant is regarded as an exemplary tenant based on the data trait; and in response to receiving one or more queries from the one or more tenants, determining, based on the synthetic statistics, one or more query plans suitable for optimally processing the one or more queries on behalf of the one or more tenants.

6. The non-transitory computer-readable medium of claim 5 wherein the synthetic statistics are further based on the subset of the actual statistics.

7. The non-transitory computer-readable medium of claim 5 wherein the exemplary value is computed based on multiple values comprising a highest value associated with the exemplary tenant having the data trait targeted for optimization.

8. The non-transitory computer-readable medium of claim 7 wherein the multiple values further comprise a lowest value associated with the exemplary tenant based on the data trait targeted for optimization.

9. A database system comprising: one or more memory devices; one or more processors coupled with the one or more memory devices to tune queries in a multi-tenant database environment, the one or more processors to: retrieve actual statistics associated with data stored on at least one memory device, wherein the data is associated with one or more tenants of tenants of the multi-tenant database environment, wherein the tenants include one or more tenants having one or more data traits targeted for query optimization, wherein a data trait includes at least one of: (1) a high volume of transactions; (2) a high number of transactions involving large file sizes; (3) a high number of resource-intensive transactions; (4) a high utilization of a rarely-used column or table; and (5) a high utilization of a rarely-used resource; select a subset of the actual statistics based on a portion of the data associated with the one or more tenants, wherein the subset includes actual statistical values associated with the one or more tenants; compute synthetic statistics for the one or more tenants based on the actual statistical values and the data traits such that a subset of the synthetic statistics is computed based on a predetermined value associated with the subset of the actual statistics, wherein the predetermined value corresponds to an exemplary value based on a historical association of a data trait with a tenant of the one or more tenants, wherein the tenant is regarded as an exemplary tenant based on the data trait; and in response to receiving one or more queries from the one or more tenants, determine, based on the synthetic statistics, one or more query plans suitable for optimally processing the one or more queries on behalf of the one or more tenants.

10. The system of claim 9 wherein the synthetic statistics are further based on the subset of actual statistics.

11. The system of claim 9 wherein the exemplary value is computed based on multiple values comprising a highest value associated with the exemplary tenant having the data trait targeted for optimization.

12. The system of claim 11 wherein the multiple values further comprise a lowest value associated with the exemplary tenant having the data trait targeted for optimization.

Assignees

• Salesforce Com Inc
• Salesforce Com

Inventors

• Collins Jesse
• Bathija Jaikumar
• O'Brien John F

Classifications

• G06F16/2453 Primary

  Query optimisation · CPC title

• G06F16/2462

  Approximate or statistical queries · CPC title

• G06F16/24542 Primary

  Plan optimisation · CPC title