Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CTX Indexes
Hmmm...do you mean CTX_DDL.SYNC_INDEX (sans "RE")? For a minute there,
I thought I was running the wrong procedure.
BTW, we run it every minute for an index whose key changes about 100
times/day the last I checked. There's no discernable overhead for us on
a 4-way HP L3000 w/8GB RAM and about 300 total concurrent DB
connections.
Rich
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Peter.Hitchman_at_thomson.com
Sent: Wednesday, May 25, 2005 10:42 AM
To: oracle-l_at_freelists.org
Subject: RE: CTX Indexes
Hi,
You need to run RESYNC_INDEX so that inserts and updates are reflected =
in the index. So how often and when you run this depends on your =
application. For the one I worked on the system was a kind of backend =
data mart so I had the RESYNC happening as part of the loading process,
=
which was a regular batch process.
As the index is updated for DML it becomes fragmented, since what is =
doing is storing what terms are in what indexes and in common with other
=
text search engines, Oracle does not try to optimize the storage of this
=
information on the fly, that is just too expensive.
So to fix this you use the OPTIMIZE_INDEX procedure. This has a few =
options about how aggressive you want it to be. As with most other =
things, how and how often you run this will depend on your application.
I recommend the Oracle Notes on MetaLink:- =
120609.1,120610.1,120611.1,104262.1,150307.1,221940.1 and anything else
=
written by Roger Ford.
Regards
Pete
-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante_at_labor.state.ny.us]
Sent: 25 May 2005 16:04
To: Hitchman, Peter (TS UK); oracle-l_at_freelists.org
Subject: RE: CTX Indexes
Peter,
Speaking of CTX_DDL.OPTIMIZE_INDEX, do you remember if I should run that procedure regularly or CTX_DDL.RESYNC_INDEX? Should I run them both?
Thanks
Tom
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 26 2005 - 13:27:15 CDT