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!

JSON Index Return Lengths

This is the fourth post investigating nuances of Oracle indexes on JSON data. The previous posts demonstrated how changing the length of returned data in an SQL JSON function call influences Oracle’s choice of index. This post begins to expose why that occurs by creating 3 indexes for 3 different return value lengths and showing how Oracle uses them.

The data set used in these examples was created in the first post here.

--
-- Create an index returning 4 characters
--
SQL> CREATE INDEX json_4 ON json_data jd
 2     ( JSON_VALUE(jd.json_col,'$.col'
 3       RETURNING VARCHAR2(4) ) );
Index created.

--
-- Create an index returning 5 characters
--
SQL> CREATE INDEX json_5 ON json_data jd
 2     ( JSON_VALUE(jd.json_col,'$.col'
 3       RETURNING VARCHAR2(5) ) );
Index created.

--
-- Create an index returning 6 characters
--
SQL> CREATE INDEX json_6 ON json_data jd
 2     ( JSON_VALUE(jd.json_col,'$.col'
 3       RETURNING VARCHAR2(6) ) );
Index created.

--
-- Verify that each index has a different number of rows
-- in it even though its the same table and JSON element.
--
SQL> SELECT index_name,
 2          num_rows
 3     FROM user_indexes
 4    WHERE index_name like 'JSON%';
INDEX_NAME                       NUM_ROWS
------------------------------ ----------
JSON_4                                  9
JSON_5                                 99
JSON_6                                100

--
-- Query the length of the JSON elements to correlate to
-- the number of rows in the index. Note that:
--    JSON_4 includes  9 length 4 rows
--    JSON_5 includes 90 length 5 rows AND
--                     9 length 4 rows
--    JSON_6 includes  1 length 6 rows AND
                      90 length 5 rows AND
                       9 length 4 rows
--
SQL> SELECT LENGTH(JSON_VALUE(jd.json_col,'$.col')) lngth,
 2          COUNT(*)
 3     FROM json_data jd
 4 GROUP BY LENGTH(JSON_VALUE(jd.json_col,'$.col' ))
 5 ORDER BY LENGTH(JSON_VALUE(jd.json_col,'$.col' ));
     LNGTH   COUNT(*)
---------- ----------
         4          9
         5         90
         6          1

--
-- Verify the JSON_4 index is used for VARCHAR2(4)
--
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col'
 3          RETURNING VARCHAR2(4) )
 4     FROM json_data jd
 5    WHERE JSON_VALUE(jd.json_col,'$.col'
 6    RETURNING VARCHAR2(4) ) = 'val9';
Explained.

SQL> SELECT *
 2    FROM TABLE(DBMS_XPLAN.DISPLAY(
 3               format => 'BASIC ROWS COST'))
 4   WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2835480143
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 2 (0)|
| 2 | INDEX RANGE SCAN | JSON_4 | 1 | 1 (0)|
------------------------------------------------------------------------------
8 rows selected.

--
-- Verify the JSON_5 index is used for VARCHAR2(5)
--
SQL> EXPLAIN PLAN FOR
 2    SELECT JSON_VALUE(jd.json_col,'$.col'
 3           RETURNING VARCHAR2(5) )
 4      FROM json_data jd
 5     WHERE JSON_VALUE(jd.json_col,'$.col'
 6     RETURNING VARCHAR2(5) ) = 'val99';

SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY(
 3                format => 'BASIC ROWS COST'))
 4   WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 322595427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 2 (0)|
| 2 | INDEX RANGE SCAN | JSON_5 | 1 | 1 (0)|
------------------------------------------------------------------------------
8 rows selected.

--
-- Verify the JSON_6 index is used for VARCHAR2(6)
--
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col'
 3                     RETURNING VARCHAR2(6) )
 4     FROM json_data jd
 5    WHERE JSON_VALUE(jd.json_col,'$.col'
 6   RETURNING VARCHAR2(6) ) = 'val100';
Explained.

SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY(
 3                format => 'BASIC ROWS COST'))
 4    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1579104487
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)|
| 1 | INDEX RANGE SCAN| JSON_6 | 1 | 1 (0)|
--------------------------------------------------------

This proves how precise one must be when querying JSON data to ensure the index gets used. In the next post I’ll discuss more nuances to be aware of.

Thanks for reading.