About Ubiquitous Search and Ubiquitous Search Indexes

Ubiquitous search enables you to perform full-text and range-based queries across multiple objects within an entire schema. You can use a ubiquitous search index (or simply a DBMS_SEARCH index) to perform ubiquitous searches.

A ubiquitous search index is a JSON SEARCH INDEX type with predefined set of preferences and settings that are enabled for performing full-text search on tables, views, or JSON Duality views. You use the DBMS_SEARCH PL/SQL package to create, manage, and query these indexes.

You can create a DBMS_SEARCH index on tables or views over schemas that you have SELECT privileges on. You can add data sources, that is tables and views, into this index (without the need to materialize the views). All the columns in the specified sources are indexed and available for full-text or range-based search.

Why Choose a Ubiquitous Search Index?

This indexing technique lets you create indexes across multiple objects, add or remove data sources, and perform full-text or range-based searches within a single data source or across multiple sources using the same index. This simplifies the indexing tasks that previously (prior to Oracle AI Database 26ai) required you to create multiple individual indexes and manually combine various data sources using the MULTI_COLUMN_DATASTORE or USER_DATASTORE procedures along with materialized views. Previously, this also required additional methods, such as triggers, to ensure that the index remained synchronized with DML operations.

With a simplified set of DBMS_SEARCH APIs, you can perform ubiquitous searches across the database as follows:

This index creates background jobs at predefined intervals to synchronize the DML changes and optimize the index using the AUTO_DAILY mode on all data sources. You do not need to explicitly run the SYNC_INDEX and OPTIMIZE_INDEX operations on this index.

Ubiquitous Search Index Creation Overview

You create a DBMS_SEARCH index by simply specifying an index name and then adding various data sources to it. This is illustrated in the following diagram:

Description of the illustration ubiquitous_search_index.png

  1. The first command (DBMS_SEARCH.CREATE_INDEX procedure) creates an index table as [schema].index_name. A ubiquitous search index, also named [schema].index_name, is created on the DATA column of the index table. Note that the index table name matches your index name.

    Here, the schema owner name (SCOTT) is specified along with the index name (MYINDEX) as SCOTT.MYINDEX.

  2. The second command (DBMS_SEARCH.ADD_SOURCE procedure) adds one or more data sources such as tables, views, or duality views from different schemas.

    Here, this procedure combines contents from all the columns of the PRODUCTS and CUSTOMERS tables in Scott’s schema into the MYINDEX table.

The MYINDEX table contains the following columns:

Query Indexed Data

As discussed earlier, you can use the DBMS_SEARCH.GET_DOCUMENT procedure to view all the contents extracted from the original base tables by querying a virtual document. This document contains a JSON representation for each indexed row of a table or view that is added as data source to your index.

The syntax for DBMS_SEARCH.GET_DOCUMENT is:

SELECT DBMS_SEARCH.GET_DOCUMENT('[schema].index_name', METADATA)
 from [schema].index_name;

For example, using our earlier PRODUCTS and CUSTOMERS source tables scenario, the following statement returns a virtual document with combined metadata values as indexed in the MYINDEX index:

SELECT DBMS_SEARCH.GET_DOCUMENT('SCOTT.MYINDEX',METADATA)
  from SCOTT.MYINDEX;

DBMS_SEARCH.GET_DOCUMENT('SCOTT.MYINDEX', METADATA)
-----------------------------------------------------------------
{
  "SCOTT" :
  {
    "PRODUCTS" :
    {
      "ID"          : 1,
      "PRICE"       : 10,
      "DESCRIPTION" : "simple widget"
    }
  }
}
{
  "SCOTT" :
  {
    "PRODUCTS" :
    {
      "ID"          : 2,
      "PRICE"       : 2000,
      "DESCRIPTION" : "shiny thing"
    }
  }
}
{
  "SCOTT" :
  {
    "CUSTOMERS" :
    {
      "ID"         : 5,
      "FIRSTNAME"  : "Robert",
      "LASTNAME"   : "Smith"
    }
  }
}
{
  "SCOTT" :
  {
    "CUSTOMERS" :
    {
      "ID"         : 9,
      "FIRSTNAME"  : "John",
      "LASTNAME"   : "Doe"
    }
  }
}

You can now run queries against your index using the CONTAINS, JSON_TEXTCONTAINS, and JSON_EXISTS operators.

DBMS_SEARCH Dictionary Views

You can use the following dictionary views to examine your ubiquitous search indexes:

Related Topics