CREATE_AUTOCOMPLETE_INDEX

The DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX procedure creates an autocomplete index on the specified table column or JSON column.

Syntax

DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX(
   index_name   VARCHAR2,
   source_name  VARCHAR2,
   column_name  VARCHAR2,
   field_name   VARCHAR2 DEFAULT NULL,
   lexer        VARCHAR2 DEFAULT 'CTXSYS.AUTOCOMPLETE_LEXER',
   wordlist     VARCHAR2 DEFAULT 'CTXSYS.AUTOCOMPLETE_WORDLIST',
   domain       VARCHAR2 DEFAULT 'GENERIC',
   stoplist     VARCHAR2 DEFAULT 'CTXSYS.EMPTY_STOPLIST'
);

index_name

Specify name of the DBMS_SEARCH autocomplete index (must be a valid, qualified SQL name).

source_name

Specify name of the table (or JSON collection table) containing the data to index.

column_name

Specify the column to use as the autocomplete text source, or the JSON column when field_name is specified.

field_name

Optionally, specify JSON path to extract a field from column_name. The default value is set to NULL. If NULL, the entire column is indexed.

lexer

Specify Oracle Text lexer for tokenization. The default value is set to CTXSYS.AUTOCOMPLETE_LEXER. See Lexer Types

wordlist

Specify Oracle Text wordlist to use for prefix and completion behavior. The default value is set to CTXSYS.AUTOCOMPLETE_WORDLIST. See Wordlist Type

domain

Specify any one of the following suggestion styles to control autocomplete suggestions in search. Currently, GENERIC and NAME domains are supported.

GENERIC: General text completion. It is recommended for all other domains, such as address search.
NAME: Name-style completion (optimized for names). It usesOracle Text NDATA search, which applies specialized algorithms optimized for querying on names.

stoplist

Specify stoplist to use for generic completion. Only empty stoplist is currently supported. The default value is set to CTXSYS.EMPTY_STOPLIST. See Stoplists

Example

This example creates an autocomplete index named MOVIES_AC_IDX on the MOVIES_JSON table. It specifically targets the JSON field $.title inside the DOC column. By setting the domain to GENERIC, it configures the index for general text completions (not name-specific). After running this, your application can provide fast, ranked suggestions for movie titles as users begin typing.

BEGIN
  DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX(
    index_name  => 'MOVIES_AC_IDX',
    source_name => 'MOVIES_JSON',
    column_name => 'DOC',
    field_name  => '$.title',  -- Extracts the 'title' field from the JSON doc
    domain      => 'GENERIC'
  );
END;
/