A customer, an order, and a shipment can each live in a different table. How do you let someone search across them without putting together separate searches or writing custom indexing code? That’s the problem Ubiquitous Database Search addresses in Oracle AI database 26ai.
With DBMS_SEARCH, you can bring multiple tables and views into one index and search across them.
Only one command is needed to create a self managed index and another one to add different sources. Without writing additional PL/SQL code you can include different table data sources in your search. 

DBMS_SEARCH also provides autocomplete, which returns suggestions as someone types. It shares the package and Oracle Text foundation with Ubiquitous Database Search, but uses a separate index and workflow. This post focuses on searching across multiple data sources; for an autocomplete example, see Search as You Type with DBMS_SEARCH Autocomplete.

In this post, I’ll focus on Ubiquitous Database Search. I’ll create an index, add data sources, inspect the virtual document, query across sources, add another source, and review index monitoring.

The post covers:

From Oracle Text to Ubiquitous Search

Oracle Text provides full-text search in Oracle Database. A basic use case is to create an index on a single text column. For more complex content, MULTI_COLUMN_DATASTORE can combine several columns from the same row into one document. A USER_DATASTORE procedure can assemble document content during indexing, using a defined interface.

An Oracle Text index can support different query operators, such as FUZZY, SOUNDEX, and NEAR, depending on the index configuration.

Ubiquitous Search makes it easier to search across multiple tables and views. With the DBMS_SEARCH PL/SQL package, you can create one index, add or remove data sources, and search across them. This can simplify workflows that previously involved multiple indexes, custom datastore procedures, and materialized views. Index changes are synchronized automatically in the background, so you don’t need to run synchronization manually.

Let’s look at a simple example to see how it works.

Step 1: Index Creation for Ubiquitous Database Search

The new interface DBMS_SEARCH allows the indexing of multiple schema objects in a single index. Detailed information can be found in the documentation in Oracle Text Reference: DBMS_SEARCH

Before you start you may install the Oracle Database sample schema from github. In my example I will use the tables from the schema “customer orders” (user CO) to illustrate the functionality.
In addition add the CTXAPP role and CREATE JOB privilege to the user.
After gathering schema statistics it should look like this: 

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
CUSTOMERS                             392
STORES                                 23
PRODUCTS                               46
ORDERS                               1950
SHIPMENTS                            1892
ORDER_ITEMS                          3914
INVENTORY                             566

Let’s create an ubiquitous index with the name SEARCH_PRODUCTS. Use the CREATE_INDEX procedure to create an ubiquitous search index. It uses a JSON search index technology with some default indexing preferences.

execute DBMS_SEARCH.CREATE_INDEX(index_name=>'SEARCH_PRODUCTS');

It creates the infrastructure for Oracle Text including a table SEARCH_PRODUCTS with two JSON columns DATA and METADATA. The METADATA column uniquely identifies each row of a particular data source, using the OWNER, SOURCE, and KEY (or ROWID) JSON values.

SQL> desc SEARCH_PRODUCTS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 METADATA                                  NOT NULL JSON
 DATA                                               JSON
 OWNER                                              VARCHAR2(128)
 SOURCE                                             VARCHAR2(128)
 KEY                                                VARCHAR2(1024)

If you need to drop the index first, use this.

execute DBMS_SEARCH.DROP_INDEX('SEARCH_PRODUCTS');

Step 2: Add Data Sources

You can add a set of tables, external tables, or views as data sources to this index. All the columns in the specified sources are indexed and available then for a full-text search. In our case let’s add the table SHIPMENTS and STORES. The ADD_SOURCE procedure adds the tables to the DBMS_SEARCH index as a data source.

Note: You can use the DBMS_SEARCH PL/SQL package to create a ubiquitous search index on multiple tables and views within a schema. In general, you can create this index only on the views that have a primary key and a foreign key constraint relationship with the component table (see also 3.1.8 Indexing and Views) .


In our case I made sure that there are primary keys and foreign Keys defined for the two tables. 

execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'SHIPMENTS');
execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'STORES');

Step 3: Review the virtual document

To get an idea what you can search for, execute the GET_DOCUMENT procedure. The GET_DOCUMENT procedure returns a virtual indexed document that is created after populating it with multiple columns, tables, or views. You can review this document before running a query against the DBMS_SEARCH index as follows. 
Note: It uses a user data store to assemble a JSON document on the fly. 

SQL> set long 1000 longc 500

SQL> select DBMS_SEARCH.GET_DOCUMENT (INDEX_NAME=>'SEARCH_PRODUCTS', DOCUMENT_METADATA=>METADATA) output 
     from SEARCH_PRODUCTS;
OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"CO":{"STORES":{"LOGO_CHARSET":null,"LOGO_FILENAME":null,"LONGITUDE":34.862138,"STORE_ID":23,"LOGO":null,"LOGO_MIME_TYPE":null,"LATITUDE":32.100664,"STORE_NAME":"Tel Aviv","LOGO_LAST_UPDATED":null,"WEB_ADDRESS":null,"PHYSICAL_ADDRESS":"B, Aharon Bart St 18, Petah Tikva, 4951400, Israel"}}}
...
{"CO":{"SHIPMENTS":{"SHIPMENT_ID":1956,"STORE_ID":1,"CUSTOMER_ID":376,"DELIVERY_ADDRESS":"Pecos, NM 87552 USA","SHIPMENT_STATUS":"IN-TRANSIT"}}}

1915 rows selected.

Step 4: Run queries against your index

You can run queries using the JSON_TEXTCONTAINS and CONTAINS operators. 

First let’s use CONTAINS with the operator FUZZY. Remember the FUZZY operator expands queries to include words that are spelled similarly to the specified term. The result from the tables SHIPMENTS and STORES looks like this. It contains results from both tables. 

SQL> select metadata output from SEARCH_PRODUCTS 
     where contains(data,'fuzzy(Los)') > 0;

OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"STORES","KEY":{"STORE_ID":10}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":976}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":469}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":470}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1484}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":545}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":546}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1806}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1807}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":854}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":855}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1833}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1834}}

-- let's check
SQL> select physical_address from stores where store_id = 10;

PHYSICAL_ADDRESS
--------------------------------------------------------------------------------
C/ Jos?? Echegaray 6B
    Las Rozas          -- here fuzzy(Los) finds the term 'Las' 
    28230 Madrid

SQL> select delivery_address from shipments where shipment_id=469;

DELIVERY_ADDRESS
--------------------------------------------------------------------------------
Los Angeles, CA 90026 USA

Let’s try a query with JSON_TEXTCONTAINS. JSON_TEXTCONTAINS checks if a specified string exists in JSON property values or not.  

SQL> select metadata from SEARCH_PRODUCTS
     where JSON_TEXTCONTAINS(data,'$.CO.STORES.PHYSICAL_ADDRESS','fuzzy(LOS)');  

METADATA
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"STORES","KEY":{"STORE_ID":10}}

Adding more data sources

Now let’s add another source and see if you can include it in a search. Let’s add the table CUSTOMERS.

execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'CUSTOMERS');

Now let’s query. We use the term  ‘jon’ – more precisely fuzzy(jon) – to find results in the table CUSTOMERS. 

SQL> select metadata output from SEARCH_PRODUCTS where contains(data,'fuzzy(jon)')>0;

OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":66}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":368}}

2 rows selected.

-- Let's check
SQL> select full_name from customers where customer_id=66;

FULL_NAME
--------------------------------------------------------------------------------
John Venzl

Let’s add a row to the table CUSTOMERS und search for it again.

SQL> insert into customers values (1000,'john.johnson@oracle.com','John Johnson');

1 row created.


SQL> commit;

Commit complete.

SQL> select metadata output from SEARCH_PRODUCTS where contains(data,'fuzzy(jon)')>0;

OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":66}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":368}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":1000}}

3 rows selected.

Summary

In this example, we created one DBMS_SEARCH index and added SHIPMENTS, STORES, and CUSTOMERS as data sources. We inspected the virtual documents with GET_DOCUMENT, then searched across the sources using CONTAINS and JSON_TEXTCONTAINS, including fuzzy matching. The index metadata identifies which source matched and provides the key needed to retrieve the full row from its table. We also added a customer record and checked if it became searchable. Together, these steps show how DBMS_SEARCH can simplify full-text search across multiple objects with one index and less custom indexing code.





Further reading





Application Developer’s Guide: Using Ubiquitous Database Search
Oracle Text Reference: DBMS_SEARCH
Search as You Type with DBMS_SEARCH Autocomplete (Posting)