Compute statistics for CTXSYS.DR$PENDING ?
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.