We are on this Oracle version:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.7.0.0.0
Our use case:
Every morning we are loading a lot of data into a table. The data is loaded from a powershell script that has 6 database connection that run in parallel,
We do not need the Oracle Text index to be synchronized before all data has been loaded into the table.
However, during data load, this statement can be seen to run after a transaction has added rows to the table:
ctxsys.syncrn( :idxownid,
:idxoname,
:idxid,
:ixpid,
:rtabnm,
:flg,
:smallr);
The statement ctxsys.syncrn... runs for several minutes and increases the total load time from 1 to 4 hours.
How can we avoid ctxsys.syncrn from being run?
We only need CTX_DDL.SYNC_INDEX(......) to run after all data has been loaded into the table.
Our text index was created like this:
CREATE INDEX MY_ALL_TEXT_IDX
ON MY_ALL_TEXT(TEXT)
INDEXTYPE IS CTXSYS.CONTEXT
LOCAL (
PARTITION EU,
PARTITION US,
PARTITION DEF)
NOPARALLEL;