Invalid rowids in dr$pending makes sync_index slow
HUJan 4 2011 — edited Feb 2 2011I have an odd problem on a production database (Windows 9.2.0.7).
I don't know yet when I can try to fix it, but would like your feedback in advance.
There are 7 text indexes in the database.
One has 800000+ rows, that appear to be invalid
- select count(*) from ctx_user_pending where pnd_index_name='MYINDEX1' and pnd_rowid not in (select rowid from MYTABLE1);
sync_index leaves a compareable number of pending rows.
While the problem looks fairly straightforward, I am puzzled by this:
Sync_index is slow on all indexes. On another table, there is an indexed name field (varchar2(30))
If there is no pending rows for this index, sync_index returns immediately, but if there is one pending row, it sync_index takes 45 seconds to respond.
Could it be, that both problems are caused by a different problem?
Any alternatives to drop index + create index?
(Am I allowed to say truncate dr$pending after "syncing" what can be "synced"?)
The index appears to work fine except for the sync problem