If we rewrite the query to use a direct call to JSON_QUERY, the index is used as expected, but only if we match all parameters of the function call. The query using the direct JSON_VALUE call found no suitable function-based index using JSON_VALUE, so no index was used. Since the index creation and the query both used dot notation, which was transformed to a JSON_QUERY call, the index was used as expected. RETURNING VARCHAR2(4000) ERROR ON at the predicate information in the execution plan produced by the query using dot notation, we can see a reference to the JSON_QUERY function, which is what we would expect from a typical query transformation of dot notation. |* 1 | TABLE ACCESS FULL| JSON_DOCUMENTS | 1 | 1499 | 4 (0)| 00:00:01 |ġ - filter(JSON_VALUE("DATA" FORMAT JSON, '$.ContactDetails.Email' ON json_documents a (a.) Ģ - access(JSON_QUERY("DATA" FORMAT JSON, '$.ContactDetails.Email' RETURNING VARCHAR2(4000)ĪSIS WITHOUT ARRAY WRAPPER NULL ON AUTOTRACE TRACE EXPLAIN Notice how the index is used when querying using the dot notation, but not when using the direct JSON_VALUE call. We can see a typical example of a query transformation of dot notation in the following example, which uses the dot notation to create the index. Normally we see dot notation references transformed into JSON_QUERY calls (as described here), but in this case, the optimizer has performed a query transformation to a JSON_VALUE call to take advantage of the index. WHERE a. = Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ĮRROR ON that although we use the dot notation, the predicate is actually treated as a JSON_VALUE call to enable it to use the index. Predicate Information (identified by operation id):Ģ - access(JSON_VALUE("DATA" FORMAT JSON, '$.ContactDetails.Email' RETURNING VARCHAR2(4000)ĮRROR ON same index is also used if we query the table using the dot notation. | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DOCUMENTS | 1 | 1499 | 2 (0)| 00:00:01 | WHERE JSON_VALUE(data, '$.ContactDetails.Email' RETURNING VARCHAR2 ERROR ON ERROR) = Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ON json_documents (JSON_VALUE(data, '$.ContactDetails.Email' RETURNING VARCHAR2 ERROR ON ERROR)) The execution plan shows the index is used by a suitable query. The following example creates a function-based index on the email element of the JSON documents using the JSON_VALUE function. The examples in this article assume the following table as been created.ĬONSTRAINT json_documents_pk PRIMARY KEY (id),ĬONSTRAINT json_documents_json_chk CHECK (data IS JSON) You will probably not want to consider these for highly volatile tables. The same rules apply with respect to the maintenance of bitmap and full-text indexes.You will definitely not be able to index every possible combination. Slight changes in parameters can make the index unavailable for a specific query. During this article you will see the optimizer is very picky about using JSON indexes.If the JSON documents are very large, the overhead of indexing is likely to be even greater.The majority of the JSON indexes are function-based indexes, which means the maintenance overhead is event higher than a regular B*Tree index. You have to weigh up the overhead of indexing on DML performance against the improved query performance. Adding indexes to any table will affect the performance of DML against it, as the indexes have to be maintained.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |