Re: Incremental indexing in Oracle Text

I've used CTX_DDL.SYNC_INDEX with good success for incremental indexing. In fact, I have been unable to do anything other than an incremental index. If I attempt to create a fully populated index all at once with CREATE INDEX, the operation bombs. I use the NOPOPULATE parameter in the CREATE INDEX command to get around this problem, so CREATE INDEX runs instantly. Then I issue dummy updates to say one percent of the rows in the table, then call CTX_DDL.SYNC_INDEX, then update another one percent and do the same, etc. Once this process is done you just have to call SYNC_INDEX occasionally.

I am concerned about this degree of incremental indexing since it seems it might lead to a highly unoptimized index. I've run the optimize stored proc but it has made no difference in terms of perforance.

As things stand, I have some simple text-based queries, e.g.

WHERE CONTAINS(mytextfield, 'somestring, otherstring') > 0 that several times longer than my "old" way of querying as

WHERE instr(somefield, '%somestring%')>0 or instr(somefield, '%otherstring%')>0

When I say several times, I mean the text query with CONTAINS takes about a minute, while the "slower" way takes about 10-15 seconds, on a 600,000 row table. This is quite baffling. Received on Wed Aug 20 2003 - 17:35:08 CDT