Is there a way to force a specific secondary index to be used first in queries in Oracle NoSQL Database?

Question

My application indexes a few fields in the JSON document to create secondary indexes. Below is a sample document. Let’s suppose indexes are created for age and income.

Sample record:

 {  "id":1,  "appUser" : {
       "firstname":"John",
       "lastname":"Doe",
       "age":27,
       "income":150000,
       "lastLogin" : "2020-10-29T18:43:59.8319",
       "address":{"street":"150 Route 2",
             "city":"Antioch",
             "state":"TN",
             "zipcode" : 37013,
             "phones":[{"type":"home", "areacode":423,
                         "number":123456}]
            },
       "connections":[2, 3],
       "expenses":{"food":1000, "gas":180}
   }
 }

Let’s say the query is:

 SELECT * from mySample WHERE income > 300000 and age < 40

During the query process, does Oracle NoSQL Database use its own index ordered search method, i.e., picking the first and second indexes (e.g., income, age or vice versa) in the order to achieve the optimal performance?

If I know the data characteristics of my documents, can I specify and force my own order of indexes for queries?

Answer 1

Oracle NoSQL can use only one index for any query. When multiple indexes are applicable to a query, it uses a heuristic to choose the "best" index. However, the chosen index may not always be the best. This is because the heuristic is not based on index key statistics, and as a result, does not compute predicate selectivity. If the heuristic choice turns out to be the wrong one, Oracle NoSQL supports index hints, that are placed inside the query itself and tell the query processor what index to use.

For the example above, both indexes (on age and income) are equally good, according to the heuristic, and the query processor will chose the one whose name is first alphabetically, say this is the one on age. To force the use of the income index instead, use the following query:

SELECT /*+ FORCE_INDEX(mySample ) */ * from mySample WHERE income > 300000 and age < 40