Using Index Hints
In the previous section, both indexes are applicable. For index idx_income, the query condition income > 10000000 can be used as the starting point for an index scan that will retrieve only the index entries and associated table rows that satisfy this condition. Similarly, for index idx_age, the condition age < 40 can be used as the stopping point for the index scan. SQL for Oracle NoSQL Database has no way of knowing which of the 2 predicates is more selective, and it assigns the same "value" to each index, eventually picking the one whose name is first alphabetically. In the previous example, idx_age was used. To choose the idx_income index instead, the query should be written with an index hint:
sql-> SELECT /*+ FORCE_INDEX(Persons idx_income) */ * from Persons
WHERE income > 10000000 and age < 40;
> Row 0
+-------------+--------------------------------------------+
| id | 3 |
+-------------+--------------------------------------------+
| firstname | John |
+-------------+--------------------------------------------+
| lastname | Morgan |
+-------------+--------------------------------------------+
| age | 38 |
+-------------+--------------------------------------------+
| income | 100000000 |
+-------------+--------------------------------------------+
| lastLogin | 2016-11-29T08:21:35.4971 |
+-------------+--------------------------------------------+
| address | street | 187 Aspen Drive |
| | city | Middleburg |
| | state | FL |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 305 |
| | number | 1234079 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 2066401 |
+-------------+--------------------------------------------+
| connections | 1 |
| | 4 |
| | 2 |
+-------------+--------------------------------------------+
| expenses | food | 2000 |
| | gas | 10 |
| | travel | 700 |
+-------------+--------------------------------------------+
1 row returned As shown above, hints are written as a special kind of comment that must be placed immediately after the SELECT keyword. What distinguishes a hint from a regular comment is the "+" character immediately after (without any space) the opening "/*".