ConText Index Maintenance - Optimize Blocking Sync

Hi,

Sorry for the cross-post, I posted this on comp.databases.oracle.misc but I never know which newsgroup to post to for Oracle Text questions.  This groups seems to be much more active.

I'm using Oracle 9.2.0.5.0.

I have a context index that I am trying to regularly sync and optimize. I have started 2 dbms_jobs, one to sync every hour and one to optimize at midnight every day.

I have noticed however that the sync is blocked by the optimize, optimize holds a lock on the dr$myindex$i table, which sync seems to need to wait for.

Reading MetaLink Note:221940.1
Subject: Technical Overview: Oracle Text version 9.2.0
"New Sync Locking Mechanism

The locking algorithm has been rewritten in 9.2.0 to lock these rows only at the end of indexing. This greatly reduces the window in which user access is blocked."

Reading MetaLink Note:111846.1
Subject:Alter Index Rebuild Online ('sync') [or ('optimize')] Hangs

"But the "sync" and "optimize" algorithms CAN be run
concurrently - if and only if you invoke them via the PL/SQL api."

Has anybody got any experience/advice for this issue? Is it possible to have the two processes running concurrently or should I schedule the sync for daytime hours and optimize overnight? (optimimize takes > 8 hours to run).

Many Thanks,

Kevin Received on Wed Jun 08 2005 - 03:07:26 CDT