如何同步和优化 Oracle Text 索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1916244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sync and optimize an Oracle Text index?
提问by trunkc
We want to use a ctxsys.contextindex 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.
我们想使用ctxsys.context索引类型进行全文搜索。但我很惊讶，这种类型的索引不会自动更新。我们有 300 万份文档，每天大约有 1 万次更新/插入/删除。
What are your recommendations for syncing and optimizing an Oracle Text index?
您对同步和优化 Oracle Text 索引有什么建议？
采纳答案by maximdim
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:
我认为 'SYNC EVERY' 选项，如先前答案中所述，仅适用于 Oracle 10g 或更新版本。如果您使用的是旧版本的 Oracle，则必须定期运行同步操作。例如，您可以创建以下存储过程：
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;
and then schedule it run via DBMS_JOB:
然后通过 DBMS_JOB 调度它运行：
DBMS_JOB.SUBMIT(job_id, 'sync_ctx_indexes;', SYSDATE, 'SYSDATE + 1/720');
As for index optimization, following command can be used (also can be scheduled with DBMS_JOB or via cron):
对于索引优化，可以使用以下命令（也可以使用 DBMS_JOB 或通过 cron 调度）：
alter index my_index rebuild online parameters('optimize full maxtime 60');
There is also CTX_* package with similar function available.
也有类似功能的 CTX_* 包可用。
回答by ewernli
What do you mean by "not automatically updated"?
“不自动更新”是什么意思？
The index can be synchronized on commit or periodically.
索引可以在提交时同步或定期同步。
Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON COMMIT)')
Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS 'SYNC (EVERY "SYSDATE+1/24")')
I you don't need real-time search accuracy our DBA recommended to sync the index periodically, say each 2 min. If you can afford to do it overnight, then even better. What is best depends on your load and the size of the document.
我不需要我们的 DBA 建议的实时搜索准确性来定期同步索引，比如每 2 分钟。如果你有能力在一夜之间完成它，那就更好了。什么是最好的取决于您的负载和文档的大小。
These links can probably provide you with more information:
这些链接可能会为您提供更多信息：
For DBA advice, maybe serverfault is better?
对于 DBA 建议，也许 serverfault 更好？
回答by markgiaconia
Putting this here as an update for Oracle 12C users. If you use the index in real time mode, then it keeps items in memory, and periodicially pushes to the main tables, which keeps fragmentation down and enables NRT search on streaming content. Here's how to set it up
将其作为 Oracle 12C 用户的更新放在这里。如果您在实时模式下使用索引，那么它会将项目保留在内存中，并定期推送到主表，从而减少碎片并启用对流内容的 NRT 搜索。这是设置方法
exec ctx_ddl.drop_preference ( 'your_tablespace' );
exec ctx_ddl.create_preference( 'your_tablespace', 'BASIC_STORAGE' );
exec ctx_ddl.set_attribute ( 'your_tablespace', 'STAGE_ITAB', 'true' );
create index some_text_idx on your_table(text_col) indextype is ctxsys.context PARAMETERS ('storage your_tablespace sync (on commit)')
this will set up the index in NRT mode. It's pretty sweet.
这将在 NRT 模式下设置索引。它很甜。
