Creating a custom index in a multi-tenant database environment

Patent metadata

Field	Value
Publication number	US-10108648-B2
Application number	US-201213549306-A
Country	US
Kind code	B2
Filing date	Jul 13, 2012
Priority date	Jul 13, 2011
Publication date	Oct 23, 2018
Grant date	Oct 23, 2018

Abstract

Methods and systems are described for creating a custom index in a multi-tenant database environment. In one embodiment, a method includes obtaining query for a multi-tenant database that is recommended as a candidate for creating an additional filter, evaluating the query against criteria to determine whether to select the query for creating the additional filter, and creating the additional filter for the query, if the query is selected.

First claim

What is claimed is:

1. A method comprising:
monitoring queries that are applied to and running on a database;
flagging at least some of the monitored queries that run more slowly than others of the monitored queries as each being recommended as a candidate query for creating an additional filter to enhance the performance of the respective query;
evaluating the candidate queries against criteria in a background process, wherein a first of the criteria is a score for selectivity, the score being a ratio of a number of rows available to the query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on the elapsed time for the query;
ranking the candidate queries based on the evaluating;
selecting a candidate query based on the ranking from the evaluating;
utilizing a message queue structure to enqueue custom index creation to be performed at a later time; and
creating the additional filter for the selected candidate query using copies of data from the database as an index table for use by the selected query when running on the database, wherein the additional filter is to limit the selected query to a subset of rows of the database contained within the index table.

2. The method of claim 1 , wherein a second of the criteria is scan size and wherein evaluating the candidate query comprises determining how many rows of the multi-tenant database are scanned by the candidate query and wherein selecting comprises selecting the candidate query if the number of rows exceeds a threshold.

3. The method of claim 1 , wherein the selectivity criteria include determining how selective the candidate query is in scanning rows of the database and wherein selecting comprises selecting the candidate query if the number of rows and the selectivity score exceed thresholds.

4. The method of claim 3 , wherein a third of the criteria is run time and wherein evaluating the candidate query comprises determining how much time the candidate query takes to run in the multi-tenant database and wherein selecting comprises selecting the candidate query if the number of rows, the selectivity, and the run time exceed thresholds.

5. The method of claim 1 , wherein flagging comprises receiving a ranked set of queries and selecting a subset of the queries that have the highest rank.

6. The method of claim 5 , wherein flagging comprises flagging a plurality of queries from the subset of the queries based on evaluating the queries of the subset of queries.

7. The method of claim 5 , wherein selecting comprises selecting a subset of highest ranked queries for creating an index.

8. The method of claim 1 , wherein creating the additional filter comprises
defining an index table including a tenant identifier for a tenant, a copy of data from a first data field having a first data type, a copy of data from a second data field having a second data type, and a key to the corresponding rows of the database; and
sorting the index table based on the first data field and the second data field.

9. The method of claim 1 , further comprising monitoring the created additional filter for frequency of use and removing the created additional filter if it is not used more frequently than a predefined number of times in a predefined duration of time.

10. A non-transitory machine-readable medium carrying one or more sequences of instructions for creating custom indexes in a database, the database being a multi-tenant database system, which instructions, when executed by one or more processors, cause the one or more processors to carry out the steps of:
monitoring queries that are applied to and running on a database;
flagging at least some of the monitored queries that run more slowly than others of the monitored queries as each being recommended as a candidate query for creating an additional filter to enhance the performance of the respective query;
evaluating the candidate queries against criteria in a background process, wherein a first of the criteria is a score for selectivity, the score being a ratio of a number of rows available to the query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on the elapsed time for the query;
ranking the candidate queries based on the evaluating;
selecting a candidate query based on the ranking from the evaluating;
utilizing a message queue structure to enqueue custom index creation to be performed at a later time; and
creating the additional filter for the selected candidate query using copies of data from the database as an index table for use by the selected query when running on the database, wherein the additional filter is to limit the selected query to a subset of rows of the database contained within the index table.

11. The medium of claim 10 , wherein a second of the criteria is scan size, wherein the second of the criteria is selectivity, wherein evaluating the candidate query comprises determining how many rows of the multi-tenant database are scanned by the candidate query, determining how selective the candidate query is in scanning rows of the multi-tenant database, and wherein selecting comprises selecting the candidate query if the number of rows and the selectivity exceed a threshold.

12. The medium of claim 11 , wherein flagging comprises receiving a set of queries ranked by runtime and selecting a subset of the queries that have the longest runtime.

13. The medium of claim 12 , wherein flagging comprises excluding queries that include a null value from the selected queries.

14. An apparatus for creating custom indexes in a database, the database being a multi-tenant database, the apparatus comprising:
a hardware processor; and
one or more stored sequences of instructions which, when executed by the processor, cause the processor to carry out the steps of:
monitoring queries that are applied to and running on a database;
flagging at least some of the monitored queries that run more slowly than others of the monitored queries as each being recommended as a candidate query for creating an additional filter to enhance the performance of the respective query;
evaluating the candidate queries against criteria in a background process, wherein a first of the criteria is a score for selectivity, the score being a ratio of a number of rows to the query within the database to a number of rows queried from among the available rows multiplied by a scaling factor based on factored by the elapsed time for the query;
ranking the candidate queries based on the evaluating;
selecting a candidate query based on the ranking from the evaluating;
utilizing a message queue structure to enqueue custom index creation to be performed at a later time; and
creating the additional filter for the selected candidate query using copies of data from the database as an index table for use by the selected query when running on the database, wherein the additional filter is to limit the selected query to a subset of rows of the database contained within the index table.

15. The apparatus of claim 14 , wherein the step of creating the additional filter comprises:
defining an index table including a tenant identifier for a tenant, a copy of data from a first data field having a first data type, a copy of data from a second data field having a second data type, and a key to the corresponding rows of the multi-tenant data structure; and
sorting the index table based on the first data field and the second data field.

16. The apparatus of claim 15 , wherein the index table is comprised o

Assignees

• Rajan Chirag
• Dutta Arup
• Obrien John
• Bathija Jaikumar
• Salmon Greg
• Soble Dan
• Penmetsa Ramalinga R
• Kim Hoon
• Jiang Yanan
• Rajan Karthik
• Collins Jesse
• Salesforce Com Inc

Inventors

• Rajan Chirag
• Dutta Arup
• O'Brien John
• Bathija Jaikumar
• Salmon Greg
• Soble Dan
• Penmetsa Ramalinga R
• Kim Hoon
• Jiang Yanan
• Rajan Karthik
• Collins Jesse

Classifications

• G06F17/30442

  Physics · mapped topic

• G06F17/30336 Primary

  Physics · mapped topic

• G06F17/30477

  Physics · mapped topic

• G06F16/2455

  Query execution · CPC title

• G06F16/2453

  Query optimisation · CPC title