METHOD, SYSTEM, AND COMPUTER PROGRAM PRODUCT FOR QUERYING IN A MULTI-TENANT DATABASE

• US 20100281014A1
• Filed: 05/07/2010
• Published: 11/04/2010
• Est. Priority Date: 09/23/2003
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Nakada, Paul, Weissman, Craig, Wong, Simon, Moellenhoff, Dave
• Granted Patent: US 8,275,763 B2
• Time in Patent Office: Days
• US Class Current: 707/715
• 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

More efficient querying of a multi-tenant database using dynamic tuning of database indices. A layer of meta-data associates data items with tenants, e.g., via tags, and the meta-data is used to optimize searches by channeling processing resources during a query to only those pieces of data bearing the relevant tenant'"'"'s unique tag.

Claims

1-25. -25. (canceled)

26. A method of querying in a multi-tenant database, the database having one or more data tables, each table having one or more logical columns defining data categories and one or more logical rows associated with a plurality of tenants, the method comprising:

generating tenant-level statistics from data stored in each of the data tables, the data stored in the data tables comprising tenant-specific data, and the data tables being stored in one database;

receiving a SQL query; and

optimizing the SQL query based on the tenant-level statistics.

33. A multi-tenant database system comprising:

a database having one or more data tables, each table having one or more logical columns defining data categories and one or more logical rows associated with a plurality of tenants;

logic for generating tenant-level statistics from data stored in each of the data tables, the data stored in the data tables comprising tenant-specific data, and the data tables being stored in one database;

logic for receiving a SQL query; and

logic for optimizing the SQL query based on the tenant-level statistics.

27. The method of claim 36, wherein the plurality of tenants provide the tenant-specific data in one or more custom fields specific to each of the plurality of tenants.

28. The method of claim 27, wherein the tenant-specific data provided by the plurality of tenants is stored in at least one table that includes only data from one of the plurality of tenants.

29. The method of claim 27 wherein the tenant-level statistics are stored in a separate table using at least one customizable field and wherein the at least one customizable field is indexable.

30. The method of claim 36 further comprising implementing at least one security mechanism to keep the tenant-specific data separate unless the data is shared.

31. The method of claim 30, wherein the at least one security mechanism includes filtering the tenant-specific data based on an access level of the tenant.

32. The method of claim 30, wherein the at least one security mechanism includes encryption.

34. The system of claim 33, wherein the system is operable such that the plurality of tenants provide the tenant-specific data in one or more custom fields that are specific to each of the plurality of tenants.

35. The system of claim 34, wherein the system is operable such that the tenant-specific data provided by the plurality of tenants is stored in at least one table that includes only data from one of the plurality of tenants.

36. The system of claim 34 wherein the system is operable such that the tenant-level statistics are stored in a separate table using at least one customizable field and wherein the system is operable such that the at least one customizable field is indexable.

37. The system of claim 33 further comprising logic for implementing at least one security mechanism to keep the tenant-specific data separate unless the data is shared.

38. The system of claim 37, wherein the system is operable such that the at least one security mechanism includes filtering the tenant-specific data based on an access level of the tenant.

39. The system of claim 37, wherein the system is operable such that the at least one security mechanism includes encryption.

40. A computer program product embodied on a tangible computer-readable medium for querying in a multi-tenant database, the database having one or more data tables, each table having one or more logical columns defining data categories and one or more logical rows associated with a plurality of tenants, the computer program product comprising:

computer code for generating tenant-level statistics from data stored in each of the data tables, the data stored in the data tables comprising tenant-specific data, and the data tables being stored in one database;

computer code for receiving a SQL query; and

computer code for optimizing the SQL query based on the tenant-level statistics.

41. The computer program product of claim 40, wherein the computer program product is operable such that the plurality of tenants provide the tenant-specific data in one or more custom fields that are specific to each of the plurality of tenants.

42. The computer program product of claim 41, wherein the computer program product is operable such that the tenant-specific data provided by the plurality of tenants is stored in at least one table that includes only data from one of the plurality of tenants.

43. The computer program product of claim 41 wherein the computer program product is operable such that the tenant-level statistics are stored in a separate table using at least one customizable field and wherein the computer program product is operable such that the at least one customizable field is indexable.

44. The computer program product of claim 40 further comprising computer code for implementing at least one security mechanism to keep the tenant-specific data separate unless the data is shared.

45. The computer program product of claim 44, wherein the computer program product is operable such that the at least one security mechanism includes filtering the tenant-specific data based on an access level of the tenant.

46. The computer program product of claim 44, wherein the computer program product is operable such that the at least one security mechanism includes encryption.