JSON Search Index - Part V (Index Path Subsetting)

Index path subsetting is one of the most practical and often underutilized features of the JSON search index. Instead of indexing every possible field inside a JSON document, path subsetting allows us to explicitly decide which paths should be indexed and which should be ignored. Any field that is excluded from indexing simply does not contribute to the index structure.
 
The immediate benefit is obvious: by filtering out irrelevant paths, we reduce the amount of data that needs to be indexed. This leads to a smaller index footprint, faster index creation time, and potentially better maintenance characteristics. In other words, we index only what truly matters to our workload.
 
Consider the following example:
 
demo@ADB26AI> create search index my_demo_idx on my_demo(c2)
  2  for json parameters(' search_on
  3     VALUE(number) include ($.products.id)
  4     TEXT include ($.products.str) ');
 
Index created.
 
This definition is very intentional. It tells the database that:

• The path $.products.id should be indexed for numeric value-based searches, including range predicates.
• The path $.products.str should be indexed for full-text search and string equality-style operations.

 
Nothing else inside the JSON document is indexed. All other paths are ignored.
Internally, if $.products.id evaluates to a value like 154980, that value is stored as a numeric token for range-based navigation. Similarly, if $.products.str evaluates to a string such as ABC, it is indexed as a text token for full-text search operations. The index stores only what we asked it to store — no more, no less.
 
The result of this focused indexing strategy is immediately visible when we check the size:
 
demo@ADB26AI> select round(sum(bytes)/1024/1024,2) size_mb
  2  from user_segments
  3  where segment_name like '%MY_DEMO_IDX%';
 
   SIZE_MB
----------
    852.06
 
In this case, the index size is around 852 MB, which is significantly smaller than the broader indexing flavors we examined earlier. This reduction comes purely from eliminating unnecessary paths.
 
With this index in place, a numeric range-based predicate on $.products.id works efficiently.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*].id?(@.number() > 15)' );
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |    25 | 18200 |   488   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |    25 | 18200 |   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*].id?(@.number() > 15)' /* json_path_str  $.products[*].id?(@.number() >
              15)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  > 15 ))')>0)
 
 
For example, an open-ended condition such as id > 15 pushes both the JSON path and the numeric boundary into the search index. The execution plan confirms that the DOMAIN INDEX is used, and only matching rowids are returned. The table is accessed only for those specific rows.
 
Similarly, full-text search on $.products.str works seamlessly. When using JSON_TEXTCONTAINS, the optimizer probes the index with both the path expression and the search term. The index performs the filtering and returns only relevant rowids, ensuring optimal performance.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2, '$.products.str ','ljfycrixvcehvmpmldubibaur');
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  2500 |  1777K|   488   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(ljfycrixvcehvmpmldubibaur) INPATH (/products/str)')>0)
 
 
However, this configuration also clearly defines its boundaries.
 
Because $.products.str is indexed using the TEXT option, it supports full-text search and text-based filtering operations, but not structured range-based string predicates. If we attempt to run a query like:
 
demo@ADB26AI> select *
  2  from my_demo nested c2.products[*]
  3      columns ( x1 varchar2(40) path '$.str')
  4  where x1 between 'A' and 'C' ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1866419551
 
------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            |   625M|   424G|    25M (19)| 00:16:31 |
|   1 |  RESULT CACHE           | 7fwrahdm15gh9fv7shatsu691k |   625M|   424G|    25M (19)| 00:16:31 |
|   2 |   NESTED LOOPS          |                            |   625M|   424G|    25M (19)| 00:16:31 |
|*  3 |    TABLE ACCESS FULL    | MY_DEMO                    | 50000 |    34M|   738  (40)| 00:00:01 |
|*  4 |    JSONTABLE EVALUATION |                            | 12500 | 25000 |   507  (18)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(JSON_EXISTS2("MY_DEMO"."C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$?(@.products[*].str<="C" && @.products[*].str>="A")' /* json_path_str
              $?((@.products[*].str.string() <= "C") && (@.products[*].str.string() >= "A"))  */  FALSE ON
              ERROR TYPE(LAX) )=1)
   4 - filter("P"."X1">='A' AND "P"."X1"<='C')
 
 
on the $.products.str path, the optimizer may skip the search index entirely. The execution plan in such cases often shows a full table scan, because range-based navigation for VARCHAR2 was not enabled under this configuration. The index was never designed to support that type of predicate.
 
This behavior is not a limitation of JSON search indexing itself, but rather a direct consequence of the deliberate path subsetting and datatype configuration we chose during index creation.
 
Another powerful aspect of path subsetting is that multiple indexed paths can be combined in a single query. For example, we can combine a full-text search on $.products.str with a numeric range predicate on $.products.id. In such cases, the optimizer can merge both conditions into a single index probe. The execution plan shows that both predicates are pushed into the search index navigation step, and only matching rowids are returned.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2, '$.products.str ','ljfycrixvcehvmpmldubibaur')
  4  and json_exists( c2, '$.products[*].id?(@.number() > 15)' );
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |    25 | 18200 |   488   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |    25 | 18200 |   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*].id?(@.number() > 15)' /* json_path_str  $.products[*].id?(@.number() >
              15)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(ljfycrixvcehvmpmldubibaur) INPATH (/products/str) and
              sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  > 15 )')>0)
 
 
This demonstrates the real strength of path subsetting: precision. We design the index to match the workload, not the other way around.
 
In summary, index path subsetting gives us fine-grained control over JSON indexing. By explicitly including only relevant paths and binding them to specific search behaviours (VALUE or TEXT), we can significantly reduce index size while maintaining excellent performance for targeted queries. It is an ideal strategy when query patterns are well understood and we want to optimize storage and efficiency without sacrificing search capability.
 

JSON Search Index - Part IV (SEARCH_ON = TEXT_VALUE_STRING)

 
In one of the previous blogs, we discussed how a JSON Search Index can be configured to support either full-text search or range-based search across supported datatypes such as NUMBER, TIMESTAMP, and VARCHAR2. We also saw that with the TEXT_VALUE option, we could combine full-text search with range-based predicates for numeric and timestamp datatypes by default, and optionally include string datatypes when explicitly specified.
 
Now let us move into a fourth flavor of JSON search index configuration — one that is slightly more aggressive than the earlier variants we explored. This option is designed to support full-text search and range-based search for all supported datatypes (numeric, string, and timestamp) by default. The trade-off, however, is increased storage consumption compared to the other three configurations.
 
This configuration is enabled using:
 
demo@ADB26AI> create search index my_demo_idx
  2  on my_demo(c2)
  3  for json parameters(' search_on TEXT_VALUE_STRING ');
 
Index created.
 
After creating the index, we can check its size:
 
demo@ADB26AI> select round(sum(bytes)/1024/1024,2) size_mb
  2  from user_segments
  3  where segment_name like '%MY_DEMO_IDX%';
 
   SIZE_MB
----------
   2485.06
 
In this case, the index size is approximately 2.4 GB. This is noticeably larger than the earlier configurations, but the benefit is clear: the index is now capable of handling a wide variety of workloads across all supported datatypes without requiring additional datatype-specific configuration.
 
Let us look at how this behaves in practice
 
When executing a numeric equality predicate using JSON_EXISTS, the execution plan clearly shows that both the JSON path and the numeric value are pushed into the search index. The index performs the navigation internally and returns only the matching rowids. These rowids are then used to access the table rows. This ensures that filtering happens inside the index rather than at the table level, improving efficiency.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2 ,'$.products[*]?(@.id.number() == 42)');
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |    25 | 18200 |   488   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |    25 | 18200 |   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.id.number() == 42)' /* json_path_str  $.products[*]?(@.id.number()
              == 42)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  = 42 ))')>0)
 
 
The same behavior applies to open-ended numeric range predicates such as >= 5. Again, both the path expression and the numeric boundary value are used during index navigation. The index performs the filtering and returns only relevant rowids.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2 ,'$.products[*]?(@.id.number() >= 5)');
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |    25 | 18200 |   488   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |    25 | 18200 |   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.id.number() >= 5)' /* json_path_str  $.products[*]?(@.id.number()
              >= 5)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  >= 5 ))')>0)
 
 
For two-sided range predicates, such as a BETWEEN 5 AND 12 condition, the optimizer still pushes both lower and upper bounds into the search index. The execution plan confirms that the index is probed with both conditions combined, resulting in efficient rowid retrieval before table access.
 
demo@ADB26AI> select *
  2  from my_demo nested c2.products[*]
  3      columns ( x1 number path '$.id')
  4  where x1 between 5 and 12;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3720428357
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   1 |  NESTED LOOPS                   |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
|*  5 |   JSONTABLE EVALUATION          |             | 12500 | 25000 |   507  (18)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'((sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  >= 5 )) and
              (sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  <= 12 )))')>0)
   5 - filter("P"."X1">=5 AND "P"."X1"<=12)
 
 
What makes TEXT_VALUE_STRING particularly powerful is that this same level of support extends to string-based predicates as well.
 
For full-text search using JSON_TEXTCONTAINS, the execution plan shows that the path and the search string are both used to probe the index. Only matching rowids are returned, ensuring efficient full-text filtering.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2, '$.products.str','ljfycrixvcehvmpmldubibaur') ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  2500 |  1777K|   488   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(ljfycrixvcehvmpmldubibaur) INPATH (/products/str)')>0)
 
More importantly, range-based string predicates are also fully supported. When performing a BETWEEN 'a' AND 'b' condition on a VARCHAR2 attribute extracted through JSON_TABLE, the execution plan confirms that both the path and the string range boundaries are pushed into the index. Unlike earlier configurations where only the path was used and value filtering happened later, here the index itself evaluates the range predicate.
 
demo@ADB26AI> select *
  2  from my_demo nested c2.products[*]
  3      columns ( x1 varchar2(40) path '$.str')
  4  where x1 between 'a' and 'b';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3720428357
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   1 |  NESTED LOOPS                   |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
|*  5 |   JSONTABLE EVALUATION          |             | 12500 | 25000 |   507  (18)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'((sdata(FVCH_073E8889C10DE4900FDC7FB51B882966_str  >= "a" )) and
              (sdata(FVCH_073E8889C10DE4900FDC7FB51B882966_str  <= "b" )))')>0)
   5 - filter("P"."X1">='a' AND "P"."X1"<='b')
 
 
This is the key differentiator. With TEXT_VALUE_STRING, both full-text search and structured range predicates — for NUMBER, TIMESTAMP, and VARCHAR2 — are fully handled at the index level. The optimizer can combine path expressions and predicate values into the index probe, returning only the necessary rowids.
 
Of course, this power comes at a cost. The index size increases significantly because it stores additional structured information for string datatypes to enable range evaluation. However, for mixed workloads that heavily rely on both textual and structured filtering across all datatypes, the storage overhead may be a worthwhile trade-off.

In summary, TEXT_VALUE_STRING represents the most comprehensive JSON search index configuration among the variants discussed so far. It provides maximum flexibility and performance coverage across all supported datatypes, making it ideal for complex JSON workloads where query patterns are diverse and unpredictable.
 

JSON Search Index - Part III (SEARCH_ON = TEXT_VALUE)

When we explored JSON search indexes earlier, we looked at how the SEARCH_ON parameter can be set to either TEXT or VALUE. Using SEARCH_ON=TEXT enables efficient full-text searches, while SEARCH_ON=VALUE helps with structured filtering such as equality and range predicates across supported datatypes. However, there has always been a practical limitation: it was not possible to efficiently support both full-text search and range-based filtering using a single search index configuration.
 
This is where the TEXT_VALUE option comes into play.
 
The TEXT_VALUE parameter brings both capabilities under a single umbrella. With this configuration, the same JSON search index can support full-text search as well as range-based filtering across supported datatypes. For workloads that combine keyword searches with structured comparisons, this becomes a powerful and practical indexing strategy.
 
To create a search index that supports both full-text and range-based search, we can define the index using the TEXT_VALUE parameter:
 
demo@ADB26AI> create search index my_demo_idx
  2  on my_demo(c2)
  3  for json parameters(' search_on TEXT_VALUE ');
 
Index created.
 
Once created, one of the first observations is the index size. In this example, the resulting index size is approximately 1.4 GB:
 
demo@ADB26AI> select round(sum(bytes)/1024/1024,2) size_mb
  2  from user_segments
  3  where segment_name like '%MY_DEMO_IDX%';
 
   SIZE_MB
----------
   1395.06
 
This size sits between the typical footprint of TEXT-only and VALUE-only indexes because the index now stores both textual and structured value information required to support multiple query patterns.
 
With this index in place, let us first look at numeric predicates. When executing equality or range-based numeric searches using JSON_EXISTS,
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2 ,'$.products[*]?(@.id.number() == 42)');
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |    25 | 18200 |   488   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |    25 | 18200 |   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.id.number() == 42)' /* json_path_str  $.products[*]?(@.id.number()
              == 42)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  = 42 ))')>0)
 
 
the execution plan clearly shows that the JSON search index is used effectively. Both the path expression and the numeric comparison value are pushed into the index lookup. The index returns only the matching rowids, which are then used to access the table rows. This ensures efficient filtering even for large datasets.
 
The same efficient behavior can be observed for range-based numeric conditions. Whether using equality comparisons, greater-than conditions, or BETWEEN predicates, the optimizer is able to use both the path expression and the numeric values to navigate the index. This confirms that filtering is performed inside the index rather than being deferred to the table access step.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2 ,'$.products[*]?(@.id.number() >= 5)');
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |    25 | 18200 |   488   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |    25 | 18200 |   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.id.number() >= 5)' /* json_path_str  $.products[*]?(@.id.number()
              >= 5)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  >= 5 ))')>0)
 
 
demo@ADB26AI> select *
  2  from my_demo nested c2.products[*]
  3      columns ( x1 number path '$.id')
  4  where x1 between 5 and 12;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3720428357
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   1 |  NESTED LOOPS                   |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
|*  5 |   JSONTABLE EVALUATION          |             | 12500 | 25000 |   507  (18)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'((sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  >= 5 )) and
              (sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  <= 12 )))')>0)
   5 - filter("P"."X1">=5 AND "P"."X1"<=12)
 
 
Even when queries are written using JSON_TABLE or nested projections, the behavior remains consistent. The index is probed using both the JSON path and the numeric range values, returning only the relevant rowids. This demonstrates that the TEXT_VALUE index effectively supports structured numeric filtering alongside full-text capabilities.
 
Next, consider full-text search scenarios. When running a JSON_TEXTCONTAINS query on a string attribute, the execution plan shows that the search index is used directly.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2, '$.products.str','ljfycrixvcehvmpmldubibaur') ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  2500 |  1777K|   488   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(ljfycrixvcehvmpmldubibaur) INPATH (/products/str)')>0)
 
In this case, both the path expression and the search string are used to probe the index, and only matching rowids are returned. This confirms that the TEXT_VALUE index fully supports full-text search requirements in addition to numeric filtering.
 
However, an interesting limitation appears when we move into string-based range predicates. Suppose we perform a query that filters string values using a BETWEEN condition on a VARCHAR2 attribute.
 
demo@ADB26AI> select *
  2  from my_demo nested c2.products[*]
  3      columns ( x1 varchar2(40) path '$.str')
  4  where x1 between 'a' and 'b';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3720428357
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   1 |  NESTED LOOPS                   |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
|*  5 |   JSONTABLE EVALUATION          |             | 12500 | 25000 |   507  (18)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'((HASPATH(/products/str)) and (HASPATH(/products/str)))')>0)
   5 - filter("P"."X1">='a' AND "P"."X1"<='b')
 
 
At first glance, the execution plan still shows index usage. But a closer look at the access predicates reveals that the index is navigated only using the path expression, not the actual string range values. As a result, the index returns all rowids that match the path expression, and the range filter on the string values is applied later during table access.

This means that while the index still contributes to the query, it is not as efficient as a scenario where both the path and range values are used directly during index navigation. The filtering on string range values is effectively pushed to a later stage, which can reduce performance benefits for large datasets.
 
This behavior highlights an important nuance of the default TEXT_VALUE configuration. By default, it enables full-text search and range-based search for NUMBER and TIMESTAMP datatypes, but not for VARCHAR2 range predicates. If the workload includes range-based filtering on string values, we must explicitly include VARCHAR2 as a supported datatype during index creation.
 
To enable this, the index can be recreated with explicit datatype inclusion:
 
demo@ADB26AI> drop index if exists my_demo_idx ;
 
Index dropped.
 
demo@ADB26AI> create search index my_demo_idx
  2  on my_demo(c2)
  3  for json parameters(' search_on TEXT_VALUE (NUMBER,VARCHAR2) ');
 
Index created.
 
demo@ADB26AI> select round(sum(bytes)/1024/1024,2) size_mb
  2  from user_segments
  3  where segment_name like '%MY_DEMO_IDX%';
 
   SIZE_MB
----------
   2477.06
 
With this configuration, the index now supports full-text search as well as range-based filtering for both numeric and string datatypes. Naturally, this broader coverage comes with an increase in index size. In this example, the index size grows to roughly 2.4 GB, reflecting the additional datatype indexing.
 
Once this enhanced index is in place, both full-text searches and string-based range predicates benefit from efficient index navigation.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2, '$.products.str','ljfycrixvcehvmpmldubibaur') ;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2005405343
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |  2500 |  1777K|   488   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   2 |   SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(ljfycrixvcehvmpmldubibaur) INPATH (/products/str)')>0)
 
 
demo@ADB26AI> select *
  2  from my_demo nested c2.products[*]
  3      columns ( x1 varchar2(40) path '$.str')
  4  where x1 between 'a' and 'b';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3720428357
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   1 |  NESTED LOOPS                   |             |    31M|    21G|  1268K (19)| 00:00:50 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | MY_DEMO     |  2500 |  1777K|   488   (0)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID BATCHED|             |       |       |     4   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX                | MY_DEMO_IDX |       |       |     4   (0)| 00:00:01 |
|*  5 |   JSONTABLE EVALUATION          |             | 12500 | 25000 |   507  (18)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'((sdata(FVCH_073E8889C10DE4900FDC7FB51B882966_str  >= "a" )) and
              (sdata(FVCH_073E8889C10DE4900FDC7FB51B882966_str  <= "b" )))')>0)
   5 - filter("P"."X1">='a' AND "P"."X1"<='b')
 
 
Execution plans confirm that the optimizer now uses both the path expression and the range predicate values to probe the index directly. Only the matching rowids are returned from the index, significantly improving filtering efficiency compared to the default configuration
 
The key takeaway is that TEXT_VALUE offers a flexible and unified indexing approach for mixed workloads that require both keyword-style searches and structured filtering. However, understanding its default datatype behavior is important. If string-based range predicates are part of the workload, explicitly including VARCHAR2 during index creation ensures that these queries benefit fully from index-level filtering.
 
Choosing the right configuration ultimately depends on query patterns and storage considerations. While broader datatype support increases index size, it can dramatically improve performance for combined search workloads. The TEXT_VALUE parameter therefore provides a powerful option for designing JSON search indexes that balance flexibility, performance, and storage in modern Oracle workloads.