Building a Vector Search Pipeline in Oracle Database - Part II

In Part I, we loaded text from Oracle Cloud Object Storage, generated embeddings with OCI Generative AI, stored those vectors alongside their source text, and queried the NEWS_DATA table with Oracle Database native vector search. That proved the semantic-search workflow inside the database. This article takes the next practical step: expose the same search as a REST API that an application can call over HTTP.
 
The implementation uses a custom Oracle REST Data Services (ORDS) module. A caller passes a search phrase in the URL; ORDS binds that value into the SQL statement, the database generates its embedding, and VECTOR_DISTANCE returns the nearest matching rows.
 
The following PL/SQL block enables the schema for ORDS, creates the vector_search module, defines a ccnews/:input_text route, and attaches a GET handler that returns a JSON collection. The handler reuses the NEWS_DATA table and the same OCI Generative AI embedding configuration used in Part I.
 
rajesh@ADBS26ai> BEGIN
  2    ORDS.ENABLE_SCHEMA(
  3        p_enabled             => TRUE,
  4        p_schema              => 'RAJESH',
  5        p_url_mapping_type    => 'BASE_PATH',
  6        p_url_mapping_pattern => 'rajesh',
  7        p_auto_rest_auth      => FALSE);
  8
  9    ORDS.DEFINE_MODULE(
 10        p_module_name    => 'vector_search',
 11        p_base_path      => '/vector_search/',
 12        p_items_per_page =>  25,
 13        p_status         => 'PUBLISHED',
 14        p_comments       => NULL);
 15    ORDS.DEFINE_TEMPLATE(
 16        p_module_name    => 'vector_search',
 17        p_pattern        => 'ccnews/:input_text',
 18        p_priority       => 0,
 19        p_etag_type      => 'HASH',
 20        p_etag_query     => NULL,
 21        p_comments       => NULL);
 22    ORDS.DEFINE_HANDLER(
 23        p_module_name    => 'vector_search',
 24        p_pattern        => 'ccnews/:input_text',
 25        p_method         => 'GET',
 26        p_source_type    => 'json/collection',
 27        p_items_per_page =>  25,
 28        p_mimes_allowed  => '',
 29        p_comments       => NULL,
 30        p_source         =>
 31  'select id,info
 32  from news_data
 33  order by vector_distance( vec, dbms_vector_chain.utl_to_embedding( :input_text , json(''{
 34      "provider": "ocigenai",
 35      "credential_name": "OCI_GENAI_CRED",
 36      "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
 37      "model": "cohere.embed-english-v3.0",
 38      "batch_size": 100
 39     }'') ) )
 40  fetch first 5 rows only'
 41        );
 42
 43
 44    COMMIT;
 45  END;
 46  /
 
PL/SQL procedure successfully completed.
 
 
ORDS binds the path parameter to :input_text; it is not concatenated into the SQL statement. DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDING converts that text into a query vector using the configured OCI Generative AI model. VECTOR_DISTANCE compares that query vector with the VEC column for every NEWS_DATA row, then the query returns the five nearest matches.
 
After the module is published, send a URL-encoded search phrase to the route. The example below uses the phrase from Part I, little red corvette.
 
$ curl --location 'https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search/ccnews/little%20red%20corvette'
{"items":[{"id":39,"info":"The Toyota Camry, the nation's most popular car has now been rated as its best new model."},{"id":45,"info":"The Carolina Panthers entered the season thrilled about their depth at running back."},{"id":50,"info":"The US and Venezuela say they have made a positive start to improving relations, after talks in Caracas."},{"id":65,"info":"Nella notte 73 interventi dei vigili nel Napoletano"},{"id":89,"info":"Mibtel +0,75%, SP/Mib +0,76%, All Stars +0,65%"}],"hasMore":false,"limit":25,"offset":0,"count":5,"links":[{"rel":"self","href":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search/ccnews/little%20red%20corvette"},{"rel":"describedby","href":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/metadata-catalog/vector_search/ccnews/item"},{"rel":"first","href":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search/ccnews/little%20red%20corvette"}]}
 

 
 
The response is an ORDS JSON collection. Each item contains the row ID and source text selected by the vector search, while the collection metadata reports the result count and pagination state.
 
ORDS can generate an OpenAPI catalog for the custom module. This gives API consumers a machine-readable description of the route, including the required input_text path parameter and JSON response schema
 
$ curl --location 'https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/open-api-catalog/vector_search/'
{"openapi":"3.0.0","info":{"title":"ORDS generated API for vector_search","version":"1.0.0"},"servers":[{"url":"https://uqbefsy0.adb.us-ashburn-1.oraclevcn.com/ords/rajesh/vector_search"}],"paths":{"/ccnews/{input_text}":{"get":{"description":"Retrieve records from vector_search","responses":{"200":{"description":"The queried record.","content":{"application/json":{"schema":{"type":"object","properties":{"items":{"type":"array","items":{"type":"object","properties":{"id":{"$ref":"#/components/schemas/NUMBER"},"info":{"$ref":"#/components/schemas/VARCHAR2"}}}}}}}}}},"parameters":[{"name":"input_text","in":"path","required":true,"schema":{"type":"string","pattern":"^[^/]+$"},"description":"implicit"}]}}},"components":{"schemas":{"NUMBER":{"type":"number"},"VARCHAR2":{"type":"string"}}}}
 
 
The first screenshot shows the OpenAPI document returned by the catalog endpoint. Notice that it describes the GET /ccnews/{input_text} route and identifies the API server URL.

 
Import that JSON into Swagger Editor to turn the generated contract into an interactive API reference. The overview confirms that the module exposes a single GET endpoint and documents the response types returned by the handler.
 
 

Swagger exposes input_text as a required path parameter. Enter little red corvette, choose Try it out, and Swagger constructs the encoded request URL automatically. This makes it easy to validate the route without manually composing a cURL command.
 

The server response returns five matching NEWS_DATA records. The results do not need to contain the exact phrase; vector search ranks content by semantic similarity, which is the behavior established in Part I.
 
 

 
What this gives an application
 

• A simple HTTP interface: clients send natural-language input instead of database SQL or vector values.
• A reusable semantic-search service: embedding generation and similarity ranking remain inside Oracle Database. 
• A documented contract: the generated OpenAPI definition can be imported into Swagger, API clients, and testing tools. 
• A clear extension point: add authentication, filters, pagination, response fields, or a POST request body as your application requirements grow.

  
The vector pipeline from Part I is now available as a REST service. ORDS receives a search phrase, binds it safely into the handler, Oracle Database generates an embedding through OCI Generative AI, and the database returns the nearest NEWS_DATA records as JSON. From there, the endpoint can be consumed by a web application, assistant, or any HTTP-capable client.

Building a Vector Search Pipeline in Oracle Database - Part I

 
One of the most exciting enhancements introduced in Oracle REST Data Services (ORDS) 26.1 is the ability to perform native vector search directly through REST API endpoints. This significantly simplifies the process of exposing semantic search capabilities over HTTP, allowing developers to build AI-powered applications without having to implement custom API layers.
 
In this article, we'll first build the complete vector search pipeline inside Oracle Database. We'll start by loading textual data from Oracle Cloud Object Storage, generate vector embeddings using OCI Generative AI, store those embeddings in a vector-enabled table, and finally perform semantic search using native SQL. In the next article, we'll extend this implementation by exposing the same functionality through the new ORDS REST endpoints introduced in version 26.1.
 
Loading the Sample Dataset
 
For this demonstration, I have a sample text dataset stored in Oracle Cloud Object Storage. Instead of importing the file into the database, Oracle Database allows us to query it directly using the EXTERNAL table feature. This makes it very convenient to work with large datasets stored in Object Storage.
 
rajesh@ADBS26ai> col sentence for a45 trunc
rajesh@ADBS26ai> select *
  2  from external(
  3      ( sentence varchar2(4000))
  4      type oracle_bigdata
  5      access parameters(
  6          com.oracle.bigdata.fileformat = textfile
  7          com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL
  8          com.oracle.bigdata.csv.rowformat.fields.terminator='\n'
  9          )
 10  location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/ax3h6mpnbnhx/b/tinno-etl-data-backup-dev/o/TEST/dataset_200K.txt')
 11  )
 12  where sentence is not null
 13  and rownum <= 10 ;
 
SENTENCE
---------------------------------------------
BOGOTA, Colombia  - A U.S.-made helicopter on
UNIONTOWN, Pa. - A police officer used a Tase
French soccer star Zidane apologized for head
Iraqi Prime Minister Iyad Allawi said on Sund
Relatives of loved ones whose bodies were fou
Hank Kuehne knows he is something of a curios
TOKYO -- The release of Sony's PlayStation 3
The American singer Frankie Laine, who sang t
President Bush will tell the nation's largest
Tapas &#151; the Spanish finger-food that is
 
10 rows selected.
 
Now that we have access to our textual data, the next step is to generate vector embeddings that can later be used for semantic search.
 
Configuring OCI Generative AI Access
 
Oracle Database communicates with OCI Generative AI over HTTPS, so the database user must first be granted permission to connect to the OCI inference endpoint. This is accomplished by configuring the appropriate Network ACL entries.
 
admin@ADBS26ai> BEGIN
  2    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  3      host => 'inference.generativeai.us-chicago-1.oci.oraclecloud.com',
  4      ace  => xs$ace_type(
  5        privilege_list => xs$name_list('resolve'),
  6        principal_name => 'RAJESH',
  7        principal_type => xs_acl.ptype_db
  8      )
  9    );
 10
 11    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
 12      host       => 'inference.generativeai.us-chicago-1.oci.oraclecloud.com',
 13      lower_port => 443,
 14      upper_port => 443,
 15      ace        => xs$ace_type(
 16        privilege_list => xs$name_list('connect'),
 17        principal_name => 'RAJESH',
 18        principal_type => xs_acl.ptype_db
 19      )
 20    );
 21  END;
 22  /
 
PL/SQL procedure successfully completed.
 
Once these privileges are in place, the database is able to establish secure connections to the OCI Generative AI service.
 
The next requirement is to create a credential (very similar to the way how we did in the earlier post) containing the OCI API authentication details. Oracle Database uses this credential whenever it invokes the embedding service.
 
rajesh@ADBS26ai> declare
  2     l_str json_object_t := json_object_t();
  3  begin
  4     l_str.put('user_ocid' , 'ocid1.user.oc1..aaaaaaaaizox65xk2rml7r7sui7cm45sbl63l7mcp3n47pajoio6oxdq2kcq' );
  5       l_str.put('tenancy_ocid' , 'ocid1.tenancy.oc1..aaaaaaaajsbvps46z5s52liti5uk3keanjc7drotset4gnoepohyfg7opnjq');
  6       l_str.put('compartment_ocid' , 'ocid1.compartment.oc1..aaaaaaaaazb4nuukyvbwtiur63tkoncdjrzvnsqlefvwasntf2ubbxfa2wqq');
  7       l_str.put('private_key' , 'MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQC0py5Ql/gRG315
  8  yqFJj/SEuhhi+L5s+tiOCWezfRG/l2kmcHcIVaHMzRA6OwdClkFNiLEYQYQjdVHf
  9  T+CKgGlKXIP/9zTqEA1deNySm1g+JstbG7u9wWBjvD2I9wKGXMaBP71gC4blqCir
       .......
       .......
       .......
 30  4PGw04XANOJgavvoL+5y+Q0vj0S8ujpku2lNcGcqacr8b38r8KkTy1SZR/mpoYS2
 31  kersujzZKtmhnxHlqH4RxvWvYXLY6UYKS9wcLVOIuymQ0UCEsfvPKzA2YuubCFSH
 32  m3nohNxcmelNF4UwxIhK1ilI');
 33      l_str.put('fingerprint' , '92:a7:ef:ba:50:d4:a1:7a:f1:e1:30:fc:f0:8e:b8:73' );
 34     dbms_vector.create_credential( credential_name => 'OCI_GENAI_CRED', params  => json(l_str.to_String) );
 35  end;
 36  /
 
PL/SQL procedure successfully completed.
 
Configuring the Embedding Model
 
With authentication configured, we can now define the embedding provider and specify the model that will generate our vector embeddings. In this example, we're using the Cohere English Embed v3 model hosted on OCI Generative AI.
 
rajesh@ADBS26ai> var params clob;
rajesh@ADBS26ai> begin
  2   :params := '
  3  {
  4    "provider": "ocigenai",
  5    "credential_name": "OCI_GENAI_CRED",
  6    "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
  7    "model": "cohere.embed-english-v3.0",
  8    "batch_size": 10
  9  }';
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
Before processing the dataset, it's always a good idea to verify that the configuration is working correctly by generating an embedding for a simple piece of text
 
rajesh@ADBS26ai> col c1 for a40 trunc
rajesh@ADBS26ai> select dbms_vector.utl_to_embedding('hello', json(:params)) c1 ;
 
C1
----------------------------------------
[-1.44386292E-003,1.67694092E-002,-3.720
 
Receiving a valid vector confirms that the database is successfully communicating with OCI Generative AI and that the credentials have been configured correctly.
 
Creating and Populating the Vector Table
 
Now that embedding generation has been verified, we can create a table that stores both the original text and its corresponding vector representation.
 
rajesh@ADBS26ai> create table if not exists news_data( id number, info varchar2(4000), vec vector );

 
Table NEWS_DATA created.
 
Instead of loading the data first and generating embeddings later, Oracle Database allows us to generate embeddings during the insert operation itself. The following statement reads the data directly from Object Storage while simultaneously invoking the OCI embedding model for every row.
 
rajesh@ADBS26ai> insert into news_data( id,info,vec )
  2  with rws as (
  3  select *
  4  from external(
  5      ( sentence varchar2(4000))
  6      type oracle_bigdata
  7      access parameters(
  8          com.oracle.bigdata.fileformat = textfile
  9          com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL
 10          com.oracle.bigdata.csv.rowformat.fields.terminator='\n'
 11          )
 12  location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/ax3h6mpnbnhx/b/tinno-etl-data-backup-dev/o/TEST/dataset_200K.txt')
 13  ) )
 14  select rownum, sentence
 15     , dbms_vector_chain.utl_to_embedding( rws.sentence , json(:params) )
 16  from rws
 17  where rws.sentence is not null
 18* and rownum <=100;
 
100 rows inserted.
 
For this demonstration, I'm only processing the first 100 rows, but the same approach scales to much larger datasets.
 
Performing Semantic Vector Search
 
With the vectors stored in the database, semantic search becomes remarkably straightforward. Unlike traditional keyword searches, vector search compares the semantic meaning of the query vector against the stored embeddings, making it possible to retrieve relevant results even when the exact words don't appear in the data.
 
Let's use the phrase "little red corvette" as our search query.
 
rajesh@ADBS26ai> variable query_vec varchar2(80)
rajesh@ADBS26ai> exec :query_vec := 'little red corvette';
 
PL/SQL procedure successfully completed.
 
rajesh@ADBS26ai> select id,info
  2  from news_data
  3  order by vector_distance( vec, dbms_vector_chain.utl_to_embedding( :query_vec , json(:params) ) )
  4* fetch first 5 rows only;
 
   ID INFO
_____ ___________________________________________________________________________________________________________
   39 The Toyota Camry, the nation's most popular car has now been rated as its best new model.
   45 The Carolina Panthers entered the season thrilled about their depth at running back.
   50 The US and Venezuela say they have made a positive start to improving relations, after talks in Caracas.
   65 Nella notte 73 interventi dei vigili nel Napoletano
   89 Mibtel +0,75%, SP/Mib +0,76%, All Stars +0,65%
 
 
The query returns the five closest matches based on semantic similarity.
 
Although the results don't contain the exact search phrase, they demonstrate how vector search identifies content based on meaning rather than simple lexical matching. This is the foundation of semantic search and one of the reasons vector databases have become so important in AI-powered applications.
 
At this point, we've successfully built an end-to-end semantic search pipeline inside Oracle Database. We loaded textual data directly from Oracle Cloud Object Storage, generated vector embeddings using OCI Generative AI, stored those embeddings alongside the source text, and queried them using Oracle Database's native vector search capabilities.
 
In the next article, we'll build on this foundation and explore the new capability introduced in ORDS 26.1, where the same vector search functionality can be exposed directly through REST endpoints, enabling AI-powered semantic search over standard HTTP APIs with minimal additional development.

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.