30.2 How To Tell Whether a Function-Based Index for JSON Data Is Picked Up
Whether or not a particular index is picked up for a given query is determined by the optimizer. To determine whether a given query picks up a given function-based index, look for the index name in the execution plan for the query.
For example:
-
Given the index defined in Example 30-3, an execution plan for each of the queries in these examples references an index scan with index
po_num_id1: Example 30-5, Example 30-6, Example 30-7, Example 30-8, and Example 30-10 -
Given the index defined in Example 30-14, an execution plan for the queries in examples Example 30-17 and Example 30-18 references an index scan with index
mvi_1.
When a multivalue index is picked up, the execution plan also shows
(MULTI VALUE) for the index range scan, and the filter used in
the plan is JSON_QUERY, not JSON_EXISTS2. If the
execution plan does not use a multivalue index for a given
json_exists query, then the filter is
JSON_EXISTS2.
Parent topic: Indexes for JSON Data