Oracle: Operators, Index Types, and Domain Indexes

Atlas now supports Oracle user-defined operators, index types, and domain indexes as first-class resources, enabling declarative management of full-text search indexes (CTXSYS.CONTEXT) and custom ODCI-based indexing infrastructure.

These three resources are closely related: a domain index uses an index_type, an index_type binds operators to an ODCI implementation object, and each operator dispatches to a function or object-type method. Together they make Oracle Text, spatial, and other ODCI-based indexing extensions manageable end-to-end from HCL.

Operators

An operator declares a named operator with one or more bindings. Each binding is backed by a standalone function:

function "COMPARE_NUM" {
  schema = schema.app
  arg "A" { type = NUMBER }
  arg "B" { type = NUMBER }
  return = NUMBER
  as     = <<-SQL
    BEGIN
      RETURN 0;
    END COMPARE_NUM;
  SQL
}

operator "CMP_OP" {
  schema = schema.app
  binding {
    function = function.COMPARE_NUM
    args     = [NUMBER, NUMBER]
    return   = NUMBER
  }
}

Bindings can also reference static methods on a user-defined object_type:

object_type "CMP_TYPE" {
  schema = schema.app
  attribute "VAL" { type = NUMBER }
  function "COMPARE" {
    static = true
    arg "A" { type = CLOB }
    arg "B" { type = CLOB }
    return = NUMBER
    as     = <<-SQL
      BEGIN
        IF DBMS_LOB.COMPARE(a, b) = 0 THEN RETURN 1; ELSE RETURN 0; END IF;
      END;
    SQL
  }
}

operator "CMP_OP" {
  schema = schema.app
  binding {
    function = object_type.CMP_TYPE.function.COMPARE
    args     = [CLOB, CLOB]
    return   = NUMBER
  }
}

Atlas diffs bindings individually, so you can add, drop, or swap bindings on an existing operator without recreating it.

Domain Indexes with Built-In Index Types

Domain indexes use an existing indextype, such as the Oracle Text CTXSYS.CONTEXT. Set the domain attribute on an index block, and optionally provide parameters that are passed to the underlying indextype:

table "DOCUMENTS" {
  schema = schema.app
  column "ID" {
    null = false
    type = NUMBER(10)
  }
  column "CONTENT" {
    null = false
    type = VARCHAR2(4000)
  }
  column "TITLE" {
    null = false
    type = VARCHAR2(255)
  }
  primary_key { columns = [column.ID] }
  index "CTX_CONTENT_IDX" {
    columns = [column.CONTENT]
    domain  = "\"CTXSYS\".\"CONTEXT\""
  }
  index "CTX_TITLE_IDX" {
    columns    = [column.TITLE]
    domain     = "\"CTXSYS\".\"CONTEXT\""
    parameters = "SYNC (ON COMMIT)"
  }
}

Custom Index Types

The index_type block defines a custom indextype backed by an ODCI implementation object and a list of supported operators. Once declared, it can be referenced from any index via the domain attribute:

index_type "TEXT_IDXTYPE" {
  schema    = schema.app
  impl_type = object_type.TEXT_IDX_IMPL
  operator {
    operator    = operator.TEXT_CONTAINS_OP
    param_types = [VARCHAR2, VARCHAR2]
  }
}

table "DOCUMENTS" {
  schema = schema.app
  column "ID"       { null = false, type = NUMBER(10) }
  column "KEYWORDS" { null = false, type = VARCHAR2(4000) }
  primary_key { columns = [column.ID] }
  index "DOC_KEYWORDS_IDX" {
    columns = [column.KEYWORDS]
    domain  = index_type.TEXT_IDXTYPE
  }
}

Generated SQL

Atlas resolves the full dependency chain across functions, object types, operators, index types, and domain indexes, and emits the statements in the correct order on create and drop:

-- Create "TEXT_CONTAINS_OP" operator
CREATE OPERATOR "TEXT_CONTAINS_OP"
  BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING "TEXT_CONTAINS";

-- Create "TEXT_IDXTYPE" index-type
CREATE INDEXTYPE "TEXT_IDXTYPE"
  FOR "TEXT_CONTAINS_OP" (VARCHAR2, VARCHAR2)
  USING "TEXT_IDX_IMPL";

-- Create domain indexes
CREATE INDEX "CTX_CONTENT_IDX" ON "DOCUMENTS" ("CONTENT")
  INDEXTYPE IS "CTXSYS"."CONTEXT";
CREATE INDEX "CTX_TITLE_IDX" ON "DOCUMENTS" ("TITLE")
  INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('SYNC (ON COMMIT)');
CREATE INDEX "DOC_KEYWORDS_IDX" ON "DOCUMENTS" ("KEYWORDS")
  INDEXTYPE IS "TEXT_IDXTYPE";

On drop, Atlas reverses the order and uses DROP INDEXTYPE ... FORCE and DROP OPERATOR ... FORCE where required, so that removing an entire stack of custom indexing resources works in a single migration.