Query optimization in a multi-tenant database system

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.

What is claimed is: 
     
       1. A method of optimizing a query in a multi-tenant database, said multi-tenant 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 one or more tenants, wherein a plurality of tenants have data stored in the data tables, the method comprising:
 determining database indices for one or more of the data tables; 
 generating tenant-level statistics for one or more of said plurality of tenants for one or more of the data tables; 
 receiving a SQL query; 
 optimizing the SQL query based on the database indices; and 
 optimizing the SQL query based on the tenant-level statistics, thereby enabling SQL query optimization according to greater semantic knowledge of use of the data tables. 
 
     
     
       2. The method of  claim 1 , wherein each tenant includes one or more associated users, the method further including:
 generating user-level statistics for one or more of the users of one or more of the tenants for one or more of the data tables; and 
 optimizing the SQL query based on the user-level statistics. 
 
     
     
       3. The method of  claim 2 , wherein the user-level statistics are stored to a user metadata table. 
     
     
       4. The method of  claim 2 , wherein generating user-level statistics includes determining a total number of distinct rows for each of said plurality of users. 
     
     
       5. The method of  claim 4 , wherein the total number is an approximate number based on one or more of a) a number of rows viewable by the user and users below the user in a role hierarchy, b) a number of rows that are shared by a group to which the user belongs and c) a number of rows that are manually shared to the user by another user or group of users. 
     
     
       6. The method of  claim 2 , wherein generating user-level statistics for a user is performed according to one of a) on a scheduled basis, b) after a predetermined number of queries by the user, and c) each time an unconstrained query is run by the user. 
     
     
       7. The method of  claim 1 , wherein generating tenant-level statistics is performed on a periodic basis. 
     
     
       8. The method of  claim 1 , wherein generating includes determining a total number of distinct rows accessible for each of said plurality of tenants. 
     
     
       9. The method of  claim 8 , wherein the tenant-level statistics are stored to a tenant metadata table. 
     
     
       10. The method of  claim 1 , wherein at least one column of one of said tables includes data associated with two or more tenants. 
     
     
       11. A multi-tenant database system, comprising:
 a database having one or more data tables, each table having one or more columns defining data categories and one or more rows associated with one or more tenants, wherein a plurality of tenants have data stored in the data tables; 
 a database indices determining module configured to generate database indices for one or more of the data tables of the multi-tenant database; 
 a tenant-level statistics generating module configured to generate tenant-level statistics for one or more tenants for one or more of the data tables; 
 a database indices optimization module, configured to optimize a database query on the database indices; and 
 a tenant-level query optimization module, configured to optimize a database query based on the tenant-level statistics. 
 
     
     
       12. The multi-tenant database system of  claim 11 , wherein each tenant includes one or more associated users, wherein the statistics generating module is further configured to generate user-level statistics for each user, and wherein the query optimization module is further configured to optimize the database query based on the user-level statistics. 
     
     
       13. The system of  claim 12 , further including a memory module, wherein the statistics generating module stores the user-level statistics to a metadata table in the memory module. 
     
     
       14. The system of  claim 12 , wherein the statistics generating module generates user-level statistics by determining a total number of distinct rows for each of said plurality of users. 
     
     
       15. The system of  claim 14 , wherein the total number is an approximate number based on one or more of a) a number of rows viewable by the user and users below the user in a role hierarchy, b) a number of rows that are shared by a group to which the user belongs and c) a number of rows that are manually shared to the user by another user or group of users. 
     
     
       16. The system of  claim 12 , wherein the statistics generating module generates user-level statistics for a user according to one of a) on a scheduled basis, b) after a predetermined number of queries by the user, and c) each time an unconstrained query is run by the user. 
     
     
       17. The system of  claim 11 , wherein the statistics generating module generates tenant-level statistics on a periodic basis. 
     
     
       18. The system of  claim 11 , wherein the statistics generating module generates tenant-level statistics by determining a total number of distinct rows viewable for each of said plurality of tenants. 
     
     
       19. The system of  claim 18 , further including a memory module, wherein the statistics generating module stores the tenant-level statistics to a tenant metadata table in the memory module. 
     
     
       20. The system of  claim 11 , wherein at least one column of one of said tables includes data associated with two or more tenants. 
     
     
       21. A method of optimizing a query in a multi-tenant database, said 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 one or more tenants, wherein a plurality of tenants have data stored in the data tables, and wherein each tenant includes one or more users, the method comprising:
 processing the data tables so as to determine tenant-level statistics for each of said plurality of tenants; 
 processing the data tables so as to determine user-level statistics for each of said plurality of users; 
 receiving a SQL query; and 
 optimizing the SQL query based on one or both of the tenant-level statistics and the user-level statistics. 
 
     
     
       22. The method of  claim 21 , further including:
 storing the user-level statistics to a user-level metadata table in a memory module; and 
 storing the tenant-level statistics to a tenant-level metadata table in the memory module. 
 
     
     
       23. The method of  claim 21 , wherein determining user-level statistics includes determining a total number of distinct rows for each of said plurality of users, and wherein determining tenant-level statistics includes determining a total number of distinct rows for each of said plurality of tenants. 
     
     
       24. The method of  claim 21 , wherein processing the data tables to determine tenant-level statistics is performed on a periodic basis. 
     
     
       25. The method of  claim 21 , wherein processing the data tables to determine user-level statistics for a user is performed according to one of a) on a scheduled basis, b) after a predetermined number of queries by the user, and c) each time an unconstrained query is run by the user. 
     
     
       26. The method of  claim 1 , wherein the generating tenant-level statistics comprises generating tenant-level statistics for each of said plurality of tenants for each of the data tables. 
     
     
       27. The method of  claim 2 , wherein the generating user-level statistics comprises generating user-level statistics for each of the users of each of the tenants for each of the data tables. 
     
     
       28. The multi-tenant database system of  claim 11 , wherein the statistics generating module is configured to generate tenant-level statistics for each tenant for each of the data table. 
     
     
       29. The method of  claim 1 , further comprising:
 determining a particularity of a query being executed; and 
 conducting at least one of optimizing the SQL query and generating statistics based on the particularity of the query being executed. 
 
     
     
       30. The method of  claim 1 , further comprising:
 determining application information corresponding an application for which the multi-tenant database is used; and 
 optimizing the SQL query based on the application information. 
 
     
     
       31. The method of  claim 30 , wherein the application information includes data sharing among at least two users of at least one tenant. 
     
     
       32. The method of  claim 31 , wherein the optimizing the SQL query based on the application information includes determining whether to access a database object from a user side or a tenant side. 
     
     
       33. The method of  claim 1 , wherein the optimizing the SQL query based on the tenant-level statistics modifies the optimization of the SQL query based on the database indices, thereby enabling the tenant-level statistics to tune query optimization otherwise provided by the optimizing the SQL query based on the table-level statistics. 
     
     
       34. The multi-tenant database system of  claim 11 , wherein the tenant-level query optimization module modifies the optimization of the SQL query by the database indices optimization module, thereby enabling the tenant-level statistics to tune query optimization otherwise provided by the table-level query optimization module.