RE: CTX Indexes
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <Peter.Hitchman@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 May 2005 13:05:30 -0400
So what is the verdict on Context Indexes and Oracle STATS?
||We have to analyze
>>"have to"
>>Why?...Performance?
Thanks,
Chris Marquez
Oracle DBA
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Mercadante, Thomas F (LABOR)
Sent: Wed 5/25/2005 11:47 AM
To: Peter.Hitchman@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: CTX Indexes
Thank you Peter!
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Peter.Hitchman@xxxxxxxxxxx
Sent: Wednesday, May 25, 2005 11:42 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: CTX Indexes
Hi,
You need to run RESYNC_INDEX so that inserts and updates are reflected =
=3D
in the index. So how often and when you run this depends on your =3D
application. For the one I worked on the system was a kind of backend =
=3D
data mart so I had the RESYNC happening as part of the loading process,
=3D
which was a regular batch process.
As the index is updated for DML it becomes fragmented, since what is =3D
doing is storing what terms are in what indexes and in common with other
=3D
text search engines, Oracle does not try to optimize the storage of this
=3D
information on the fly, that is just too expensive.
So to fix this you use the OPTIMIZE_INDEX procedure. This has a few =3D
options about how aggressive you want it to be. As with most other =3D
things, how and how often you run this will depend on your application.
I recommend the Oracle Notes on MetaLink:- =3D
120609.1,120610.1,120611.1,104262.1,150307.1,221940.1 and anything else
=3D
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 =
=3D3D
that the analyze did nothing, it only recorded the fact that it had been
=3D3D
run. The optimization method was changed to so that it was carried out =
=3D
=3D3D
at query time to find the token document count.
The object owned by ctxsys were not analyzed, but the =3D3D
dr$<your_index_name>$I etc objects created in the application schema =
=3D3D
that owned the text indices were analyzed. As well as a regular =3D3D
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,
=3D3D20
We are just creating CTX type indexes for the first time and I have a
couple of questions.
=3D3D20
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.
=3D3D20
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)?
=3D3D20
Thanks
Tom
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
