Oracle Text – 5 million rows, 100 hours and counting

Last week, I was using DBMS_REFINITION to migrate a LONG column on a table to a CLOB so that I could use that column in a MULTI_COLUMN_DATASTORE for an Oracle Text search.

 Unfortunately, on a development system with nothing else happening, it took hours and hours. I left it running with the datafiles set to AUTOEXTEND ON and I think it took the best part of a day.

I knew that I would be off site on Friday and the following Monday so I set off an Oracle Text index creation, thinking that it might take a day or so, at worst.

However, as I write, it’s still going. So, started on the 1st, still going on the 6th. Five million rows. Seems a bit excessive to me.

And as it gets towards the end, although we’re not there yet, the indexing has slowed right down. Initially, each parallel process was indexing 100 documents every 6-8 seconds. Now each 100 documents takes nearly 1 minute so, practically, we’re never going to get to the finish.
CREATE INDEX i_myindex_ctx01
ON mytable(ctx_search_multistore_column)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('LEXER mylexer
STORAGE mystorage
DATASTORE mydatastore
SECTION GROUP mysection
FILTER CTXSYS.NULL_FILTER
STOPLIST CTXSYS.EMPTY_STOPLIST
TRANSACTIONAL
SYNC (EVERY "SYSDATE+1/24")
MEMORY 250M')
PARALLEL 4;


There must be some more optimisation that I can do – will need to research, but definitive advice is a bit thin on the ground.

For development purposes, I could make the table NOLOGGING but that probably wouldn’t reflect what I could do in production eventually.