Skip to Main Content
  • Questions

  • JSON Multi value index in Oracle 21c

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: October 22, 2021 - 3:20 am UTC

Last updated: November 10, 2021 - 4:24 am UTC

Version: 21.0

Viewed 100K+ times! This question is

You Asked

Team,
create table stage as select * from all_objects;

create table t (x json);
insert into t(x)
select json_object( 'owner_name' value owner, 'object_type' value json_arrayagg( json_object( 'object_name' value object_name ,
    'object_type' value object_name,
    'object_id' value object_id ) returning clob ) returning clob)
from stage     
group by owner;
commit;

select jt.*
from t, json_table( x, '$.object_type[*]'
    columns(
        object_id number path '$.object_id') ) jt
where rownum <= 5 ;
create multivalue index t_idx on t t1( t1.x.object_type.object_id.number() );
exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

demo@XEPDB1> set autotrace traceonly exp
demo@XEPDB1> select *
  2  from t
  3  where json_exists( x, '$.object_type?(@.object_id == 75909)' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1148600783

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |   605 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |   605 |     1   (0)| 00:00:01 |
|   2 |   HASH UNIQUE                       |       |     1 |   605 |            |          |
|*  3 |    INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(JSON_QUERY("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type.object_id.number()' RETURNING NUMBER ASIS  WITHOUT ARRAY WRAPPER
              ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=75909)

demo@XEPDB1> 
demo@XEPDB1> select jt.*
  2  from t, json_table( x, '$.object_type[*]'
  3      columns( object_id number path '$.object_id' ) ) jt
  4  where jt.object_id = 75909;

Execution Plan
----------------------------------------------------------
Plan hash value: 1386969201

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    21 | 12705 |  1482   (1)| 00:00:01 |
|   1 |  NESTED LOOPS          |      |    21 | 12705 |  1482   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL    | T    |     1 |   603 |  1453   (1)| 00:00:01 |
|*  3 |   JSONTABLE EVALUATION |      |       |       |            |          |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(JSON_EXISTS2("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$?(@.object_type[*].object_id==75909)' FALSE ON ERROR)=1)
   3 - filter("P"."OBJECT_ID"=75909)

demo@XEPDB1> select jt.*
  2  from (
  3  select x
  4  from t
  5  where json_exists( x, '$.object_type?(@.object_id == 75909)' )
  6       )t1, json_table( x, '$.object_type[*]'
  7       columns( object_id number path '$.object_id' ) ) jt
  8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3213954389

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |     1 |   607 |    30   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |     1 |   607 |    30   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |   605 |     1   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                       |       |     1 |   607 |            |          |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   JSONTABLE EVALUATION               |       |       |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(JSON_QUERY("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type.object_id.number()' RETURNING NUMBER ASIS  WITHOUT ARRAY WRAPPER ERROR
              ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=75909)

demo@XEPDB1> set autotrace off
demo@XEPDB1>

Questions:
1) JSON_TABLE doesnt pick this multivalue index by default, if so then json_exits plus json_table is always the way to go for index friendly cases?

and Connor said...

I will double check with the JSON team, but the docs suggests that this is specifically targetted at JSON_EXISTS

https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/indexes-for-json-data.html#GUID-CA25E863-1EA2-4E9A-A898-E7CA9CD645B1

For JSON data that is stored as JSON type you can use a multivalue function-based index for SQL/JSON condition json_exists.

You should not need the inline view however

SQL> explain plan for
  2  select *
  3  from t,
  4        json_table(x,'$.object_type[*]'
  5          columns(
  6            object_id number path '$.object_id'
  7            ) )
  8  where json_exists( x, '$.object_type?(@.object_id == 75909)' );

Explained.

SQL> select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3213954389

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |     1 |  4117 |    30   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |     1 |  4117 |    30   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |  4115 |     1   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                       |       |     1 |  4117 |            |          |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   JSONTABLE EVALUATION               |       |       |       |            |          |
----------------------------------------------------------------------------------------------


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Item method : Number and NumberOnly

Rajeshwaran, Jeyabal, November 09, 2021 - 7:46 am UTC

Team:

was playing with above test case and ended up with few more questions, please help on this.

1) what is item method number() and numberOnly() means?
2) why the optimizer is not index friendly with numberOnly() item method, what should be done over here to make it index friendly
3) when should one consider to use item method number() and numberOnly() ?

demo@XEPDB1> create multivalue index t_idx on t t1( t1.x.object_type.object_id.numberOnly() );

Index created.

demo@XEPDB1> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

demo@XEPDB1> set autotrace traceonly exp
demo@XEPDB1> select *
  2  from t
  3  where json_exists( x, '$.object_type?(@.object_id == 75909)' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   633 |  1061   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   633 |  1061   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(JSON_EXISTS2("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type?(@.object_id == 75909)' FALSE ON ERROR)=1)

demo@XEPDB1> set autotrace off
demo@XEPDB1>
demo@XEPDB1> drop index t_idx;

Index dropped.

demo@XEPDB1> create multivalue index t_idx on t t1( t1.x.object_type.object_id.number() );

Index created.

demo@XEPDB1> set autotrace traceonly exp
demo@XEPDB1> select *
  2  from t
  3  where json_exists( x, '$.object_type?(@.object_id == 75909)' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1148600783

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |   635 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |   635 |     2   (0)| 00:00:01 |
|   2 |   HASH UNIQUE                       |       |     1 |   635 |            |          |
|*  3 |    INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |   279 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(JSON_QUERY("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type.object_id.number()' RETURNING NUMBER ASIS  WITHOUT ARRAY WRAPPER
              ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=75909)

demo@XEPDB1> set autotrace off
demo@XEPDB1>

Connor McDonald
November 10, 2021 - 4:24 am UTC

It is the difference between a string that is numeric and a genuine number in the JSON, eg

SQL> create table t (id number, col json);

Table created.

SQL> insert into t values(1, '{a:[1, "7"]}');

1 row created.

SQL> insert into t values(2, '{a:[2, 8]}');

1 row created.

SQL>
SQL> select t.col.a[1].number() from t t;

T.COL.A[1].NUMBER()
-------------------
                  7
                  8

SQL> select t.col.a[1].numberOnly() from t t;

T.COL.A[1].NUMBERONLY()
-----------------------

                      8


answer to question#2 above.

Rajeshwaran, Jeyabal, November 09, 2021 - 12:36 pm UTC

Team,

got answer for the above question(2) , when added item method numberOnly() to json_exists predicate, it got index friendly.

but please help us with the other questions.

demo@XEPDB1> create multivalue index t_idx on t t1( t1.x.object_type.object_id.numberOnly() );

Index created.

demo@XEPDB1> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

demo@XEPDB1> set autotrace traceonly exp
demo@XEPDB1> select *
  2  from t
  3  where json_exists( x, '$.object_type?(@.object_id == 75909)' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   632 |  1573   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   632 |  1573   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(JSON_EXISTS2("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type?(@.object_id == 75909)' FALSE ON ERROR)=1)

demo@XEPDB1> select *
  2  from t
  3  where json_exists( x, '$.object_type?(@.object_id.numberOnly() == 75909)' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1148600783

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |   634 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |   634 |     1   (0)| 00:00:01 |
|   2 |   HASH UNIQUE                       |       |     1 |   634 |            |          |
|*  3 |    INDEX RANGE SCAN (MULTI VALUE)   | T_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(JSON_QUERY("X" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.object_type.object_id.numberOnly()' RETURNING NUMBER ASIS  WITHOUT ARRAY WRAPPER
              ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH MULTIVALUE)=75909)

demo@XEPDB1>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here