Ubiquitous Database Search in Oracle 23c

Introduction

The Oracle Database 23c comes with a host of new features and enhancements aimed at making database management more efficient and user-friendly. One standout feature is the introduction of DBMS_SEARCH, a powerful PL/SQL package designed to simplify and optimize indexing for multiple schema objects. This revolutionary tool allows for full-text and range-based searches across multiple tables, columns, and views within a schema. In this blog post, we’ll delve into the key aspects of DBMS_SEARCH and explore how it enhances the search capabilities in Oracle Database 23c.

Understanding the Ubiquitous Search Index

At the heart of DBMS_SEARCH lies the concept of a “ubiquitous search index.” This index is essentially a JSON-based index that incorporates predefined preferences and settings, enabling seamless full-text searches on various schema objects. With the DBMS_SEARCH PL/SQL package, users can efficiently create, manage, and query these indexes.

Key Capabilities of DBMS_SEARCH

DBMS_SEARCH offers a set of APIs that empower users to perform a range of tasks:

1. Create Index:
   • The DBMS_SEARCH.CREATE_INDEX API allows users to create a DBMS_SEARCH index. By default, this index is created with key indexing preferences that enable wildcard search and support both full-text and range-search queries. These indexes are automatically maintained in the background, eliminating the need for manual synchronization and optimization operations.
2. Manage Data Sources:
   • The DBMS_SEARCH.ADD_SOURCE API allows users to add data sources, such as specific tables or views, from different schemas to the index.
   • The DBMS_SEARCH.REMOVE_SOURCE API enables the removal of a table or view and all its associated data from the index.
3. View Virtual Indexed Documents:
   • The DBMS_SEARCH.GET_DOCUMENT API allows users to view virtual indexed JSON documents, displaying metadata values indexed in the index for each row of the data sources.
4. Query Multiple Objects:
   • The DBMS_SEARCH.FIND API facilitates the retrieval of a hitlist of all documents based on specified filter conditions. Users can perform full-text or range-based queries within a single data source or across multiple sources using the same index.

Index Tables: DATA and METADATA Columns

A DBMS_SEARCH index stores data in JSON objects, supporting various SQL data types. However, it excludes XMLTYPE and LONG data types. The DBMS_SEARCH.CREATE_INDEX procedure creates an index table named INDEX_NAME with two essential columns:

1. DATA column:
   • The index is created on the DATA column. This empty column serves as a placeholder for querying the DBMS_SEARCH index, allowing the addition of data sources. Users can run PL/SQL queries on the DATA column using various operators.
2. METADATA column:
   • The METADATA column helps uniquely identify each row indexed in the table or view. It contains separate virtual columns for OWNER, SOURCE, and KEY. The table itself is partitioned by OWNER and SOURCE, enabling efficient data source identification and querying using partition pruning.

Benefits of DBMS_SEARCH

The DBMS_SEARCH PL/SQL package provides a comprehensive solution for creating, managing, and querying search indexes for textual and range-based ubiquitous database search. With its set of well-defined APIs and built-in capabilities, users can streamline the indexing process, facilitating efficient and accurate search operations.

Conclusion

Oracle Database 23c’s introduction of DBMS_SEARCH marks a significant advancement in the world of database management. With the ability to create, manage, and query ubiquitous search indexes, users can enjoy more efficient and powerful search capabilities across multiple schema objects. As organizations continue to generate vast amounts of data, tools like DBMS_SEARCH are critical in enabling quick and accurate data retrieval, contributing to improved decision-making and enhanced database management in today’s data-driven world.

Read more about DBMS_SEARCH:

• DBMS_SEARCH Package
• Performing Ubiquitous Database Search with the DBMS_SEARCH APIs