Query optimization in a multi-tenant database system

• US 8,620,954 B2
• Filed: 04/16/2010
• Issued: 12/31/2013
• Est. Priority Date: 09/23/2003
• Status: Expired due to Term

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Moellenhoff, Dave, Wong, Simon, Nakada, Paul
• Primary Examiner(s): PHAM, KHANH B
• Application Number:
  US12/762,247
• Publication Number:
  US 20100235837A1
• Time in Patent Office: 1,355 Days
• Field of Search: 707781-786, 707/713
• US Class Current: 707/783
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

The present invention provides techniques for managing process space on a multi-tenant database system that features a method that provides to multiple users of two or more organizations, over a network, access to information stored in a database repository shared by the two or more organizations. The database repository implements security protocols to restrict a set of first users to a first sub-portion of the information. The set of first users belongs to one of the two or more organizations. The first sub-portion of the information is different from a second sub-portion accessible by users from another organization. Access of the information by the multiple users is monitored. A first processing space for the set of first users is generated in response to the access of the database repository. The first processing space is distinct from a second processing space for the users from the other organization.

Claims

1. A method in a Multitenant Database System, the method comprising:

receiving a request for access to privileged data via a sharing feature of the Multitenant Database System, the request received at an interface of the Multitenant Database System;

maintaining sharing information within a Many-to-Many table, wherein the Many-to-Many table specifies whether a user has access to the privileged data, and wherein tenant data for each of a plurality of tenants is arranged so that data of one tenant is kept separate from tenant data of other tenants, wherein the one tenant does not have access to another tenant'"'"'s data unless the tenant data of the one tenant is expressly shared;

maintaining tenant-level statistics for each of the plurality of tenants having data stored within the Multitenant Database System, the tenant-level statistics kept on a per-tenant basis separate from native statistics aggregated across the plurality of tenants of the Multitenant Database System;

generating a filtered sharing query designed to access the privileged data requested;

optimizing the filtered sharing query based on the tenant-level statistics and the sharing information maintained by the Many-to-Many table to determine a query plan that includes at least whether to access the Many-to-Many table from a user side or an entity side of the relationship to retrieve the privileged data requested; and

performing the optimized filtered sharing query.

2. The method of claim 1, further comprising:

tracking, via metadata, information about;

(a) users, (b) tenants, (c) organizations, and (d) data contained in entity rows for tenants that are tracked.

3. The method of claim 1, further comprising:

evaluating a sharing model which controls which users can see which records.

4. The method of claim 3, further comprising:

tracking, for each user of the Multitenant Database System, a count of the number of rows that can be seen by a user for which a sharing model exists.

5. The method of claim 1, wherein the Multitenant Database System generates statements designed to access the desired information for the requests received comprises using a sharing filter to optimize the statements, wherein the sharing filter is used based on sharing visibility for a user being less than a threshold.

6. The method of claim 1, wherein each server among a plurality of servers is configured to handle requests for any user or organization, wherein no server affinity for a user or organization exists for any of the plurality of servers.

7. The method of claim 6, wherein the lack of server affinity supports adding and removing any of the plurality of servers from a server pool at any time and for any reason.

8. The method of claim 1, wherein the Multitenant Database System includes a tenant database divided into individual tenant storage areas via a physical arrangement or a logical arrangement, and wherein the individual tenant storage areas comprises user storage allocated for each respective user.

9. The method of claim 1:

wherein an overall database structure or schema for the Multitenant Database System is fixed; and

wherein each of the plurality of tenants having data stored within data tables of the Multitenant Database System comprises a tenant-specific virtual schema that describes the logical structure of the respective tenant'"'"'s data stored within the data tables.

10. The method of claim 9, wherein each of the plurality of tenants having data stored within the data tables comprises some data structures which are allocated at a tenant level and other data structures which are managed at a user level.

11. The method of claim 10, wherein the tenant-specific virtual schema comprises a plurality of customizable fields.

12. The method of claim 11, wherein some or all of the plurality of customizable fields are indexable per designation.

13. The method of claim 1:

further comprising generating user-level statistics for each user of each tenant; and

wherein the Multitenant Database System generates statements designed to access the desired information for the requests received comprises the Multitenant Database System optimizing a database query based on the user-level statistics.

14. The method of claim 1, wherein the Multitenant Database System comprises process space for executing Multitenant Database System processes and tenant-specific processes.

15. The method of claim 1, wherein each of a plurality of servers within the Multitenant Database System has access to tenant data stored by the Multitenant Database System for at least the user systems having requests received at the interface of the Multitenant Database System.

16. The method of claim 1:

wherein each of the user systems is one of a desktop personal computer, a workstation, a laptop, a Personal Digital Assistant (PDA), a cell phone, or a wireless device; and

wherein each of the user systems communicate with the interface of the Multitenant Database System via the Internet.

17. The method of claim 16, wherein each of the user systems communicate through a browsing program running as a client of the user system.

18. The method of claim 1, further comprising:

providing web pages, forms, data, and media content to the user systems to support access by the user systems to the Multitenant Database System as tenants of the Multitenant Database System.

19. The method of claim 18, wherein providing the web pages, forms, data, and media content to the user systems further comprises providing security mechanisms to keep each tenant'"'"'s data separate unless shared.

20. The method of claim 19:

wherein the plurality of tenants having data stored within data tables stored at the Multitenant Database System comprises tenants which are competitors; and

wherein providing the security mechanisms comprises security protocols that keep (a) data, (b) applications, and (c) application use separate amongst the plurality of tenants.

21. A non-transitory medium storing program code that, when executed by a central processing unit of a Multitenant Database System, the program code causes the Multitenant Database System to perform operations including:

receiving a request for access to privileged data via a sharing feature of the Multitenant Database System, the request received at an interface of the Multitenant Database System;

maintaining sharing information within a Many-to-Many table, wherein the Many-to-Many table specifies whether a user has access to the privileged data, and wherein tenant data for each of a plurality of tenants is arranged so that data of one tenant is kept separate from tenant data of other tenants, wherein the one tenant does not have access to another tenant'"'"'s data unless the tenant data of the one tenant is expressly shared;

maintaining tenant-level statistics for each of the plurality of tenants having data stored within the Multitenant Database System, the tenant-level statistics kept on a per-tenant basis separate from native statistics aggregated across the plurality of tenants of the Multitenant Database System;

generating a filtered sharing query designed to access the privileged data requested;

optimizing the filtered sharing query based on the tenant-level statistics and the sharing information maintained by the Many-to-Many table to determine a query plan that includes at least whether to access the Many-to-Many table from a user side or an entity side of the relationship to retrieve the privileged data requested; and

performing the optimized filtered sharing query.

22. A Multitenant Database System, comprising:

a database system including one or more data tables, each data table having one or more columns defining data categories and one or more rows associated with one or more tenants among a plurality of tenants having data stored within the data tables;

a plurality of servers to store data to and retrieve data from the database system;

an interface to receive a request for access to privileged data via a sharing feature of the Multitenant Database System, the request received at an interface of the Multitenant Database System;

a Many-to-Many table to maintain sharing information, wherein the Many-to-Many table specifies whether a user has access to the privileged data, and wherein tenant data for each of a plurality of tenants is arranged so that data of one tenant is kept separate from tenant data of other tenants, wherein the one tenant does not have access to another tenant'"'"'s data unless the tenant data of the one tenant is expressly shared;

a query optimizer to optimize a filtered sharing query designed to access the privileged data requested, wherein the query optimizer optimizes optimizing the filtered sharing query based on the tenant-level statistics and the sharing information maintained by the Many-to-Many table to determine a query plan that includes at least whether to access the Many-to-Many table from a user side or an entity side of the relationship to retrieve the privileged data requested; and

wherein the plurality of servers to perform the optimized filtered sharing query.