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

Patent metadata

Field	Value
Publication number	US-10417611-B2
Application number	US-89727610-A
Country	US
Kind code	B2
Filing date	Oct 4, 2010
Priority date	May 18, 2010
Publication date	Sep 17, 2019
Grant date	Sep 17, 2019

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.

First claim

What is claimed is:

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.

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.

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 th

Assignees

• Collins Jesse
• Wong Simon Y
• Bathija Jaikumar
• Obrien John F
• Salesforce Com Inc

Inventors

• Collins Jesse
• Wong Simon Y
• Bathija Jaikumar
• O'Brien John F

Classifications

• G06F16/2228

  Indexing structures · CPC title

• G06Q10/10 Primary

  Office automation; Time management · CPC title