Improving a multi-tenant database query using contextual knowledge about tenant data

• US 10,152,508 B2
• Filed: 04/29/2013
• Issued: 12/11/2018
• Est. Priority Date: 09/23/2003
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Moellenhoff, Dave, Wong, Simon
• Primary Examiner(s): Waldron, Scott A.
• Assistant Examiner(s): Cheung, Hubert
• Application Number:
  US13/873,146
• Publication Number:
  US 20130246450A1
• Time in Patent Office: 2,052 Days
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24575 using context
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

Abstract

In embodiments, methods and systems for improving a query in a database system are provided. These method and system embodiments can enable greater contextual knowledge about the types and use of data in tables underlying a relational database to be employed to improve query efficiency. By employing contextual information, embodiments can provide improved queries and/or make recommendations to a query optimizer of a database system to improve its operation based upon knowledge of the data and/or application gathered. Embodiments can be useful in improving query performance in multi-tenant database systems.

Claims

1. A non-transitory computer readable medium having computer instructions stored therein that when executed by a computer system cause the computer system to perform operations comprising:

for each of at least two tenants in a multi-tenant database, computing the selectivity of different portions of that tenant'"'"'s tenant-specific data based on at least two characteristics of that tenant'"'"'s tenant-specific data other than characteristics identifying a user or tenant'"'"'s access to data;

storing an indication of the computed selectivity of different portions of that tenant'"'"'s tenant-specific data;

subsequent to the storing, receiving a query directed to tenant-specific data in the multi-tenant database of a particular one of the at least two tenants, wherein the query includes a plurality of query elements and has an associated query plan usable to implement the query;

evaluating the associated query plan based on the stored indication of the computed selectivity of different portions of the particular tenant'"'"'s tenant-specific data;

based on the evaluating, modifying an ordering of query elements specified in the associated query plan to generate a modified query plan usable to implement the received query, wherein modification of the query plan is performed such that a first query element that is more selective of the particular tenant'"'"'s tenant-specific data than a second query element is reordered within the query plan to be performed before the second query element; and

providing, in place of the query plan, the modified query plan.

2. The non-transitory computer readable medium of claim 1, the operations further including storing at least one metadata table for use in determining the modified query plan.

3. The non-transitory computer readable medium of claim 2, wherein each of the stored metadata tables is associated with each of the at least two tenants.

4. The non-transitory computer readable medium of claim 2, wherein the at least one metadata table is used as a filter.

5. The non-transitory computer readable medium of claim 2, wherein the tenant-specific data is stored in the at least one metadata table for use in determining the modified query plan.

6. The non-transitory computer readable medium of claim 1, wherein a particular logical portion of the multi-tenant database includes one or more particular rows of the multi-tenant database, but not one or more other rows of the multi-tenant database.

7. The non-transitory computer readable medium of claim 1, wherein the selectivity of different portions of the tenant-specific data is based on the cardinality of the different portions of the tenant-specific data.

8. The non-transitory computer readable medium of claim 1, wherein the first query element is a first filter and the second query element is a second filter;

the operations further comprising determining, based on the stored indication of the computed selectivity of different portions of the particular tenant'"'"'s tenant-specific data, that the first filter is more selective of the particular tenant'"'"'s tenant-specific data than the second filter.

9. The non-transitory computer readable medium of claim 8, wherein receiving the query includes receiving the query from a particular user associated with the particular tenant, wherein the particular user has access to a first potion of the particular tenant'"'"'s tenant-specific data but not a second portion of the particular tenant'"'"'s tenant-specific data, and wherein the first filter passes the first portion and filters out the second portion.

10. A method, comprising:

for each of at least two tenants in a multi-tenant database, computing, with a computer system, the selectivity of different portions of that tenant'"'"'s tenant-specific data based on at least two characteristics of that tenant'"'"'s tenant-specific data other than characteristics identifying a user or tenant'"'"'s access to data;

storing an indication of the computed selectivity of different portions of that tenant'"'"'s tenant-specific data;

subsequent to the storing, receiving a query directed to tenant-specific data in the multi-tenant database of a particular one of the at least two tenants, wherein the query includes a plurality of query elements and has an associated query plan usable to implement the query;

evaluating the associated query plan based on the stored indication of the computed selectivity of different portions of the particular tenant'"'"'s tenant-specific data;

based on the evaluating, modifying an ordering of query elements specified in the associated query plan to generate a modified query plan usable to implement the received query, wherein modification of the query plan is performed such that a first query element that is more selective of the particular tenant'"'"'s tenant-specific data than a second query element is reordered within the query plan to be performed before the second query element; and

executing the modified query plan in place of the query plan.

11. The method of claim 10, wherein the multi-tenant database includes a multi-tenant database table that is configured to allow each of the at least two tenants to access certain particular logical portions but not other logical portions.

12. The method of claim 10,

wherein the stored indication of selectivity of a particular tenant'"'"'s tenant specific data is a denormalized index table for the particular tenant'"'"'s tenant-specific data,

wherein the first query element is a first filter and the second query element is a second filter, and

wherein evaluating the associated query plan based on the stored indication of the computed selectivity of different portions of the particular tenant'"'"'s tenant-specific data includes determining, based on the denormalized index table, that the first filter is more selective of the particular tenant'"'"'s tenant-specific data than the second filter.

13. The method of claim 10,

wherein a first one of the different portions of a particular tenant'"'"'s tenant-specific data includes a first plurality of values,

wherein the stored indication of the computed selectivity of the computed selectivity of different portions of that tenant'"'"'s tenant-specific data includes statistics about respective proportions of the first plurality of values that are repeated occurrences of respective particular values, and

wherein the evaluating is based on the statistics about respective proportions of the first plurality of values that are repeated occurrences of respective particular values.

14. A non-transitory computer readable medium having computer instructions stored therein that when executed by a computer system cause the computer system to perform operations comprising, comprising:

for each of at least two tenants of a multi-tenant database, computing the selectivity of different portions of that tenant'"'"'s tenant-specific data based on at least two characteristics of that tenant'"'"'s tenant-specific data other than characteristics identifying a user or tenant'"'"'s access to data;

storing an indication of the computed selectivity of different portions of that tenant'"'"'s tenant-specific data;

subsequent to the storing, receiving a query directed to tenant-specific data in the multi-tenant database of a particular one of the at least two tenants, the query including a plurality of query elements and having an associated query plan usable to implement the query;

evaluating the associated query plan based on the stored indication of the computed selectivity of different portions of the particular tenant'"'"'s tenant-specific data;

based on the evaluating, modifying an ordering of query elements specified in the associated query plan to generate a modified query plan usable to implement the received query, wherein modification of the query plan is performed such that a first query element that is more selective of the particular tenant'"'"'s tenant-specific data than a second query element is reordered within the query plan to be performed before the second query element; and

executing the modified query plan in place of the query plan.

15. The non-transitory computer readable medium of claim 14, wherein the multi-tenant database includes a multi-tenant database table that is configured to allow each of the at least two tenants to access certain particular logical portions but not other logical portions.

16. The non-transitory computer readable medium of claim 15, wherein each of the respective different logical portions of the multi-tenant database table comprises one or more distinct rows of the multi-tenant database table.