Methods and systems for providing multiple column custom indexes in a multi-tenant database environment

• US 10,417,611 B2
• Filed: 10/04/2010
• Issued: 09/17/2019
• Est. Priority Date: 05/18/2010
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Collins, Jesse, Wong, Simon Y, Bathija, Jaikumar, O'Brien, John F
• Primary Examiner(s): Harper, Eliyah S.
• Application Number:
  US12/897,276
• Publication Number:
  US 20110289091A1
• Time in Patent Office: 3,270 Days
• Field of Search: 707687, 707705, 707802
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06Q 10/10 Office automation; Time management

Abstract

In accordance with embodiments, there are provided methods and systems for providing multiple column custom indexes in a multi-tenant database environment. A method embodiment provides defining a multi-tenant data structure having a plurality of data columns data fields and a plurality of rows for each of multiple tenants, each row including a data column for a tenant identifier, defining a first data field for a tenant, the first data field having a first data type, defining a second data field for the tenant, the second field having a second data type, and defining an index table including a tenant identifier for the tenant, a copy of data from the first data field and the second data field, and a key to the corresponding rows of the multi-tenant data structure.

Claims

1. A database system-implemented method for providing custom indexes in a multi-tenant database environment, the method comprising:

reducing, by a custom index computing device, sorting and scanning operations involving data columns and rows during query executions for multiple tenants within the multi-tenant database environment using a multi-tenant data structure having a primary table, wherein the multi-tenant database environment includes the custom index computing device having one or more processing devices and associated memory devices, and a multi-tenant database system coupled to the custom index computing device, wherein the multi-tenant database system includes one or more database storing the multi-tenant data structure and having data resources shared by the multiple tenants, wherein reducing comprises;

maintaining, by the custom index computing device, a first column and a second column in the primary table of the multi-tenant data structure stored at the multi-tenant database system, wherein maintaining includes defining a first data field for a first data type associated with the first column, and a second data field for a second data type associated with the second column, wherein the first column corresponds to first data of the first data type corresponding to a first tenant of multiple tenants within the multi-tenant environment, wherein the second column corresponds to second data of the second data type corresponding to a second tenant of the multiple tenants;

maintaining, by the custom index computing device, custom fields in the custom field table of the multi-tenant data structure stored at the multi-tenant database system, wherein maintaining includes assigning tenant-defined indexing to the custom fields such that the custom fields offer tenant-defined indexing of the first and second data persisting in the database;

maintaining, by the custom index computing device, the index table separate from and associated with the primary table, wherein maintaining includes assigning a tenant identifier of the first tenant and a second tenant identifier of the second tenant to the index table, wherein the first tenant identifier and the second tenant identifier correspond to the first column and the second column, respectively, of the primary table;

sorting, by the custom index computing device, the index table based on the first column and the second column of the primary table, and the custom fields of the custom field table; and

causing, by the custom index computing device, the sorted index table to be stored at one or more of the associated memory devices, wherein the key comprises a primary key, wherein the tenant-defined indexing of the data includes tenant-based designation for types of the data to be held by one or more of the custom fields or one or more of the data columns such that an entire data column is capable of representing a single data type, where the single data type includes one or more of a prefix, a suffix, a customer identification, a name, and a title.

2. The method of claim 1, wherein one of the first data field and the second data field indicates a time.

3. The method of claim 1, wherein the index table further comprises an index field.

4. The method of claim 1, wherein the index table comprises columns in the multi-tenant data structure.

5. The method of claim 1, wherein the index table is independent of the multi-tenant data structure.

6. The method of claim 1, further comprising applying a query to the index table to locate rows of the multi-tenant data structure using the sort order of the index table.

7. The method of claim 6, wherein applying further comprises looking up rows in the index table to identify a group of rows related to the query, using the sort order of the rows to select a desired group of rows, and applying the keys for the desired group of rows to read data from the multi-tenant data structure.

8. The method of claim 1, further comprising maintaining the index table by copying data from the multi-tenant data structure to the first and second data fields and sorting the rows of the index table using the copied data.

13. The method of claim 1, further comprising defining a metadata catalog having at least one metadata table, the metadata of the table defining the data fields of the index table.

14. The method of claim 1, wherein values of the copy of data from at least one of the first data field and the second data field are converted to a different format to improve searching.

15. The method of claim 1, wherein the index table further includes additional fields that are not included in the primary table.

9. A non-transitory machine-readable medium having stored thereon instructions when executed by a processing device, cause the processing device to perform operations comprising:

reducing sorting and scanning operations involving data columns and rows during query executions for multiple tenants within a multi-tenant database environment using a multi-tenant data structure having a primary table, wherein the multi-tenant database environment includes the custom index computing device having one or more processing devices and associated memory devices and a multi-tenant database system coupled to the custom index computing device, wherein the multi-tenant database system includes one or more database storing the multi-tenant data structure and having data resources shared by the multiple tenants, wherein reducing comprises;

maintaining a first column and a second column in the primary table of the multi-tenant data structure stored at the multi-tenant database system, wherein maintaining includes defining a first data field for a first data type associated with the first column, and a second data field for a second data type associated with the second column, wherein the first column corresponds to first data of the first data type corresponding to a first tenant of multiple tenants within the multi-tenant, wherein the second column corresponds to second data of the second data type corresponding to a second tenant of the multiple tenants;

maintaining custom fields in the custom field table of the multi-tenant data structure stored at the multi-tenant database system, wherein maintaining includes assigning tenant-defined indexing to the custom fields such that the custom fields offer tenant-defined indexing of the first and second data persisting in the database;

maintaining the index table for each tenant separate from and associated with the primary table, wherein maintaining includes assigning a tenant identifier of the first tenant and a second tenant identifier of the second tenant to the index table, wherein the first tenant identifier and the second tenant identifier correspond to the first column and the second column, respectively, of the primary table;

sorting the index table based on the first column and the second column of the primary table and the custom fields of the custom field table; and

causing the sorted index table to be stored at one or more of the associated memory devices, wherein the key comprises a primary key, wherein the tenant-defined indexing of the data includes tenant-based designation for types of the data to be held by one or more of the custom fields or one or more of the data columns such that an entire data column is capable of representing a single data type, where the single data type includes one or more of a prefix, a suffix, a customer identification, a name, and a title.

10. The machine-readable medium of claim 9, wherein the index table further comprises an index field.

11. The machine-readable medium of claim 9, wherein the index table comprises columns in the multi-tenant data structure.

12. The machine-readable medium of claim 9, wherein the index table is independent of the multi-tenant data structure.

16. The machine-readable medium of claim 9, wherein the operations further comprise defining a metadata catalog having at least one metadata table, the metadata of the table defining the data fields of the index table, wherein values of the copy of data from at least one of the first data field and the second data field are converted to a different format to improve searching, wherein the index table further includes additional fields that are not included in the primary table.

17. A database system for providing custom indexes in a multi-tenant database environment having a custom index computing device having one or more processing devices and associated memory devices, the one or more processing devices to perform operations comprising:

reducing sorting and scanning operations involving data columns and rows during query executions for multiple tenants within a multi-tenant database environment using a multi-tenant data structure having a primary table, a custom field table, and an index table, wherein the multi-tenant database environment includes the custom index computing device having one or more processing devices and associated memory devices, and a multi-tenant database system coupled to the custom index computing device, wherein the multi-tenant database system includes one or more databases storing the multi-tenant data structure and having resources shared by the multiple tenants, wherein reducing comprises;

maintaining a first column and a second column in the primary table of the multi-tenant data structure stored at the multi-tenant database system, wherein maintaining includes defining a first data field for a first data type associated with the first column, and a second data field for a second data type associated with the second column, wherein the first column corresponds to first data of the first data type corresponding to a first tenant of multiple tenants within the multi-tenant, wherein the second column corresponds to second data of the second data type corresponding to a second tenant of the multiple tenants;

maintaining a second column in the custom field table of the multi-tenant data structure stored at the multi-tenant database system, wherein maintaining includes assigning tenant-defined indexing to the custom fields such that the custom fields offer tenant-defined indexing of the first and second data persisting in the database;

maintaining the index table for each tenant separate from and associated with the primary table, wherein maintaining includes assigning a tenant identifier of the first tenant and a second tenant identifier of the second tenant to the index table, wherein the first tenant identifier and the second tenant identifier correspond to the first column and the second column, respectively, of the primary table;

sorting the index table based on the first column and the second column of the primary table and the custom fields of the custom field table; and

causing the sorted index table to be stored at one or more of the associated memory devices, wherein the key comprises a primary key, wherein one of the first data field and the second data field indicates a time, wherein the index table further comprises an index field, wherein the tenant-defined indexing of the data includes tenant-based designation for types of the data to be held by one or more of the custom fields or one or more of the data columns such that an entire data column is capable of representing a single data type, where the single data type includes one or more of a prefix, a suffix, a customer identification, a name, and a title.

18. The database system of claim 17, wherein the index table comprises columns in the multi-tenant data structure, wherein the index table is independent of the multi-tenant data structure.

19. The database system of claim 17, wherein the operations further comprise applying a query to the index table to locate rows of the multi-tenant data structure using the sort order of the index table, wherein applying further comprises looking up rows in the index table to identify a group of rows related to the query, using the sort order of the rows to select a desired group of rows, and applying the keys for the desired group of rows to read data from the multi-tenant data structure.

20. The database system of claim 17, wherein the operations further comprise maintaining the index table by copying data from the multi-tenant data structure to the first and second data fields and sorting the rows of the index table using the copied data.