Indexing JSON Arrays in Oracle Database - Part II

In the previous blog post, we explored how JSON array elements can be indexed using materialized views and relational flattening. While that approach delivers excellent performance, it comes at a cost — additional storage, maintenance overhead, and schema complexity.
 
Starting with Oracle Database 21c and above, Oracle introduced a much more elegant and native solution for indexing JSON arrays:
👉 Multi-Value Indexes
 
Multi-value indexes allow Oracle to directly index JSON array elements — without flattening the data, without materialized views, and without rewriting your schema.
 
In this post, we will explore: 

• What multi-value indexes are
• How they improve JSON array search performance
• How they can index nested sibling arrays
• How to handle queries on specific array positions 

Traditional B-tree and function-based indexes expect one value per row. But JSON arrays naturally contain multiple values per document.
 
A multi-value index solves this mismatch by allowing multiple index entries per row, one for each array element.
 
This allows Oracle to treat JSON arrays as first-class indexable structures.
 
Let’s start by indexing the products[*].id field from our JSON document.
 
demo@ADB26ai> create multivalue index my_demo_idx on my_demo(
  2      json_table( c2 ,'$.products[*]'
  3          error on error null on empty null on mismatch
  4          columns ( object_id number path '$.id')
  5      )
  6  );
 
Index created.
 
This creates a native multi-value index — one index entry per product ID inside every JSON document. Now run the same query we used earlier:
 
demo@ADB26ai> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' );
 
122 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3662244578
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |     2 |  1456 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE                        | bhn66uu5b6guf8qzf1w80zdqqr |     2 |  1456 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MY_DEMO                    |     2 |  1456 |     4   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED     |                            |     2 |       |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | MY_DEMO_IDX                |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].id' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=55)
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO); name="select *
from my_demo
where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' )"
 
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
      92501  bytes sent via SQL*Net to client
        401  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
        122  rows processed
 
 
This multi-value index is much more efficient than the materialized view based solutions, Direct index access into JSON arrays and Only 18 consistent gets
 
One of the most powerful features of multi-value indexes is their ability to index sibling arrays inside JSON structures.
 
In our JSON document, each product has a nested dimension array:
 
{
  "id": 1,
  "products": [
    {
      "id": 55,
      "title": "Charger SXT RWD",
      "price": 32999.99,
      "quantity": 3,
      "dimension": [935, 412, 120, 88]
    },
    {
      "id": 56,
      "title": "Apple MacBook Pro 14 Inch Space Grey",
      "price": 1999.99,
      "quantity": 2,
      "dimension": [210, 330, 40, 25]
    }
  ],
  "total": 103774.85
}
 
 
We can extend the multi-value index to include both id and dimension:
 
demo@ADB26ai> drop index my_demo_idx ;
 
Index dropped.
 
demo@ADB26ai> create multivalue index my_demo_idx on my_demo(
  2      json_table( c2 ,'$.products[*]'
  3          error on error null on empty null on mismatch
  4          columns ( object_id number path '$.id' ,
  5                     nested path dimension[*]
  6                     columns( dim number path '$')
  7             )
  8      )
  9  );
 
Index created.
 
Now that the dimension array inside the products array is indexed, queries that filter on both product attributes and dimension values can efficiently leverage the multi-value index.
 
demo@ADB26ai> select * from my_demo
  2  where json_exists( c2, '$.products[*]?(@.id.number() == 55
  3     && @.dimension[*].number() == 662 )' ) ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3662244578
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |     1 |   728 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                        | 4888wgv2m25hdfrrua1uxadh6y |     1 |   728 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MY_DEMO                    |     1 |   728 |     2   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED     |                            |     1 |       |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | MY_DEMO_IDX                |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].id' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=55 AND
              JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].dimension[*]' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=662)
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO); name="select * from my_demo
where json_exists( c2, '$.products[*]?(@.id.number() == 55
        && @.dimension[*].number() == 662 )' ) "
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1239  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1    rows processed
 
This query Uses the multi-value index Avoids scanning JSON documents Executes with only 10 consistent gets Returns results in milliseconds, However the above multi value index wont be efficient for Queries targeting specific array position
 
demo@ADB26ai> select * from my_demo where json_exists( c2, '$.products[*]?(@.id.number() == 55
  2     && @.dimension[0].number() == 662 )' ) ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3662244578
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |     1 |   728 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                        | 3kjsv3q8tq4sy40thmuuv5n384 |     1 |   728 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MY_DEMO                    |     1 |   728 |     2   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED     |                            |     1 |       |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | MY_DEMO_IDX                |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*]?(@.id.number() == 55  &&
              @.dimension[0].number() == 662 )' /* json_path_str  $.products[*]?((@.id.number() == 55) &&
              (@.dimension[0].number() == 662))  */  FALSE ON ERROR TYPE(LAX) )=1)
   4 - access(JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].id' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=55 AND
              JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].dimension[*]' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=662)
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO); name="select * from my_demo where json_exists( c2, '$.products[*]?(@.id.number() == 55
        && @.dimension[0].number() == 662 )' ) "
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1239  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1    rows processed
 
 
In the above query, Oracle first probes the multi-value index using the path expression filter:
$.products[*]?(@.id.number() == 55)
 
This predicate is used to identify all rowids where at least one product has an id value of 55. For each matching rowid returned from the index, Oracle then accesses the base table and applies the additional predicate:
 
@.dimension[0].number() == 662
 
Only the rows that satisfy this second condition are finally returned to the client. Rows that fail this predicate are discarded during the table access phase. This behavior is clearly visible in the execution plan, where the predicates appear separately under the ACCESS and FILTER sections.
 
The performance impact of this execution strategy depends on selectivity:

• If many rows match the first predicate (product id = 55), a large number of rowids will be fetched from the index and used to probe the table.

• If only a few of those rows satisfy the second predicate (dimension[0] = 662), most of the table lookups will be wasted work and will be discarded during filtering. 

This results in unnecessary I/O and increased CPU usage.
 
For queries that target a specific array position, the optimal solution is to include the ordinality pseudo-column in the multi-value index definition. By indexing the array position along with the value, Oracle can fully resolve both predicates at the index level and avoid redundant table probes — delivering true index-driven execution for position-based JSON queries.
 
To make queries on specific array positions fully indexable, we can include the ordinality pseudocolumn.
 
demo@ADB26ai> drop index my_demo_idx;
 
Index dropped.
 
demo@ADB26ai>
demo@ADB26ai> create multivalue index my_demo_idx on my_demo(
  2      json_table( c2 ,'$.products[*]'
  3          error on error null on empty null on mismatch
  4          columns ( object_id number path '$.id' ,
  5                     nested path dimension[*]
  6                     columns( dim number path '$'
  7                             , idx for ordinality )
  8             )
  9      )
 10  );
 
Index created.
 
This adds the array position (idx) into the index.
 
demo@ADB26ai> select * from my_demo where json_exists( c2, '$.products[*]?(@.id.number() == 55
  2     && @.dimension[0].number() == 662 )' ) ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3662244578
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |     1 |   728 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE                        | 3kjsv3q8tq4sy40thmuuv5n384 |     1 |   728 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| MY_DEMO                    |     1 |   728 |     2   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED     |                            |     1 |       |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN (MULTI VALUE)   | MY_DEMO_IDX                |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].id' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=55 AND
              JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].dimension[*]' RETURNING NUMBER ASIS
              WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=662 AND
              JSON_QUERY("C2" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.products[*].dimension[*].position' RETURNING NUMBER
              ASIS  WITHOUT ARRAY WRAPPER ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH TYPE(LAX)  MULTIVALUE)=0)
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO); name="select * from my_demo where json_exists( c2, '$.products[*]?(@.id.number() == 55
        && @.dimension[0].number() == 662 )' ) "
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1239  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1    rows processed
 
 
Now everything is handled by the index: 

• product_id = 55
• dimension = 662
• position = 0 

There is no separate FILTER step — only indexed access. This makes even position-based JSON array queries fully scalable.
 
Multi-value indexes represent a major leap forward in Oracle’s JSON indexing architecture.

They provide:

• Native indexing of JSON arrays
• Direct index access without flattening
• Support for nested sibling arrays
• Efficient position-based filtering with ordinality
• Minimal storage and maintenance overhead

This makes multi-value indexing the best-practice approach for high-performance JSON workloads in Oracle Database 21c and above.
 
In the next blog post, we’ll explore how Oracle’s native JSON storage formats (OSON and binary JSON) further enhance query performance and indexing efficiency.
 
Stay tuned!
 

Indexing JSON Arrays in Oracle Database - Part I

JSON is a hierarchical (tree-structured) data model where entities are embedded within each other using nested objects and arrays. In contrast, relational tables are flat — each column holds a single value, and relationships are expressed using common keys across tables.

 

Traditional function-based indexes in relational databases are designed to index a single scalar value per row. This model works well for flat data, but JSON introduces a new challenge: arrays can contain multiple values for the same attribute. Because of this, conventional function-based indexes cannot directly index JSON arrays.

 

In this blog post, we’ll explore:

• Why indexing JSON arrays is challenging
• How queries behave without proper indexing
• How materialized views can help flatten JSON for indexing
• How performance improves with the right strategy

 We start with a simple table that stores one JSON document per row. Each document represents an order containing a list of products, and each product has a nested dimension array.

demo@ADB26ai> create table if not exists my_demo(c1 number,c2 json);
 
Table created.
 
demo@ADB26ai> declare
  2     l_json long;
  3  begin
  4     l_json := q'# {
  5             "id": 1,
  6             "products": [
  7               {
  8                     "id": $$b1$$,
  9                     "title": "Charger SXT RWD",
 10                     "price": 32999.99,
 11                     "quantity": 3,
 12                     "total": 98999.97,
 13                     "discountPercentage": 13.39,
 14                     "dimension" : [$$b3$$,$$b4$$,$$b5$$,$$b6$$],
 15                     "discountedTotal": 85743.87,
 16                     "thumbnail": "https://cdn.dummyjson.com/products/images/vehicle/Charger%20SXT%20RWD/thumbnail.png"
 17                     , "str" : "$$b11$$"
 18               },
 19               {
 20                     "id": $$b2$$,
 21                     "title": "Apple MacBook Pro 14 Inch Space Grey",
 22                     "price": 1999.99,
 23                     "quantity": 2,
 24                     "total": 3999.98,
 25                     "discountPercentage": 18.52,
 26                     "dimension" : [$$b7$$,$$b8$$,$$b9$$,$$b10$$],
 27                     "discountedTotal": 3259.18,
 28                     "thumbnail": "https://cdn.dummyjson.com/products/images/laptops/Apple%20MacBook%20Pro%2014%20Inch%20Space%20Grey/thumbnail.png"
 29                     , "str" : "$$b11$$"
 30               }
 31             ],
 32             "total": 103774.85,
 33             "discountedTotal": 89686.65,
 34             "userId": 33,
 35             "totalProducts": 4,
 36             "totalQuantity": 15
 37     } #';
 38
 39     insert /*+ append */ into my_demo(c1,c2)
 40     select rownum, json(
 41                             replace(replace( replace( replace( replace(
 42                                     replace( replace( replace( replace( replace(
 43                                     replace(l_json,'$$b1$$',object_id)
 44                                     ,'$$b2$$',object_id+1),
 45                                     '$$b3$$', ceil(dbms_random.value(1,1000)) ) ,
 46                                     '$$b4$$', ceil(dbms_random.value(1,1000)) ) ,
 47
 48                                     '$$b5$$', ceil(dbms_random.value(1,1000)) ) ,
 49                                     '$$b6$$', ceil(dbms_random.value(1,1000)) ) ,
 50                                     '$$b7$$', ceil(dbms_random.value(1,1000)) ) ,
 51                                     '$$b8$$', ceil(dbms_random.value(1,1000)) ) ,
 52                                     '$$b9$$', ceil(dbms_random.value(1,1000)) ) ,
 53                                     '$$b10$$', ceil(dbms_random.value(1,1000)) ) ,
 54                                     '$$b11$$', dbms_random.string('l',25) )
 55                             )
 56     from all_objects , all_users
 57     where rownum <=5000000;
 58     commit;
 59  end;
 60  /
 
PL/SQL procedure successfully completed.
 
demo@ADB26ai> select count(*) from my_demo;
 
            COUNT(*)
--------------------
           5,000,000
                
demo@ADB26ai> alter table my_demo
  2  add constraint my_demo_pk
  3  primary key(c1);
 
Table altered.

 
Each row contains an order document with a products array and a nested dimension array:
 

{
  "id": 1,
  "products": [
    {
      "id": 55,
      "title": "Charger SXT RWD",
      "price": 32999.99,
      "quantity": 3,
      "dimension": [935, 412, 120, 88]
    },
    {
      "id": 56,
      "title": "Apple MacBook Pro 14 Inch Space Grey",
      "price": 1999.99,
      "quantity": 2,
      "dimension": [210, 330, 40, 25]
    }
  ],
  "total": 103774.85
}

 

Suppose we frequently search for orders that contain a specific product ID. For example
 

select *
from my_demo
where json_exists(c2, '$.products[*]?(@.id.number() == 55)');

 
Naturally, the first thought is to create a function-based index on the product ID:
 

demo@ADB26ai> create index my_demo_idx on my_demo( json_value(c2,'$.products[*].id.number()' error on error) );
create index my_demo_idx on my_demo( json_value(c2,'$.products[*].id.number()' error on error) )
                                                   *
ERROR at line 1:
ORA-40470: JSON query '$.products[*].id.number()' evaluated to multiple values.
Help: https://docs.oracle.com/error-help/db/ora-40470/
 

However this fails, Because products[*].id is an array path. Each JSON document can contain multiple product IDs, and a function-based index expects a single scalar value per row, Without an index, Oracle has no choice but to perform a full table scan

 
demo@ADB26ai> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' );
 
122 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3804406768
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            | 50000 |    35M|   738  (40)| 00:00:01 |
|   1 |  RESULT CACHE      | bhn66uu5b6guf8qzf1w80zdqqr | 50000 |    35M|   738  (40)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MY_DEMO                    | 50000 |    35M|   738  (40)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.id.number() == 55 )' /* json_path_str  $.products[*]?(@.id.number() ==
              55)  */  FALSE ON ERROR TYPE(LAX) )=1)
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO); name="select *
from my_demo
where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' )"
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      70151  consistent gets
          0  physical reads
          0  redo size
      92501  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        122  rows processed
 
 

Even with JSON_EXISTS filtering, the database still has to inspect all 5 million rows, resulting in:

• High consistent gets
• Full scan of JSON documents
• Increased CPU and I/O

 
While this may work for small datasets, it becomes expensive at scale.
 
One effective workaround is to flatten the JSON array into relational rows using a materialized view.
 

demo@ADB26ai> create materialized view mv_my_demo
  2  build immediate
  3  refresh fast on statement
  4  enable query rewrite
  5  as
  6  select c1, jt.*
  7  from my_demo ,
  8     json_table( c2, '$.products[*]'
  9             columns(
 10                     product_id number path '$.id' error on error null on empty
 11                     , product_title varchar2(40) path '$.title' error on error null on empty) ) jt;
 
Materialized view created.

 

This converts each product inside the JSON array into a separate relational row:

 

C1	PRODUCT_ID	PRODUCT_TITLE
1	55	Charger SXT RWD
1	56	MacBook Pro

 

Now each product becomes indexable, With query rewrite enabled, Oracle can automatically redirect the original JSON query to use the materialized view.

 
demo@ADB26ai> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' );
 
122 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1777526163
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |   117 | 87282 |   229  (35)| 00:00:01 |
|   1 |  RESULT CACHE                 | gq00k9mdyn70344q7at0r2wr7h |   117 | 87282 |   229  (35)| 00:00:01 |
|   2 |   NESTED LOOPS                |                            |   117 | 87282 |   229  (35)| 00:00:01 |
|   3 |    NESTED LOOPS               |                            |   117 | 87282 |   229  (35)| 00:00:01 |
|   4 |     SORT UNIQUE               |                            |   117 |  1287 |   110  (72)| 00:00:01 |
|*  5 |      MAT_VIEW ACCESS FULL     | MV_MY_DEMO                 |   117 |  1287 |   110  (72)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | MY_DEMO_PK                 |     1 |       |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID| MY_DEMO                    |     1 |   735 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("SYS_JMV_1"."PRODUCT_ID"=55)
   6 - access("SYS_JMV_1"."C1"="MY_DEMO"."C1")
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO, DEMO.MV_MY_DEMO); parameters=(nls); name="select *
from my_demo
where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' )"
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
   - this is an adaptive plan
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2495  consistent gets
          0  physical reads
          0  redo size
      92501  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        122  rows processed

 
Instead of scanning 5 million JSON documents, Oracle scans the flattened product list — a massive improvement. We can take this one step further by indexing the flattened product ID:
 

demo@ADB26ai> create index mv_my_demo_idx on mv_my_demo( product_id );
 
Index created.
 

Now the execution plan becomes:

 
demo@ADB26ai> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' );
 
122 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3980998711
 

----------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name             | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |                  |   117 | 87282 |   123   (1)|

|   1 |  RESULT CACHE                              | gq00k9mdyn70344q7|   117 | 87282 |   123   (1)|

|   2 |   NESTED LOOPS                             |                  |   117 | 87282 |   123   (1)|

|   3 |    NESTED LOOPS                            |                  |   117 | 87282 |   123   (1)|

|   4 |     SORT UNIQUE                            |                  |   117 |  1287 |     4   (0)|

|   5 |      MAT_VIEW ACCESS BY INDEX ROWID BATCHED| MV_MY_DEMO       |   117 |  1287 |     4   (0)|

|   6 |       SORT CLUSTER BY ROWID                |                  |   117 |       |     3   (0)|

|*  7 |        INDEX RANGE SCAN                    | MV_MY_DEMO_IDX   |   117 |       |     3   (0)|

|*  8 |     INDEX UNIQUE SCAN                      | MY_DEMO_PK       |     1 |       |     1   (0)|

|   9 |    TABLE ACCESS BY INDEX ROWID             | MY_DEMO          |     1 |   735 |     2   (0)|

----------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("SYS_JMV_1"."PRODUCT_ID"=55)
   8 - access("SYS_JMV_1"."C1"="MY_DEMO"."C1")
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=2; dependencies=(DEMO.MY_DEMO, DEMO.MV_MY_DEMO); parameters=(nls); name="select *
from my_demo
where json_exists( c2, '$.products[*]?(@.id.number() == 55 )' )"
 
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
   - this is an adaptive plan
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
      92501  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        122  rows processed
 

And the statistics show:

• Only 46 consistent gets
• No full table scan
• Fast index lookup
• Highly efficient nested loop access

 
This is now a fully optimized execution path. This strategy delivers excellent performance, but it comes with a cost: 

• Additional storage for the materialized view
• Extra maintenance overhead on DML
• Flattening JSON into relational format

 
In other words, we are trading storage and maintenance for query performance.
 
JSON’s hierarchical and multi-valued structure does not fit naturally into traditional indexing models. A JSON array path can return multiple values per row, which makes it incompatible with classic function-based indexes.
 
However, by:

• Flattening JSON arrays using JSON_TABLE
• Leveraging materialized views
• Enabling query rewrite
• Adding indexes on extracted attributes

 we can achieve highly efficient and scalable search performance on JSON data.
 
In the next blog post, we’ll explore how Oracle Database has natively evolved its JSON storage and indexing architecture to support efficient JSON querying — without requiring relational flattening or materialized views. Stay tuned!