Hello experts,
oracle database 12.1.3.0.161018
I am using a number of oracle text ctxsys.context domain indexes in my database.
I have used the user_datastore parameter, which uses a stored procedure and commit on sync parameters. On insert, the new index is updated as expected. However, my stored procedure's SQL has joins on a number of tables associated as foreign keys to the base table. What i require: any updates to the associated tables should update the basetable's index/tokens. e.g. the ctx_ddl.sync_index(''); or ctx_ddl.optimize_index('','full'); does not help either until i recreate the domain index (this tokenizes the columns in the associated tables as expected) However it takes about 3-4 minutes to complete where the total number of rows in less than 500K across all tables. basically that's slow and not usable in production. kindly advice on what can be done to ensure inserts/updates on the secondary tables update the domain index as required. thanks