Jun 11, 2006

sync text index may not work in oracle9i(90130) sometimes

--
--Issue
--
CTX_DDL.SYNC_INDEX doesn't work on local partition of text index after new data is loaded.

CTXSYS.CTX_DDL.SYNC_INDEX('mytext_idx', '5M', 'partition2006');
(introduced in 816)
--
--diagnose
--
1.to check data in the base table

2.to check if data in text index
select text_clob from mytext_table where contains(text_clob,'test',1)>0;

3.to try to re-sync the partition index.
NOT WORK

--
--workaround
--
to rebuild local partition index
alter index mytext_idx rebuild partition partition2006;
--
--Advanced Diagnosis
--
1. to check all index partitions.
select IXP_TABLE_NAME,IXP_INDEX_NAME, IXP_INDEX_NAME,IXP_STATUS
from CTX_USER_INDEX_PARTITIONS;

2. to check which indexes are waiting to be indexed.
re-sync may remove records from this view.
select PND_INDEX_NAME,PND_PARTITION_NAME,COUNT(*) from ctx_user_pending group by PND_INDEX_NAME,PND_PARTITION_NAME;

3.to check the index error.
select * from CTX_USER_INDEX_ERRORS order by ERR_TIMESTAMP


--
--Reseach
--
Local Partitioned Index Maintenance in Technical Overview: Oracle Text version 9.0.1
SYNC OPTIMIZE

sync/optimize won't support in 10g.
alter index INDEXNAME rebuild parameters('sync') partition PARTITIONNAME;
alter index INDEXNAME rebuild parameters('optimize fast/full') partition PARTITIONNAME;

**
**It's not a good idea to use a tentative release in production.
**