At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index

full-text-searchindexoptimizationoracle

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.

I guess that it depends on the amount of indexed data, the time after which the index does not "performs well" (maybe after a lot of writes), the time taken by running CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) (is it "blocking" DB when running?), etc. so the answer to this question may not be very precise.

However, I would like to know if there are some general rules, principles, recommendations to follow (couldn't find any).

Best Answer

You can try to use the index statistics report by first enabling reporting on the index (BEGIN ctx_output.enable_query_stats(ix_my_indexname); END;). Then you can use the procedure ctx_report.index_stats. While sifting through the CLOB of info in the report, the most useful information to me is the FRAGMENTATION STATISTICS...estimated row fragmentation: 23%

see Oracle documentation here for more thorough examples of the stats report.

Related Solutions

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Execution plan XML would be useful here but as a shot in the dark:

Daft as it may sound, try pushing either the [id] or date predicate into the CONTAINSTABLE query. See SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned - Consider embedding filter conditions as keywords in the indexed text.
I don't remember where but I recall reading an article or blog post some time ago that flagged big OR full-text queries as problematic. IIRC the suggested hack/workaround was to issue a UNION query instead.

For your example, the UNION for 2) would be along the lines of:

INSERT
    @processed_rules
    (
    rule_id
    , story_id
    )
SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term one"')

UNION

SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term two"')

UNION

  SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term three"')

Oracle 11g delete by unique key slow

This could happen if: - Your table MYTABLE has a unique column UNIQCOL - This MYTABLE.UNIQCOL is referenced by some column in another table, say MYTABLE2.UNIQCOL_REF - This MYTABLE2.UNIQCOL_REF is not indexed.

Adding a (non-unique) index to MYTABLE2.UNIQCOL_REF could then solve the problem.

(You said all FKs in MYTABLE are indexed, but you didn't say that all FKs referencing MYTABLE are indexed - they should be to avoid whole-table locks on MYTABLE2)

Best Answer

Related Solutions

Sql-server – SQL Server Fulltext search against big amount of search terms over some period of time

Oracle 11g delete by unique key slow

Related Question