CONTEXT INDEX IN TRANSACTIONAL MODE WITH SYNC (EVERY) RESULTS IN ORA-29855 - DRG-12253 (Doc ID 2927586.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

In an Oracle Text application, having:

1) a CONTEXT index in non TRANSACTIONAL mode and with SYNC(ON COMMIT) for a near-online indexing requirement;

2) CONTAINS function;

3) some SDATA sections optimized for search for filtering results.

Switching to TRANSACTIONAL MODE and SYNC (EVERY), when creating the index we get the following error:

DRG-12253: optimized for SEARCH or SORT_AND_SEARCH SDATA sections do not support CREATE TRANSACTIONAL INDEX operation

Cause: An attempt was made to use an operation that is not supported for these SDATA sections.

Action: Use another subsitute operation if possible.

The full error body is:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-12253: optimized for SEARCH or SORT_AND_SEARCH SDATA sections do not support CREATE TRANSACTIONAL INDEX operation

ORA-06512: at "CTXSYS.DRUE", line 186

ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320

We can observe that:

a) If the index has been created TRANSACTIONANL with SDATA sections NOT optimized for search, the query will consider only one value when using SDATA operator;

b) If we enable the TRANSACTIONAL more after the index creation, the SDATA sections optimized for search are not updated until the SYNC is scheduled or executed on demand.

c) Altering the index after its creation, using ALTER INDEX, does not raise the same errors.

Changes

 N/A

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.