Domain indexes in Oracle Database 19c

In Oracle Database 19c, "domain indexes" are specialized indexes designed for efficiently handling complex data types such as text, spatial, multimedia, JSON, XML, or custom-defined data types that traditional indexes (e.g., B-tree or Bitmap) can't effectively optimize. These indexes significantly enhance performance by enabling specialized data retrieval methods tailored specifically for these complex data types. Here's how domain indexes improve performance:

1. Specialized Data Retrieval:
   • Domain indexes provide a mechanism to implement indexing logic tailored to specific data types, which standard indexing methods cannot handle effectively.
   • They are especially useful for data stored as JSON, XML, spatial coordinates, multimedia (images, audio), or text documents, leveraging specialized query capabilities for these data formats.
   
   Example:
   • Oracle Text Indexes (for text documents)
   • Spatial Indexes for geographic/spatial data
   • JSON Search Indexes for rapid querying of JSON documents
2. Optimized Query Performance:
   • Queries that include complex predicates (like text searching or spatial queries) can significantly benefit from domain indexes.
   • They reduce table scans by directly accessing index entries optimized for the data type involved.
   Example use-case:
   • A text domain index using Oracle Text (CTXCAT or CONTEXT) for fast text searches.
   • Spatial indexes to quickly retrieve data based on geographic coordinates.
3. Extensible Indexing Framework:
   • Domain indexes are implemented using Oracle's extensible indexing framework, which allows developers to create custom indexing methods.
   • Developers can define their own index types to match custom data needs.
   Example:
   • A custom index might be built to accelerate searches in multimedia databases or GIS systems.
4. Improved Scalability:
   • By offloading complex data handling from the main query logic into specialized index implementations, overall system scalability improves.
   • Resource-intensive operations (such as text parsing or image recognition) can be executed efficiently by specialized index methods.
5. Enhanced Flexibility and Customization:
   • Domain indexes enable developers to integrate specialized indexing functionality directly into Oracle’s indexing infrastructure, using extensible indexing APIs.
   • Oracle 19c includes robust support for domain indexes through Oracle Text, Spatial and Graph, JSON, and XML features, ensuring streamlined performance tuning.

Example of Creating a Domain Index (Oracle Text):

-- Create a text domain index on a column
CREATE INDEX my_text_index ON documents(doc_content)
  INDEXTYPE IS CTXSYS.CONTEXT;

Common Built-In Domain Index Types in Oracle 19c:

• Oracle Text:
  Optimizes text search operations (e.g., full-text searches, linguistic searches, and fuzzy matching).
• Spatial Indexes:
  Allow fast processing of location-based queries and spatial analysis.
• JSON Search Indexes:
  Efficient searching within JSON documents stored in Oracle Database.

Practical Benefits:

• Significantly reduces execution times for specialized queries.
• Avoids unnecessary overhead of parsing large data types during searches.
• Optimizes I/O by reading fewer blocks from storage due to targeted index reads.

Conclusion: In Oracle 19c, domain indexes are powerful performance-enhancing tools designed explicitly for non-standard, complex data types. They enable more efficient querying, lower resource consumption, and enhance the capability and responsiveness of modern, data-intensive applications.

The Oracle database provides a great deal of capability and functionality, but there may be times when users want to supplement that functionality to address some of their own specific needs. A domain index is an index that applies to a specific application domain. The domain index is external to the standard Oracle database. The domain index feature allows a developer to create extensions to the standard functionality of Oracle through the use of domain indexing.

• Why use Domain Indexing?
  There are times when a particular application may require special processing. Frequently, these are times when an application is using a special type of data, such as spatial data or video clips, that does not fit comfortably into an existing Oracle data type. These special types of data also may require special types of operators, such as determining if a particular color is in a particular image in a greater concentration than in another image. A domain index could define a comparison operator to encapsulate this comparison.
  To implement a domain index, you must first define an index type. The index type is a schema object that contains all the routines needed to use an index. These routines include:
  1. Index definition routines
  2. Index maintenance routines
  3. Index scan routines
  Whenever a SQL statement requires the use of a domain index, the Oracle database makes the appropriate calls to the index type to access and manipulate the domain index. In addition, an index type can contain specific operators that can be used on the domain index, or these operators can be defined elsewhere in the schema and simply access the domain index. A domain index can be stored in an index-organized table or outside of the Oracle database.

A domain index is an index designed for a specialized domain, such as spatial or image processing. Users can build a domain index of a given type after the designer creates the indextype. The behavior of domain indexes is specific to an industry, a business function, or some other special purpose; you must specify it during cartridge development. The system-managed approach to domain indexes in the Oracle Database 11g Release 1, requires less programmatic overhead and delivers better performance than the earlier user-managed domain indexes. It addresses the limitations of the user-managed approach, and has the following benefits:

1. Because the kernel performs many more maintenance tasks on behalf of the user, there is no need for programmatic support for table and partition maintenance operations. These operations are implemented by taking actions in the server, thus requiring a very minimal set of user-defined interface routines to be coded by the user. The cartridge code can then be relatively unaware of partition issues.
2. The number of objects that must be managed to support local partitioned domain indexes is identical to identical to those for non-partitioned domain indexes. For local partitioned indexes, the domain index storage tables are equipartitioned with respect to the base tables (using system-partitioned tables); therefore, the number of domain index storage tables does not increase with an increase in the number of partitions.
3. A single set of query and DML statements can now access and manipulate the system-partitioned storage tables, facilitating cursor sharing and enhancing performance.

Oracle recommends that you develop new applications with system-managed domain indexes instead of user-managed domain indexes. Indextypes encapsulate search and retrieval methods for complex domains such as text, spatial, and image processing. An index type is similar to the indexes that are supplied with the Oracle Database. The difference is that you provide the application software that implements the indextype.
In the next lesson, you will learn how Oracle allows you to perform some index maintenance operations online.