JSON Index Non Reuse

Indexes on JSON data elements  can be used by all SQL JSON functions (JSON_VALUE, JSON_QUERY, etc) using that element as criteria.  But the match must be exact including the RETURNING and ERROR clause. Here’s an example of and index not being re-used.

--
-- Verify the virtual column used for the index
--
SQL> SELECT index_name,
 2          column_name
 3     FROM user_ind_columns
 4    WHERE index_name = 'JSON_COL_IX';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
JSON_COL_IX          SYS_NC00002$

--
-- Verify the JSON index. It uses JSON_QUERY on the
-- COL element in the JSON_COL column. It uses the default
-- return type of VARCHAR2(30) and the default error
-- behavior of NULL ON ERROR
--
SQL> SELECT data_default
 2     FROM user_tab_cols
 3    WHERE column_name = ( SELECT column_name
 4                            FROM user_ind_columns
 5                           WHERE index_name = 'JSON_COL_IX' );
DATA_DEFAULT
-------------------------------------------
JSON_QUERY("JSON_COL" FORMAT JSON , '$.col'
RETURNING VARCHAR2(4000) ASIS WITHOUT ARRAY
WRAPPER NULL ON ERROR)

--
-- Run a JSON_VALUE query that could use the index
-- because it is the same column after all. But it
-- won't because the RETURNING clause differs from the
-- index one (10 characters versus 4000)
--
SQL> SELECT JSON_VALUE(jd.json_col,'$.col') col
 2     FROM json_data jd
 3    WHERE JSON_VALUE(jd.json_col,'$.col'
 4                     RETURNING VARCHAR2(10) ) = 'val33';
COL
----------
val33

--
-- Create an explain plan for the JSON_VALUE query
--
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col') col
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5                     RETURNING VARCHAR2(10) ) = 'val33';
Explained.

--
-- Check the explain plan. Sure enough the index was
-- not used.
--
SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3213740116
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| JSON_DATA | 1 | 2002 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter(JSON_VALUE("JD"."JSON_COL" FORMAT JSON , '$.col'
 RETURNING VARCHAR2(10) NULL ON ERROR)='val33')
Note
-----
 - dynamic statistics used: dynamic sampling (level=2)
14 rows selected.

The index did not get used because changing the length of the return type is enough to make Oracle unsure the index will contain all of the possible values. In the next post I’ll delve into this in more detail.

Thanks for reading!

JSON Index Reuse

The first post in this series created an index using Dot Notation to specify the JSON element to index. Behind the scenes Oracle translated the Dot Notation to a JSON_QUERY call to build the index. Subsequent queries using Dot Notation undergo the same translation to use the index.

Can other functions such as JSON_VALUE use the index too? Let’s dive into an example to find out. We’ll use index created in the first post here.

-- Query details of the index column
SQL> SELECT index_name,
 2          column_name
 3     FROM user_ind_columns
 4    WHERE index_name = 'JSON_COL_IX';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
JSON_COL_IX SYS_NC00002$

-- View the JSON_QUERY used for the index
SQL> SELECT data_type,
 2          data_length,
 3          data_default
 4     FROM user_tab_cols
 5    WHERE column_name = ( SELECT column_name
 6                            FROM user_ind_columns
 7                           WHERE index_name = 'JSON_COL_IX' );
DATA_TYPE LNGTH DATA_DEFAULT
--------------- ----- -------------------------
VARCHAR2 4000 JSON_QUERY("JSON_COL" FORMAT JSON , '$.col'
              RETURNING VARCHAR2(4000) ASIS
              WITHOUT ARRAY WRAPPER NULL ON ERROR
1 row selected.

-- Get the query plan for a dot notation query
SQL> EXPLAIN PLAN FOR
 2   SELECT *
 3     FROM json_data jd
 4    WHERE jd.json_col.col = 'val33';
Explained.

--
-- Review the explain plan to verify the index was used
-- Note the "access" closely matches the index column
-- "DATA_DEAFULT" shown above. That's why the index was used
--
SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 716575500
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4004 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 4004 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_COL_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_QUERY("JSON_COL" FORMAT JSON , '$.col' 
            RETURNING VARCHAR2(4000) ASIS
            WITHOUT ARRAY WRAPPER NULL ON ERROR)='val33')
Note
-----
 - dynamic statistics used: dynamic sampling (level=2)

15 rows selected.

-- Now use JSON_VALUE to find the same record with
-- essentially the same query as above
SQL> SELECT JSON_VALUE(jd.json_col,'$.col' )
 2     FROM json_data jd
 3    WHERE JSON_VALUE(jd.json_col,'$.col') = 'val33';
JSON_VALUE(JD.JSON_COL,'$.COL')
------------------------------------------------------------------------------------------------------------------------------------
val33

-- Get the explain plan for the JSON_VALUE query
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') = 'val33';
Explained.

--
-- Check the query plan to see if the index was used.
-- It was! Note the "filter" shown is directly from the
-- query text while the "access" is what Oracle used to
-- find the row.
--
SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 716575500
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4004 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 4004 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_COL_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter(JSON_VALUE("JD"."JSON_COL" FORMAT JSON , '$.col'
            RETURNING VARCHAR2(4000)
            NULL ON ERROR)='val33')
 2 - access(JSON_QUERY("JSON_COL" FORMAT JSON , '$.col'
            RETURNING VARCHAR2(4000) ASIS
            WITHOUT ARRAY WRAPPER NULL ON ERROR)='val33')
Note
-----
 - dynamic statistics used: dynamic sampling (level=2)
17 rows selected.

This proves that JSON indexes usage is not always restricted to the specific JSON syntax used to create it. It will reuse it if possible. In the next post I’ll discuss cases where it may not be used.

Thanks for reading.

JSON Index 1

Welcome to the introductory post of a new series on JSON indexes in the Oracle database. This post shows how JSON indexes are created and used in SQL queries. As always – lets get right to the code!

SQL> CREATE TABLE json_data
 2   ( json_col CLOB,
 3       CONSTRAINT json_only CHECK ( json_col IS JSON ) );
Table created.

-- Put 100 simple JSON values into the demo table
SQL> DECLARE
 2     v_val VARCHAR2(100);
 3   BEGIN
 4     FOR counter IN 1..100 LOOP
 5       v_val := '{"col":"val' || counter || '"}';
 6       INSERT INTO json_data
 7       VALUES(v_val);
 8     END LOOP;
 9   END;
 10 /
PL/SQL procedure successfully completed.

-- Create an index using dot notation to denote the
-- JSON key to index. In this case its the "col" key in the
-- JSON data stored in the "json_col" column of the
-- "json_data" table.
SQL> CREATE INDEX json_col_ix ON json_data jd
 2   ( jd.json_col.col );
Index created.

-- The index is implemented via a virtual column added
-- to the json_data_table. In this case the virtual column
-- is named SYS_NC00002$
SQL> SELECT index_name,
 2          column_name
 3     FROM user_ind_columns
 4    WHERE index_name = 'JSON_COL_IX';

INDEX_NAME      COLUMN_NAME
--------------- ---------------
JSON_COL_IX     SYS_NC00002$
1 row selected.

-- Here are the details of the virtual column. Note the
-- dot notation index was translated into a call
-- to the JSON_QUERY function.
SQL> SELECT data_type,
 2          data_length,
 3          data_default
 4     FROM user_tab_cols
 5    WHERE column_name = ( SELECT column_name
 6                            FROM user_ind_columns
 7                           WHERE index_name = 'JSON_COL_IX' );
TYPE     LNGTH DATA_DEFAULT
-------- ----- -------------------------
VARCHAR2 4000 JSON_QUERY("JSON_COL" FORMAT JSON ,
              '$.col' RETURNING VARCHAR2(4000) ASIS
              WITHOUT ARRAY WRAPPER NULL ON ERROR)
1 row selected.

-- Now to query a value from the table using dot notation
-- in the where clause
SQL> SELECT *
 2     FROM json_data jd
 3    WHERE jd.json_col.col = 'val33';

JSON_COL
---------------
{"col":"val33"}
1 row selected.

-- Now use explain plan to verify if the index was used
SQL> EXPLAIN PLAN FOR
 2   SELECT *
 3     FROM json_data jd
 4    WHERE jd.json_col.col = 'val33';
Explained.

-- Viewing the plan shows the index was used!
SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 716575500
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0  | SELECT STATEMENT | | 1 | 4004 | 2 (0)| 00:00:01 |
| 1  | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 4004 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_COL_IX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_QUERY("JSON_COL" FORMAT JSON ,
     '$.col' RETURNING VARCHAR2(4000) ASIS
     WITHOUT ARRAY WRAPPER NULL ON ERROR)='val33')
Note
-----
 - dynamic statistics used: dynamic sampling (level=2)

Its important to note that dot notation was translated into JSON_QUERY twice:

  • When the index was created
  • When the WHERE clause was specified

This ensures the comparison will be “apples to apples” to allow the index to be used. The next few posts in this series will explore just how easy it is to wind up comparing “oranges to apples” rendering indexes unused.

Thanks for reading!