Implementing composite custom indices in a multi-tenant database

Patent metadata

Field	Value
Publication number	US-10229157-B2
Application number	US-89726510-A
Country	US
Kind code	B2
Filing date	Oct 4, 2010
Priority date	Oct 5, 2009
Publication date	Mar 12, 2019
Grant date	Mar 12, 2019

Abstract

Methods and systems for query optimization for a multi-tenant database system are provided. Some embodiments comprise receiving, at a network interface of a server computer in a multi-tenant database system, an original query transmitted to the multi-tenant database system by a user associated with a tenant, wherein the original query is associated with data accessible by the tenant, and wherein the multi-tenant database system includes a first two-column custom index. Metadata associated with the data accessible by the tenant is retrieved, using a processor of the server computer, wherein at least a portion of the data accessible by the tenant is stored in a common table within the multi-tenant database system. A tenant-selective query syntax is determined, using the processor, wherein determining comprises analyzing at least one of metadata generated from information about the tenant or metadata generated from the data accessible by the tenant. Finally, using the processor, an improved query is generated using the query syntax, wherein the improved query is based at least in part upon the original query and data stored in the first two-column custom index.

First claim

What is claimed is:

1. A computer-implemented method of improving a query, the computer system having one or more processors and at least one accessible memory device configurable to provide a multitenant database system, the method comprising:
maintaining, with the one or more processors, in a database of the multitenant database system, contextual metadata for multiple tenants of the multitenant database system, wherein different tenants of the multi-tenant database system have different schemas corresponding to the different tenants;
receiving, with the one or more processors, an original query transmitted to the multi-tenant database system by a user associated with a tenant, wherein the original query is associated with data accessible by the tenant and is stored in the multitenant database system, and wherein the multi-tenant database system utilizes a first two-column custom index associated with data stored therein;
retrieving, with the one or more processors from the multitenant database system, tenant-level contextual metadata associated with the data accessible by the tenant in the multi-tenant database system, wherein at least a portion of the data accessible by the tenant is stored in a shared table that is shared between tenants within the multi-tenant database system, and further wherein rows of the data accessible by the tenant is filtered by a user filter to provide a private sharing feature to allow a specific list of users to have access to specific subsets of data within the data accessible by the tenant;
determining, with the one or more processors, a tenant-selective query syntax, by analyzing contextual metadata generated from information about the tenant and metadata generated from the data accessible only by the tenant and metadata related to the user filter, wherein the metadata comprises at least row ownership information;
generating, with the one or more processors, an improved query, wherein the improved query is based at least in part upon the original query, the data stored in the first two-column custom index and the tenant-selective query syntax, and the improved query is generated prior to submission to an underlying database manager, wherein when a query requests a sort based on one column and requests only a subset of rows, the query is rewritten to use the ordering of the two-column custom index; and
executing, with the one or more processors, the improved query on data stored in the database of the multitenant database system.

2. The method of claim 1 , wherein the multi-tenant database system includes a second two-column custom index, and wherein the first two-column custom index includes an index on a particular column, and wherein the second two-column custom index includes an index on the particular column.

3. The method of claim 1 , further comprising:
receiving information identifying the user;
retrieving, using the processor, metadata about the user; and wherein determining, using the processor, comprises analyzing at least one of the group consisting of metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant.

4. The method of claim 1 , further comprising:
calculating selectivity for one or more columns of data accessible by the tenant; and
wherein determining, using the processor, comprises analyzing the selectivity of the one or more columns of data accessible by the tenant.

5. The method of claim 1 , wherein one column of the two-column custom index is an index on a foreign key.

6. A non-transitory computer-readable medium containing program code executable by one or more processors and at least one accessible memory device configurable to provide a multitenant database system to improve a query in the multi-tenant database system, the program code including instructions configurable to:
maintaining, with the one or more processors, in a database of the multitenant database system, contextual metadata for multiple tenants of the multitenant database system, wherein different tenants of the multi-tenant database system have different schemas corresponding to the different tenants;
retrieve, with the one or more processors from the multitenant database system, tenant-level contextual metadata associated with the data accessible by the tenant in the multi-tenant database system, wherein at least a portion of the data accessible by the tenant is stored in a shared table that is shared between tenants within the multi-tenant database system, and further wherein rows of the data accessible by the tenant is filtered by a user filter to provide a private sharing feature to allow a specific list of users to have access to specific subsets of data within the data accessible by the tenant;
determine, with the one or more processors, a tenant-selective query syntax, by analyzing contextual metadata generated from information about the tenant and metadata generated from the data accessible only by the tenant and contextual metadata related to the user filter, wherein the metadata comprises at least row ownership information;
generate, with the one or more processors, an improved query, wherein the improved query is based at least in part upon the original query, the data stored in the first two-column custom index and the tenant-selective query syntax, and the improved query is generated prior to submission to an underlying database manager, wherein when a query requests a sort based on one column and requests only a subset of rows, the query is rewritten to use the ordering of the two-column custom index; and
execute, with the one or more processors, the improved query on data stored in the database of the multitenant database system.

7. The non-transitory computer-readable medium of claim 6 , wherein the multi-tenant database system includes a second two-column custom index, and wherein the first two-column custom index includes an index on a particular column, and wherein the second two-column custom index includes an index on the particular column.

8. The non-transitory computer-readable medium of claim 6 , wherein the program code further includes instructions to:
receive information identifying the user; retrieve, using the processor, metadata about the user; and
wherein the instructions to determine, using the processor, comprise instructions to analyze at least one of the group consisting of metadata generated from information about the user, metadata generated from information about the tenant, and metadata generated from the data accessible by the tenant.

9. The non-transitory computer-readable medium of claim 6 , wherein the program code further includes instructions to:
calculate selectivity for one or more columns of data accessible by the tenant; and
wherein the instructions to determine, using the processor, comprise instructions to analyze the selectivity of the one or more columns of data accessible by the tenant.

10. The non-transitory computer-readable medium of claim 6 , wherein one column of the two-column custom index is an index on a foreign key.

11. A system for improving a query in a multi-tenant database system, the system comprising:
a processor;
a network interface; and
a non-transitory computer-readable medium tangibly embodied with instructions configurable to:
maintain, with the one or more processors, in a database of the multitenant database system, contextual metadata for multiple tenants of the multitenant database system, wherein different tenants of the multi-tenant database system have different schemas corresponding to the different tenants;
receive, with the one or more processors, an original qu

Assignees

• Collins Jesse
• Weissman Craig
• Salesforce Com Inc

Inventors

• Collins Jesse
• Weissman Craig

Classifications

• G06F16/2453

  Query optimisation · CPC title

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/2462

  Approximate or statistical queries · CPC title

• G06F17/30442

  Physics · mapped topic

• G06F17/30463 Primary

  Physics · mapped topic