Oracle Text (MOSC)

MOSC Banner

Compute statistics for CTXSYS.DR$PENDING ?

edited Apr 27, 2009 3:08AM in Oracle Text (MOSC) 4 commentsAnswered
 Hi,

I  am using  the following procedure to synchronize the Text indexes on  a large database:

CREATE OR REPLACE PROCEDURE MAXIMO.maximo_ts_job AS
BEGIN
   FOR idx in (SELECT pnd_index_name iname from CTX_USER_PENDING) LOOP
      CTX_DDL.SYNC_INDEX(idx.iname);
   END LOOP;
END;
/

Please note that one of the base tables that are referenced in the CTX_USER_PENDING view is CTXSYS.DR$PENDING, which is an index-organized table. I noticed that each time statistics for the CTXSYS.DR$PENDING had been computed, the SELECT statement on the FOR loop took a very long time to return the rows. If I deleted the statistics on CTXSYS.DR$PENDING, the mentioned SELECT statement ran fast again.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center