No pending rows using triggers on Text indexed columns with MULTI_COLUMN_DATASTORE or USER_DATASTORE to bring in the various columns for indexing (Doc ID 1613741.1)

Last updated on MARCH 03, 2019

Applies to:

Symptoms

We have a Oracle Text CONTEXT index on multiple columns using a MULTI_COLUMN_DATASTORE or USER_DATASTORE to bring in the various columns for indexing with a BEFORE ROW trigger to propagate changes and update the Text index if any of the columns (other than the one on which the index is based) gets updated and add them to the pending table for indexing.

Although the trigger works and fires it does not add any entries in pending table or ctx_user_pending view.

This works well, but the kernel doesn't know which columns you've used in the index.  As far as the kernel is concerned, you have created the index on a specific column, and it will only update the index when the contents of that column change.

Changes

- A fresh 12gR1 database with Text indexes using a MULTI_COLUMN_DATASTORE or USER_DATASTORE to bring in the various columns for indexing with a BEFORE ROW trigger

or
- Database has been upgraded from 11.2.0.3 to 12.1.0.1.0

or
- <Patch 14155722> has been applied on affected database

Cause

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