DBMS_SEARCH Package

The DBMS_SEARCH PL/SQL package provides procedures and functions to create, manage, and query a ubiquitous search index.

Instead of creating various individual indexes or manually defining the USER_DATASTORE or MULTI_COLUMN_DATASTORE procedures along with materialized views, you can create a ubiquitous search index to automatically synthesize multiple tables or views into a single index. This index lets you perform full-text, vector-based semantic search, hybrid text-vector search and range-based searches across multiple objects within an entire schema.

The DBMS_SEARCH index is a local index, meaning it’s partitions are aligned with the partitions of the underlying table which is partitioned by schema and source. Partition names for the index are assigned internally by the database and are not user-defined. Query the CTX_USER_INDEX_PARTITIONS view, to retrieve the internally assigned partition names for the DBMS_SEARCH index. This view provides the partition names for index operations, such as CTX_DDL.OPTIMIZE_INDEX, which requires specifying the partition names.

Starting Oracle Database 26ai ( 23.26.0) release, DBMS_SEARCH package users require the following privileges to be granted to ensure proper functionality:

• CREATE SEQUENCE

• CREATE TRIGGER

• CREATE JOB Failure to do so may result in errors or incomplete functionality when creating, managing, or querying the search index. It is advised to review the existing user roles before granting these privileges, as those roles may already have these privileges. For example, the CTXAPP role automatically has the CREATE SEQUENCE privilege, and the RESOURCE role has the CREATE TRIGGER privilege.

Note: In addition to the examples provided for each of these APIs, you can run various end-to-end example scenarios, as demonstrated in Oracle Text Application Developer’s Guide.

Name	Description
CREATE_INDEX	Creates a ubiquitous search index.
ADD_SOURCE	Adds a table, view, or Duality view to the index as data source.
REMOVE_SOURCE	Removes a data source and all its associated data from the index.
DROP_INDEX	Removes the index and all its associated data from the database.
GET_DOCUMENT	Returns a virtual indexed JSON document for the specified source metadata.
FIND	Retrieves a hitlist, and facets an aggregations of JSON documents based on the specified filter conditions.

Maintain data consistency and prevent stale entries in the DBMS_SEARCH index by ensuring proper management of index sources following schema changes, such as dropping or renaming a table or view.

When you drop a table or view using the DROP TABLE or DROP VIEW commands, it does not remove that object from the index. All previously indexed data for that source persists in the index tables. You need to remove the source from the index using DBMS_SEARCH.REMOVE_SOURCE procedure.

When you rename a table or view using the RENAME TABLE or RENAME VIEW commands, the newly named object is not automatically included as a source in the index. To include the renamed object in the index, you need to manually add it using the DBMS_SEARCH.ADD_SOURCE procedure. All the data associated with the original (before the rename) table or view remains in the index. You need to manually remove the data using the DBMS_SEARCH.REMOVE_SOURCE procedure with the old source name.