如何同步和优化Oracle文本索引？ [英] How to sync and optimize an Oracle Text index?

问题描述

我们希望为全文搜索使用 ctxsys.context 索引类型。但我很惊讶，这种类型的索引不会自动更新。我们有300万个文件，每天更新/插入/删除约10k个。

We want to use a ctxsys.context index type for full text search. But I was quite surprised, that an index of this type is not automatically updated. We have 3 million documents with about 10k updates/inserts/deletes per day.

您对Oracle文本索引进行同步和优化的建议是什么？

What are your recommendations for syncing and optimizing an Oracle Text index?

推荐答案

我认为'SYNC EVERY'选项，如前面回答中所述，仅在Oracle 10g或更新版本中可用。如果您使用的是较旧版本的Oracle，则必须定期运行同步操作。例如，您可以创建以下存储过程：

I think 'SYNC EVERY' option, as described in previous answer only available in Oracle 10g or newer. If you're using older version of Oracle you would have to run sync operation periodically. For example, you can create following stored procedure:

CREATE OR REPLACE 
Procedure sync_ctx_indexes
IS
 CURSOR sql1 is select distinct(pnd_index_owner||'.'||pnd_index_name) as index_name from ctx_pending;
BEGIN
 FOR rec1 IN sql1 LOOP
 ctx_ddl.sync_index(rec1.index_name);
 END LOOP;
END;

然后计划通过DBMS_JOB运行：

and then schedule it run via DBMS_JOB:

DBMS_JOB.SUBMIT(job_id, 'sync_ctx_indexes;', SYSDATE, 'SYSDATE + 1/720');

至于索引优化，可以使用以下命令（也可以使用DBMS_JOB或通过cron进行调度） ：

As for index optimization, following command can be used (also can be scheduled with DBMS_JOB or via cron):

alter index my_index rebuild online parameters('optimize full maxtime 60');

还有CTX_ *包提供了类似的功能。

There is also CTX_* package with similar function available.

这篇关于如何同步和优化Oracle文本索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！