RE: CTX Indexes
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <Peter.Hitchman@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 May 2005 11:26:33 -0400
Tom,
>> If I deleted the record, resync does not remove those entries
>> from the tables, but optimize does.
Hmmm, interesting.
>>should I run both procedures?
I *guess* would base on your findings.
I'm my case we should be ok because we flat out rebuild the whole index?
This is clear from a space gain...rows must be deleted or clean up on complete
rebuild.
Chris Marquez
Oracle DBA
-----Original Message-----
From: Mercadante, Thomas F (LABOR) [mailto:Thomas.Mercadante@xxxxxxxxxxxxxxxxx]
Sent: Wed 5/25/2005 11:25 AM
To: Marquez, Chris; Peter.Hitchman@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: CTX Indexes
Chris,
What I found was that if I insert a record into a table and run
resync_index, entries are placed into the $I, $K, $N & $R tables. If I
deleted the record, resync does not remove those entries from the
tables, but optimize does.
So my basic question is, should I run both procedures? Or does it not
matter that entries are still in the tables. They must be marked as
"invalid" or something someplace else.
Tom
-----Original Message-----
From: Marquez, Chris [mailto:cmarquez@xxxxxxxxxxxxxxxx]
Sent: Wednesday, May 25, 2005 11:17 AM
To: Mercadante, Thomas F (LABOR); Peter.Hitchman@xxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: CTX Indexes
>>CTX_DDL.OPTIMIZE_INDEX
What is this? What does it do?
>>CTX_DDL.RESYNC_INDEX
We run nightly.
>>Rebuild Context Index via DBMS_JOB
We use pl/sql and DBSM_JOB to rebuild our Context Index once a week.
We used to never do it...the were over 1GB after years.
We the did it once every 3 months.
Now we do it every 7 days same Context Indexes with growth over years
are only 100MB after rebuild.
Don't know if it help performance, but helps on space.
...
v_statment:= 'drop index ...._ctx_idx';
EXECUTE IMMEDIATE v_statment;
-- dbms_output.put_line('-------'||v_statment||'-----');
v_statment:= 'create index ...._ctx_idx on .... indextype is
ctxsys.context parameters(''storage .....CTX_STORAGE'')';
EXECUTE IMMEDIATE v_statment;
-- dbms_output.put_line('-------'||v_statment||'-----');
Chris Marquez
Oracle DBA
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Mercadante, Thomas F
(LABOR)
Sent: Wed 5/25/2005 11:04 AM
To: Peter.Hitchman@xxxxxxxxxxx; 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
that the analyze did nothing, it only recorded the fact that it had been
run. The optimization method was changed to so that it was carried out =
at query time to find the token document count.
The object owned by ctxsys were not analyzed, but the
dr$<your_index_name>$I etc objects created in the application schema
that owned the text indices were analyzed. As well as a regular
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,
20
We are just creating CTX type indexes for the first time and I have a
couple of questions.
20
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.
20
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)?
20
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
Other related posts:
