Home » Server Options » Text & interMedia » Refresh of indexes on CLOB fields. (Oracle,10.2.0.4.0,linux red hat 4)
Refresh of indexes on CLOB fields. [message #355050] Wed, 22 October 2008 06:54 Go to next message
Malakay79
Messages: 26
Registered: September 2007
Junior 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
Re: Refresh of indexes on CLOB fields. [message #355100 is a reply to message #355050] Wed, 22 October 2008 11:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7938
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.

Re: Refresh of indexes on CLOB fields. [message #355108 is a reply to message #355050] Wed, 22 October 2008 12:58 Go to previous messageGo to next message
Malakay79
Messages: 26
Registered: September 2007
Junior 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 Go to previous message
Barbara Boehmer
Messages: 7938
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;
Previous Topic: Query expansion and FIRST_ROWS problem
Next Topic: Problems with CTX_DOC.SNIPPET on HTML documents
Goto Forum:
  


Current Time: Fri Aug 01 08:00:13 CDT 2014

Total time taken to generate the page: 0.11750 seconds