RE: CTX Indexes

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_at_labor.state.ny.us] Sent: Wed 5/25/2005 11:25 AM
To: Marquez, Chris; Peter.Hitchman_at_thomson.com; oracle-l_at_freelists.org 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_at_collegeboard.org] Sent: Wednesday, May 25, 2005 11:17 AM
To: Mercadante, Thomas F (LABOR); Peter.Hitchman_at_thomson.com; oracle-l_at_freelists.org
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_at_freelists.org on behalf of Mercadante, Thomas F (LABOR)
Sent: Wed 5/25/2005 11:04 AM
To: Peter.Hitchman_at_thomson.com; 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

-----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 9:53 AM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l