OraFAQ Forum: Text & interMedia » Refresh of indexes on CLOB fields.

Home » Server Options » Text & interMedia » Refresh of indexes on CLOB fields. (Oracle,10.2.0.4.0,linux red hat 4)

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

Refresh of indexes on CLOB fields. [message #355050]

Wed, 22 October 2008 06:54

Malakay79
Messages: 41
Registered: September 2007

Member

I have 1 index on a clob column create in this way:

CREATE INDEX index_name
ON TABELLA (COLONNA)
INDEXTYPE IS ctxsys.context
PARAMETERS ( 'STOPLIST mystop' );

This fild is updated very often.
I have created a job that refresh that index with ctx_ddl.sync_index

There is another way to schedule his refresh?

IF I use a trigger after insert or update I think this can be too heavy because sometimes there are 200 people that modify that table in that column.

Thanks

Report message to a moderator

Re: Refresh of indexes on CLOB fields. [message #355100 is a reply to message #355050]

Wed, 22 October 2008 11:52

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

You can use SYNC(ON COMMIT) or whatever interval you want in your index creation parameters, but remember that synchronizing causes fragmentation, so you need to periodically optimize as well.

Report message to a moderator

Re: Refresh of indexes on CLOB fields. [message #355108 is a reply to message #355050]

Wed, 22 October 2008 12:58

Malakay79
Messages: 41
Registered: September 2007

Member

I try to execute this command:
CREATE INDEX index
ON tabella (colonna)
INDEXTYPE IS ctxsys.context
PARAMETERS ( 'STOPLIST mystop
SYNC (EVERY "SYSDATE+10/1440")' );

but I get this error:

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-27486: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

If I create the index with sync (ON COMMIT) it works.

Does anyone know why?

[Updated on: Wed, 22 October 2008 13:13]

Report message to a moderator

Re: Refresh of indexes on CLOB fields. [message #355136 is a reply to message #355108]

Wed, 22 October 2008 15:49

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The user creating the index must have the CREATE JOB privilege granted explicitly, not as part of a role:

SQL> GRANT CREATE JOB TO user_createing_index;

Report message to a moderator

Previous Topic:	Query expansion and FIRST_ROWS problem
Next Topic:	Problems with CTX_DOC.SNIPPET on HTML documents

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 18:48:22 CST 2024