RE: ctx_ddl.sync_index question

  • From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <ganstadba@xxxxxxxxxxx>, "Oracle L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 8 Apr 2009 11:37:50 -0400

Michael,

 

This simply keeps the index in sync.  So inserts into the table are
added to the context index by this job.

 

We run a cron job to do this on a nightly basis for some of our
databases.

 

We have a COTS application that creates indexes like this:

 

create index {index_name} on {table_name}({column_name}) indextype is
CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)');

 

Note the 'SYNC ON COMMIT' option.  According to the docs, this keeps the
index in sync at commit time.  I'm not sure how this performs, but we
are just starting out with it.


Hope this helps.


Tom

 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael McMullen
Sent: Wednesday, April 08, 2009 10:25 AM
To: 'Oracle L'
Subject: ctx_ddl.sync_index question

 

10.2.0.4

Redhat linux

2 cpu

12 GB ram

COTS app: kana

 

Kana is telling our developer that we need to run 

begin

ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;

to resync the text index.

The index is 2.5GB and there are 3 million records in ctx_user_pending.
Of course, there is no dev/test or anything and they want to run it
straight on prod. Why the app doesn't have a job predefined to do this I
don't know. I'm trying to get a handle on what this will actually do
under the covers. This will be run off peak load but is it so resource
intensive that the system will essentially lock (from the users
viewpoint) or any idea of how long this type of thing would take.

I've told them we will just have to go with it as again we have no way
of testing beforehand.

 

Thanks

Mike

 

 

 

Other related posts: