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!