Domain/Spatial/Text/Vector Index Concepts and Choosing Specialized Access Paths

Learning outcomes

ServiceHub now needs four kinds of search: exact/range relational lookup, “documents containing these concepts,” “assets within this geometry,” and “documents semantically similar to this embedding.” A team proposes one conventional B-tree strategy for all of them. That fails because the predicates themselves are different mathematical operations. Oracle’s extensible/domain and specialized indexes attach access methods to those operators, with maintenance and feature gates that must be understood separately.

1. Domain indexes extend Oracle’s access-method contract

A domain index is application-specific. An indextype and implementation code tell Oracle how to build, maintain and query the index. This lets SQL expose specialized operators while the optimizer sees a domain-index row source. Creating your own domain index requires privileges such as EXECUTE on the indextype and implementation types.

This mechanism is why a spatial relation or full-text search does not have to be flattened into awkward scalar columns merely to fit a conventional B-tree.

SELECT    index_name,    index_type,    ityp_owner,    ityp_name,    domidx_status,    domidx_opstatus,    parametersFROM user_indexesWHERE table_name IN (    'SERVICEHUB_SEARCH_DOC',    'SERVICEHUB_ASSET_GEO',    'SERVICEHUB_VECTOR_DOC')ORDER BY table_name, index_name;

2. Oracle Text indexes documents and uses text operators

Oracle Text CONTEXT, CTXCAT, and CTXRULE indexes are domain/composite-domain index types with their own tokenization, filtering and synchronization behavior. A CONTEXT index is queried with CONTAINS, not with a B-tree equality predicate. Index freshness/maintenance policy is therefore part of correctness for a search application.

-- Requires Oracle Text objects/privileges available in the environment.CREATE INDEX sh08_doc_text_ixON servicehub_search_doc(document_text)INDEXTYPE IS CTXSYS.CONTEXT;SELECT document_id, SCORE(1) AS relevanceFROM servicehub_search_docWHERE CONTAINS(document_text, 'compressor AND vibration', 1) > 0ORDER BY SCORE(1) DESC;

The mandatory lab later does not require Oracle Text index creation; it records the operator/index contract and checks feature availability. Dedicated text/search chapters should cover lexer preferences, synchronization, document filtering and operational maintenance in depth.

3. Spatial indexes answer geometric predicates

Oracle Spatial can index SDO_GEOMETRY using spatial indextypes such as MDSYS.SPATIAL_INDEX_V2. Spatial queries use operators such as SDO_FILTER and relationship predicates rather than ordinary scalar comparison. Current licensing information states that Oracle Spatial and Graph no longer requires an extra-cost license, but specific capabilities such as partitioned spatial indexes can still carry offering/Partitioning-option conditions.

CREATE INDEX sh08_asset_spatial_ixON servicehub_asset_geo(geometry)INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;SELECT asset_idFROM servicehub_asset_geoWHERE SDO_FILTER(          geometry,          :window_geometry      ) = 'TRUE';

Geometry dimensionality, SRID consistency, partitioning and index maintenance are part of the design. “Spatial is included” does not mean every surrounding topology or option is automatically licensed.

4. Vector indexes trade exactness for speed

Oracle AI Vector Search supports specialized vector indexes including HNSW (Hierarchical Navigable Small World, graph-based) and IVF (Inverted File Flat, partition/centroid-based). Approximate search intentionally trades some recall/accuracy for speed. The distance metric used by the query must be compatible with the index design.

CREATE VECTOR INDEX sh08_doc_hnsw_ixON servicehub_vector_doc(embedding)ORGANIZATION INMEMORY NEIGHBOR GRAPHDISTANCE COSINEWITH TARGET ACCURACY 95;CREATE VECTOR INDEX sh08_doc_ivf_ixON servicehub_vector_doc(embedding)ORGANIZATION NEIGHBOR PARTITIONSDISTANCE COSINEWITH TARGET ACCURACY 95;

HNSW uses the Vector Pool and can have memory implications; current release notes document VECTOR_MEMORY_SIZE behavior. Newer vector capabilities are also gated by exact RU and COMPATIBLE. Oracle 26ai documentation notes that features introduced at RU 23.6 require COMPATIBLE=23.6.0 or later. This lesson therefore records the gate instead of changing COMPATIBLE in a lab.

5. Deliberately wrong approach: choose an index by data type name alone

A team sees VARCHAR2 and always chooses a B-tree, even though the requirement is linguistic relevance across long documents. Another team sees a VECTOR column and always creates HNSW even though the table is tiny and exact distance search is fast enough. Both designs skip the workload question.

Specialized indexes can add background maintenance, memory, refresh/synchronization semantics, approximate-result behavior, option/edition constraints, and new failure modes. The right question is: which operator must be accelerated, what correctness does that operator promise, and what operational cost does its index impose?

6. A decision matrix for ServiceHub

7. Mandatory free/local lab: inventory capability before adoption

This lab deliberately avoids assuming optional components are configured in the learner’s Free installation. It records database version, COMPATIBLE, index types already present, relevant object types, and vector-memory configuration. The output becomes a capability sheet for later dedicated chapters.

SELECT banner_fullFROM v$versionWHERE banner_full LIKE 'Oracle%';SELECT name, valueFROM v$parameterWHERE name IN ('compatible', 'vector_memory_size')ORDER BY name;SELECT    index_type,    COUNT(*) AS index_countFROM user_indexesGROUP BY index_typeORDER BY index_type;SELECT owner, object_name, object_type, statusFROM all_objectsWHERE (owner = 'MDSYS'  AND object_name LIKE 'SPATIAL_INDEX%')   OR (owner = 'CTXSYS' AND object_name IN ('CONTEXT','CTXCAT','CTXRULE'))ORDER BY owner, object_name;SELECT comp_id, comp_name, version, statusFROM dba_registryWHERE comp_id IN ('CONTEXT','SDO')ORDER BY comp_id;

DBA_REGISTRY requires catalog visibility; if the course owner cannot query it, run only the user/all-object checks or ask a DBA to capture the component evidence. Absence of an object from your privileges is not proof that the database binary lacks the feature.

SELECT COUNT(*) AS lesson_owned_specialized_indexesFROM user_indexesWHERE index_name LIKE 'SH08_%'  AND index_type LIKE 'DOMAIN%';

8. Production judgment and chapter bridge

Specialized indexes should be selected from operator semantics, dataset scale, latency/accuracy requirements, DML/refresh behavior, recovery/backup implications, memory and platform limits, and current licensing. Spatial, Text and vector search each have dedicated maintenance and troubleshooting models; this chapter intentionally stops at access-path architecture so later feature chapters can teach those systems without smuggling in assumptions.

The current 26ai licensing manual lists Spatial and Graph as available without an extra-cost license, while option-dependent surrounding features still need checking. Vector feature availability must be checked against RU, COMPATIBLE, platform and memory requirements. No specialized index is mandatory in this lab, so it remains reproducible on a basic Free installation. Chapter 09 now turns from index structures to the cost-based optimizer, statistics, runtime plan evidence and cardinality estimation.

Authoritative references

• CREATE INDEX — domain-index privilege and indextype framework
• Oracle Text CREATE INDEX — Text domain/composite-domain index types
• Creating a Spatial Index — Spatial index indextypes and requirements
• CREATE VECTOR INDEX — HNSW/IVF organizations, distance and target accuracy
• Restrictions for Oracle AI Vector Search — platform and Vector Pool considerations
• Licensing Information — Spatial/Graph and offering-specific feature licensing