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.