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]=20 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:=3D 'drop index ...._ctx_idx';   EXECUTE IMMEDIATE v_statment;
-- dbms_output.put_line('-------'||v_statment||'-----');  =20
  v_statment:=3D '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
=20
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=20 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 = =3D

at query time to find the token document count.

The object owned by ctxsys were not analyzed, but the=20 dr$<your_index_name>$I etc objects created in the application schema=20 that owned the text indices were analyzed. As well as a regular=20 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