JSON Search Index - Part VII (SEARCH_ON = ADD , REMOVE , REPLACE)

One of the notable enhancements introduced in Oracle Database 26ai (23.26.1) is the ability to rebuild an existing JSON Search Index with path subsetting. This new capability gives database administrators much finer control over what gets indexed and, more importantly, what does not. As JSON workloads continue to grow in size and complexity, controlling index footprint has become increasingly important. Oracle's latest enhancement addresses this challenge by allowing index definitions to evolve without requiring the index to be dropped and recreated.

To understand the value of this feature, consider a JSON Search Index created using the traditional SEARCH_ON TEXT_VALUE_STRING option. Such an index provides excellent query performance because it indexes text values, numeric values, and timestamp values across all supported JSON paths. As a result, both full-text searches and value-based predicates can be efficiently answered through the same index. Queries that search for a product identifier, a quantity value, or a text attribute such as a product title can all take advantage of index access paths.
 
demo@ADB26AI> create search index my_demo_idx
  2  on my_demo(c2)
  3  for json parameters(' search_on TEXT_VALUE_STRING ');
 
Index created.
demo@ADB26AI> set autotrace traceonly exp
demo@ADB26AI>
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 72)' );
 
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() == 72)' /* json_path_str  $.products[*]?(@.id.number()
              == 72)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  = 72 ))')>0)
 
 
demo@ADB26AI>
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.quantity.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[*]?(@.quantity.number() == 42)' /* json_path_str
              $.products[*]?(@.quantity.number() == 42)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_409E93841EA61DD3F60D03041A92157A_quantity  = 42 ))')>0)
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2,'$.products.title','Charger SXT RWD');
 
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 */ ,'(Charger SXT
              RWD) INPATH (/products/title)')>0)
 
 
The downside of this approach is storage consumption. Since every supported datatype across every JSON path is indexed, the resulting index can become significantly larger than the underlying data itself. In the demonstration environment, the JSON Search Index occupied nearly 2.5 GB of storage. While this provides maximum query flexibility, many applications do not actually require every path to be indexed.
 
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
----------
   2483.63
 
This is where path subsetting becomes valuable. Oracle Database 26ai now allows an existing JSON Search Index to be rebuilt with additional path restrictions. For example, suppose an application frequently searches for product identifiers but rarely performs numeric searches on other attributes. Instead of indexing every numeric value in every document, the index can be rebuilt to index numeric values only for the path $.products.id.
 
demo@ADB26AI> alter index my_demo_idx rebuild
  2  parameters( ' ADD search_on value(number) include ($.products.id) ' );
 
Index altered.
 
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
----------
   2556.69
 
After rebuilding the index with this configuration, queries searching for product identifiers continue to use the JSON Search Index and retain their performance characteristics. However, queries that search for numeric values in other paths, such as $.products.quantity, can no longer be satisfied through the index and therefore fall back to a full table scan. Text searches remain fully supported because text indexing is still enabled for all paths.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 72)' );
 
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() == 72)' /* json_path_str  $.products[*]?(@.id.number()
              == 72)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  = 72 ))')>0)
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.quantity.number() == 42)' );
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3804406768
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            | 50000 |    34M|   738  (40)| 00:00:01 |
|   1 |  RESULT CACHE      | g9xkr5g0q79r37y3xw9y29gvmm | 50000 |    34M|   738  (40)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MY_DEMO                    | 50000 |    34M|   738  (40)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.quantity.number() == 42)' /* json_path_str
              $.products[*]?(@.quantity.number() == 42)  */  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[*]?(@.quantity.number() == 42)' )"
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2,'$.products.title','Charger SXT RWD');
 
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 */ ,'(Charger SXT
              RWD) INPATH (/products/title)')>0)
 
An interesting observation from the demonstration is that restricting numeric indexing alone does not significantly reduce the overall index size. The reason is that full-text indexing remains enabled and continues to consume the majority of the storage. The index size actually remained around 2.5 GB even after restricting numeric indexing to a single path. This highlights an important point: path subsetting provides query optimization flexibility, but meaningful storage savings require careful consideration of which datatypes are being indexed.
 
The next step in the demonstration was to remove text indexing entirely. By rebuilding the index and removing the TEXT_VALUE component, Oracle eliminated both full-text search support and value-based indexing for string datatypes. At that point, the index retained only the numeric indexing required for the $.products.id path. The impact on storage was dramatic. The index footprint dropped from approximately 2.5 GB to less than 400 MB, representing a reduction of more than eighty percent.
 
demo@ADB26AI> alter index my_demo_idx rebuild
  2  parameters( ' REMOVE search_on text_value(varchar2) ' );
 
Index altered.
 
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
----------
    388.81
 
Naturally, this change affects query capabilities. Numeric searches on product identifiers continue to use the index and remain highly efficient. Numeric searches on paths that are not part of the defined subset revert to full table scans. Full-text searches are no longer possible because the required text indexing structures have been removed. Attempts to use JSON_TEXTCONTAINS result in the expected ORA-40467 error indicating that a JSON-enabled context index is no longer available.
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 72)' );
 
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() == 72)' /* json_path_str  $.products[*]?(@.id.number()
              == 72)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  = 72 ))')>0)
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.quantity.number() == 42)' );
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3804406768
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            | 50000 |    34M|   738  (40)| 00:00:01 |
|   1 |  RESULT CACHE      | g9xkr5g0q79r37y3xw9y29gvmm | 50000 |    34M|   738  (40)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MY_DEMO                    | 50000 |    34M|   738  (40)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.quantity.number() == 42)' /* json_path_str
              $.products[*]?(@.quantity.number() == 42)  */  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[*]?(@.quantity.number() == 42)' )"
 
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_textcontains( c2,'$.products.title','Charger SXT RWD');
from my_demo
     *
ERROR at line 2:
ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without a JSON-enabled context index
Help: https://docs.oracle.com/error-help/db/ora-40467/
 
Oracle has also introduced the ability to replace existing path subsets during a rebuild operation. Instead of incrementally adding or removing definitions, administrators can completely replace the current path subset with a new definition. This makes it easier to adapt indexing strategies as application requirements evolve over time. As business needs change, the index can be reshaped without the operational overhead of dropping and recreating it from scratch.
 
demo@ADB26AI> alter index my_demo_idx rebuild
  2  parameters( ' REPLACE search_on value(number) include ($.products.id) ' );
 
Index altered.
 
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
----------
    383.44
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.id.number() == 72)' );
 
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() == 72)' /* json_path_str  $.products[*]?(@.id.number()
              == 72)  */  FALSE ON ERROR TYPE(LAX) )=1)
   3 - access("CTXSYS"."CONTAINS"("MY_DEMO"."C2" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_6AA7829B12A1ECA7231B4272D111B8B4_id  = 72 ))')>0)
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2, '$.products[*]?(@.quantity.number() == 42)' );
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3804406768
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            | 50000 |    34M|   738  (40)| 00:00:01 |
|   1 |  RESULT CACHE      | g9xkr5g0q79r37y3xw9y29gvmm | 50000 |    34M|   738  (40)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MY_DEMO                    | 50000 |    34M|   738  (40)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.quantity.number() == 42)' /* json_path_str
              $.products[*]?(@.quantity.number() == 42)  */  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[*]?(@.quantity.number() == 42)' )"
 
 
 
demo@ADB26AI> select *
  2  from my_demo
  3  where json_exists( c2,'$.products[*]?(@.title == "Charger SXT RWD")');
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3804406768
 
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            | 50000 |    34M|   738  (40)| 00:00:01 |
|   1 |  RESULT CACHE      | bdzdkjsz7xptdbx5kjyaykf3yf | 50000 |    34M|   738  (40)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| MY_DEMO                    | 50000 |    34M|   738  (40)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(JSON_EXISTS2("C2" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.products[*]?(@.title == "Charger SXT RWD")' /* json_path_str
              $.products[*]?(@.title.string() == "Charger SXT RWD")  */  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[*]?(@.title == "Charger SXT RWD")')"
 
The real significance of this enhancement is the flexibility it introduces into JSON indexing. Historically, administrators had to choose between indexing everything or manually creating highly specialized indexing strategies. With Oracle Database 26ai, JSON Search Indexes become much more adaptable. Organizations can now index only the paths that are critical to application performance while avoiding the storage costs associated with indexing data that is rarely queried.
 
The demonstration clearly illustrates the benefits. Starting with a fully indexed JSON Search Index occupying nearly 2.5 GB, it was possible to reduce the footprint to roughly 383 MB while still maintaining indexed access for the application's most important predicate. For large JSON repositories and document-centric applications, this capability provides a practical way to balance performance, storage consumption, and operational simplicity.
 
The ability to add, remove, and replace indexed path subsets through a simple rebuild operation makes JSON Search Indexes in Oracle Database 26ai significantly more flexible than previous releases. For customers managing large-scale JSON workloads, this enhancement represents an important step forward in making JSON indexing both efficient and cost-effective.

JSON Search Index - Part VI (Handling ORA-29855 / DRG-51401 (Duplicate SEARCH_ON))

 

In the earlier posts of this series, we explored how the SEARCH_ON clause controls the behavior of a JSON search index, whether it is optimized for full-text queries using TEXT, value-based predicates using VALUE, or a combination of both using TEXT_VALUE. In this post, let us look at a slightly tricky error that can arise when these options are combined incorrectly.

 

Consider the following index definition:

 

create search index my_demo_idx on my_demo(c2)
for json parameters(' search_on
VALUE(number) include ($.products.id)
TEXT include ($.products.str)
TEXT_VALUE(number) include ($.products.quantity) ');

 

At first glance, this looks perfectly valid. The intention is quite clear: index products.id for numeric comparisons, index products.str for text search, and allow products.quantity to support both text and numeric queries. However, this statement fails with the error:

 

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)
  5     TEXT_VALUE(number) include ($.products.quantity) ');
create search index my_demo_idx on my_demo(c2)
*
ERROR at line 1:
ORA-29855: Error while processing the ODCIINDEXCREATE routine for index "DEMO"."MY_DEMO_IDX".
ORA-20000: Oracle Text error:
DRG-51401: duplicate SEARCH_ON specification for TEXT component
ORA-06512: at "CTXSYS.DRUE", line 192
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 359
Help: https://docs.oracle.com/error-help/db/ora-29855/

 

To understand why this happens, we need to look a little deeper into how Oracle interprets the SEARCH_ON clause. The important detail here is that TEXT_VALUE is not an entirely separate mode. Internally, it already includes both text and value indexing capabilities. This means that whenever you use TEXT_VALUE, you are implicitly asking Oracle to create a TEXT component as well.

 

Now, if you also specify a standalone TEXT clause in the same definition, Oracle sees this as defining the same component twice. In other words, the index definition is attempting to assign two different rules to the same TEXT behavior. Rather than making assumptions about which one should take precedence, Oracle raises the DRG-51401 error to prevent ambiguity.

 

This restriction is intentional. Allowing overlapping definitions like this would make it unclear how different JSON paths should be indexed and queried. It could lead to inconsistencies in how text search is executed, how tokens are generated, or how scoring behaves. By enforcing that each SEARCH_ON component is defined only once, Oracle ensures that the index definition remains unambiguous and predictable.

 

The solution, therefore, is to make the indexing intent explicit and avoid overlapping specifications. Instead of mixing TEXT and TEXT_VALUE, we can define the required behavior using TEXT and VALUE directly, grouping the relevant paths under each.

 

A corrected version of the index definition would look like this:

 

demo@ADB26AI> create search index my_demo_idx on my_demo(c2)
  2  for json parameters(' search_on
  3     VALUE(number) include ($.products.id, $.products.quantity)
  4     TEXT include ($.products.str, $.products.quantity)');
 
Index created.

 

In this version, the intent is much clearer. The paths that need numeric or range-based evaluation are grouped under VALUE(number), while the paths that should participate in full-text search are grouped under TEXT. If a path such as products.quantity needs to support both types of queries, it is explicitly included in both sections. This avoids any hidden overlap and keeps the definition straightforward.

 

The key takeaway from this is that TEXT_VALUE should not be used alongside TEXT in the same index definition, since it already includes text indexing internally. When both text and value semantics are required, it is better to express them explicitly using separate TEXT and VALUE clauses. This not only resolves the error but also results in a cleaner and more maintainable index design.

 

As with the earlier topics in this series, the broader lesson here is to think in terms of query patterns rather than just syntax. A well-defined JSON search index should clearly reflect how the data will be queried. When that intent is expressed without overlap or ambiguity, both errors and performance surprises can be avoided.