Hi,

I'm wondering what I can do to speed up the creation of a context index.
The target table contains five million rows.
This index is using a multi column datastore across three columns, one of which is a CLOB.
Tailing the index creation logs, initially each parallel process was indexing 100 documents every six seconds but as more documents have been indexes, so the index rate has decreased.
Currently, the index creation has been running for six days (!!!!), they've indexed four million out of the five million rows but the index creation has slowed to such an extent that for each parallel process it takes one minute to index 100 documents.

This is a standalone development database with no other activity against it and with no updates happening against it.
If I set the table to NOLOGGING then I could get some increase in index creation speed but I would not be able to do that on release into production so there seems little point in me doing that.

There doesn't seem to be a definitive reference to this sort of tuning.

I had initially set the index creation to be done in parallel based on a 2 dual core CPUs and had increased the amount of memory that the index creation could use.

This my initial index creation:

CREATE INDEX i_search_ctx01
ON search_demo(ctx_search_multistore_column)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('LEXER my_lexer
STORAGE my_storage
DATASTORE my_datastore
SECTION GROUP my_section
FILTER CTXSYS.NULL_FILTER
STOPLIST CTXSYS.EMPTY_STOPLIST
TRANSACTIONAL
SYNC (EVERY "SYSDATE+1/24")
MEMORY 250M')
PARALLEL 4;

Any thoughts?

In the index creation log file you will notice that first the index is created in memory until it reaches the indexing memory limit, flushes it to disk ($I table), and continue to index more documents in memory, and so on. So basically the entire indexing task is broken up into smaller batches (depending on the size of the indexing memory you have set) and each batch is processed in two stages, the in-memory indexing and the flush to disk.

Looking at the index creation log, Is the degradation in indexing performance limited to in-memory indexing stage or to the disk flush stage or both?

If it is limited to in-memory indexing stage then you might be hitting a memory leak bug which you can prove by taking PGA dumps and validating that some sub-heap is growing uncontrollably.

Faisal