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@xxxxxxxxxxxxxxxxx] Sent: 25 May 2005 16:04 To: Hitchman, Peter (TS UK); oracle-l@xxxxxxxxxxxxx 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 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Peter.Hitchman@xxxxxxxxxxx Sent: Wednesday, May 25, 2005 9:53 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: CTX Indexes Hi, Well it has been a while, but back in 8.1.7 Oracle changed things so =3D that the analyze did nothing, it only recorded the fact that it had been =3D run. The optimization method was changed to so that it was carried out = =3D at query time to find the token document count. The object owned by ctxsys were not analyzed, but the =3D dr$<your_index_name>$I etc objects created in the application schema =3D that owned the text indices were analyzed. As well as a regular =3D CTX_DDL.OPTIMIZE_INDEX. Regards Pete -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mercadante, Thomas F (LABOR) Sent: 25 May 2005 13:00 To: oracle-l Subject: CTX Indexes All, =3D20 We are just creating CTX type indexes for the first time and I have a couple of questions. =3D20 I know that I need to periodically resync the indexes using the Ctx_ddl package. We plan on doing this nightly for now until the apps folks tell us it needs to be done more often. =3D20 I also tried to gather stats on the index, but the stats columns in the user_indexes view did not get updated. Is gathering stats on ctx type indexes a waste of time? Should I be gathering stats on some CTX tables instead - like the DR$INDEX_VALUE table where all of the data from the column is actually stored (this is a big assumption on my part)? =3D20 Thanks Tom -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l