oracle text performance

oracle text的索引跟其它的基本索引不同，当执行dml操作时，数据库不会自动维护这些索引(不过会将这些dml操作记录在表CTX_USER_PENDING里)，需要调用存储过程ctx_dll.sync_index来维护索引。

当执行ctx_dll.sync_index时，会先去CTX_USER_PENDING表里查看未索引的行，然后将其索引。ctx_dll.sync_index能够利用多cpu的优势，并行执行。

Index Fragmentation

The CONTEXT index is an inverted index where each word contains the list of documents that contain that word. For example, after a single initial indexing operation, the word DOG might have an entry as follows:

   DOG DOC1 DOC3 DOC5

When new documents are added to the base table, the index is synchronized by adding new rows. Thus if you add a new document (DOC 7) with the word dog to the base table and synchronize the index, you now have:

DOG DOC1 DOC3 DOC5
DOG DOC7

Subsequent DML will also create new rows:

DOG DOC1 DOC3 DOC5

DOG DOC7

DOG DOC9

DOG DOC11

我们可以用ctx_dll.optimize_index来优化索引，去除索引碎片。

You optimize your index after you synchronize it. Optimizing an index removes old data and minimizes index fragmentation,

which can improve query response time. Querying and DML may proceed while optimization takes place.

Some users choose to perform frequent time-limited full optimizations along with occasional rebuild optimizations.

 

下面是一些限制，关于并行执行的

You can run CTX_DDL.SYNC and CTX_DDL.OPTIMIZE at the same time.

You can also run CTX_DDL.SYNC and CTX_DDL.OPTIMIZE with parallelism at the same time.

However, you should not run CTX_DDL.SYNC with parallelism at the same time as CTX_DDL.OPTIMIZE,

nor CTX_DDL.SYNC with parallelism at the same time as CTX_DDL.OPTIMIZE with parallelism.

If you should run one of these combinations, no error is generated;

however, one operation will wait until the other is done.

 

看起来，如果数据库服务器的cpu数量如果足够的话，估计性能应该还是不错的。可以每隔几分钟就执行一次

sync_index, optimize_index(full 模式)。晚上执行一次optimize_index(rebuild 模式)。

不太清楚多cpu的工作方式，也不太清楚oracle是怎样利用多cpu的，不知道它在并行执行sync_index时，

会不会用到主cpu？如果不用的话，应该是比较完美吧？

评论

1 楼 fishoflove 2009-10-09  

怎么让索引自动同步?因为需要,但我对ORACLE　TEXT 又不熟悉,找到的方法不管用