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;
/

Materialized View Created During Autocomplete Index Creation

Creating an autocomplete index also creates a materialized view with the name specified by index_name. The materialized view is populated from source_name, and a CONTEXT index is created on the generated materialized view’s searchable column.

The following example creates USER_INFO_FIRST_NAME_AC materialized view, populates it from USER_INFO, and creates a CONTEXT index on the materialized view’s DATA column:

BEGIN
  DBMS_SEARCH.CREATE_AUTOCOMPLETE_INDEX(
    'USER_INFO_FIRST_NAME_AC',
    'USER_INFO',
    'DATA',
    '$.FIRST_NAME'
  );
END;
/

Materialized View Usage Notes

Cross-schema users require an explicit SELECT grant on the generated materialized view. A user invoking DBMS_SEARCH.AUTOCOMPLETE against an autocomplete index in another schema must have direct SELECT privilege on the generated materialized view. SELECT privilege on the source table does not automatically grant access to the generated materialized view. Without this grant, DBMS_SEARCH.AUTOCOMPLETE can fail with the DRG-10502 error, even when the autocomplete/CONTEXT index is valid.

The following example grants SELECT privilege to MSAWRITE to query autocomplete index owned by ORGANIZATION.
```
GRANT SELECT
ON ORGANIZATION.USER_INFO_FIRST_NAME_AC
TO MSAWRITE;
```
The generated materialized view and its CONTEXT index have a lifecycle independent of the source table. Before dropping the source table, explicitly remove the autocomplete index and its generated objects by using DBMS_SEARCH.DROP_AUTOCOMPLETE_INDEX procedure. This procedure drops the generated materialized view and, when applicable, the materialized view log created for the source table.

The following example drops USER_INFO_FIRST_NAME_AC autocomplete index:
```
BEGIN
DBMS_SEARCH.DROP_AUTOCOMPLETE_INDEX(
index_name  => 'USER_INFO_FIRST_NAME_AC',
source_name => 'USER_INFO'
);
END;
/
```