JSON Index Relies On Left Side Of Criteria

This post further explains how JSON queries and indexes interact with the Oracle optimizer.

The code shown below relies on the indexes created in the previous post here.

Here’s a quick summary of the indexes:

JSON_4
JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
   RETURNING VARCHAR2(4)...

JSON_5
JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
   RETURNING VARCHAR2(5)..

JSON_6
JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
   RETURNING VARCHAR2(6)...

And now for some code!

SQL> --
SQL> -- Oracle uses the JSON_4 index for this query because
SQL> -- it matches the left side of the criteria even though
SQL> -- it will not find return the row with val100
SQL> --
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col' )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5                     RETURNING VARCHAR2(4) ) = 'val100';
Explained.

SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2835480143
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2006 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 2006 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_4 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
            RETURNING VARCHAR2(4) NULL ON ERROR)='val100')
SQL> --
SQL> -- Running the query does not find return the row
SQL> --
SQL> SELECT JSON_VALUE(jd.json_col,'$.col' )
 2     FROM json_data jd
 3    WHERE JSON_VALUE(jd.json_col,'$.col'
 4                     RETURNING VARCHAR2(4) ) = 'val100';
no rows selected

SQL> --
SQL> -- Another example - Oracle uses the JSON_6 index even
SQL> -- though the JSON_4 index MIGHT be better suited
SQL> -- to finding the val9 row
SQL> --
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col' )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5                     RETURNING VARCHAR2(6) ) = 'val9';
Explained.

SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 32193117
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2007 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 2007 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_6 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
            RETURNING VARCHAR2(6) NULL ON ERROR)='val9')

SQL> --
SQL> -- Running the query DOES find the row
SQL> --
SQL> SELECT JSON_VALUE(jd.json_col,'$.col' )
 2     FROM json_data jd
 3    WHERE JSON_VALUE(jd.json_col,'$.col'
 4          RETURNING VARCHAR2(6) ) = 'val9';

JSON_VALUE(JD.JSON_COL,'$.COL')
--------------------------------------------------------
val9

If this was not JSON data then the Oracle optimizer would switch to another index or a full table scan in order to find the row that satisfied the criteria. But JSON is different as the examples show.

In the next post I’ll show some final examples to tie it all together.

Thanks for reading!