OraFAQ Forum: Text & interMedia » SYS Context index is not working for new records

Home » Server Options » Text & interMedia » SYS Context index is not working for new records (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SYS Context index is not working for new records [message #514709]

Wed, 06 July 2011 01:00

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

Report message to a moderator

Re: SYS Context index is not working for new records [message #514723 is a reply to message #514709]

Wed, 06 July 2011 01:42

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SYS Context index is not working for new records [message #514734 is a reply to message #514723]

Wed, 06 July 2011 03:08

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)');

Report message to a moderator

Re: SYS Context index is not working for new records [message #514748 is a reply to message #514734]

Wed, 06 July 2011 04:29

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

Re: SYS Context index is not working for new records [message #534170 is a reply to message #514748]

Sat, 03 December 2011 12:47

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.

Report message to a moderator

Re: SYS Context index is not working for new records [message #534173 is a reply to message #534170]

Sat, 03 December 2011 13:40

Barbara Boehmer
Messages: 9101
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 23:35:04 CST 2024