Home » Server Options » Text & interMedia » SYS Context index is not working for new records (oracle 10g)
SYS Context index is not working for new records [message #514709] Wed, 06 July 2011 01:00 Go to next message
darshanvaidya
Messages: 2
Registered: July 2011
Junior Member
Hi,
I am new in oracle,I have one table tb_search and am searching few contents from one clob column.after creating following index,

I have created following index

CREATE INDEX INDX_SEARCH ON
TB_SEARCH (SEARCH_CLOB) INDEXTYPE IS CTXSYS.CONTEXT;


SELECT ID FROM TB_SEARCH WHERE
CONTAINS(SEARCH_CLOB, '%keyboard%' )>0 ;

search is working fine on existing records on tb_search table but if any record get inserted in tb_search then that search is not included into the search query result.wat wud be the reason Please suggest.

regards
Darshan



Re: SYS Context index is not working for new records [message #514723 is a reply to message #514709] Wed, 06 July 2011 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know much about context but waiting for our expert (Barbara) what I can say is that this kind of index is not updated each time you insert or update a row, you have to synchronize it from time to time.

Regards
Michel
Re: SYS Context index is not working for new records [message #514734 is a reply to message #514723] Wed, 06 July 2011 03:08 Go to previous messageGo to next message
darshanvaidya
Messages: 2
Registered: July 2011
Junior Member
Hi Michel,
Thanks for giving a hint,
i tried following and it is working

CREATE INDEX INDX_SEARCH ON
TB_SEARCH (SEARCH_CLOB) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT)');

Re: SYS Context index is not working for new records [message #514748 is a reply to message #514734] Wed, 06 July 2011 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Re: SYS Context index is not working for new records [message #534170 is a reply to message #514748] Sat, 03 December 2011 12:47 Go to previous messageGo to next message
TheKevinRyan
Messages: 5
Registered: December 2011
Location: New York
Junior Member
Alter index <index name> rebuild;

That takes care of that. I didn't see it explicitly stated here.
Re: SYS Context index is not working for new records [message #534173 is a reply to message #534170] Sat, 03 December 2011 13:40 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
There are multiple options with context indexes. The index could be synchronized and optimized periodically or rebuilt periodically or the index could be recreated to automatically synchronize, as the original poster chose to do, which would still require periodic optimization and rebuilding. The index could also be created as transactional, so that no synchronization, optimization, rebuilding, or dropping and recreating would be necessary.

Previous Topic: Need help on CATSEARCH
Next Topic: Oracle text - CONTAINS sql query returning wrong result
Goto Forum:
  


Current Time: Thu Mar 28 06:44:47 CDT 2024