Speed up Oracle Text indexing or let the indexer work only on low load times

Question

We're using a Oracle Text CTXSYS.CONTEXT index to index about half a million rows containing metainformation. The information is spread over two tables that are combined by a procedure that the indexer calls at runtime (functional index).

When I run the CREATE INDEX on my local machine (simple dualcore notebook) the index is built in about 3 minutes. On our DB server which runs on Solaris with 8 cores and 16G of RAM it takes abozt 24 hours to create an index for the same (exactly the same) data.

Sample code: This is our index feeder for two tables and 3 columns:

create or replace procedure docmeta_revisions_text_feeder 
    ( p_rowid in rowid , p_clob in out nocopy clob) as v_clob CLOB begin
    FOR c1 IN (select DM.DID, DM.XDESCRIB || ' ' || DM.XAUTHOR AS data
        from DOCMETA DM
        WHERE ROWID = p_rowid) 
    LOOP
        v_clob := v_clob || c1.data;
        FOR c2 IN (
            SELECT ' ' || RV.DDOCTITLE AS data
            FROM   REVISIONS RV
            WHERE  RV.DID = c1.DID)
        LOOP
            v_clob := v_clob || c2.data;
        END LOOP;
    END LOOP;
    p_clob := v_clob;    
    end docmeta_revisions_text_feeder

These are the preferences

BEGIN
CTX_DDL.CREATE_PREFERENCE ('concat_DM_RV_DS', 'USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE ('concat_DM_RV_DS', 'PROCEDURE',
'docmeta_revisions_text_feeder');
 END;

Now we create the index

CREATE INDEX concat_DM_RV_idx ON DOCMETA (FULLTEXTIDX_DUMMY)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('datastore concat_DM_RV_DS 
section group CTXSYS.AUTO_SECTION_GROUP
') PARALLEL 4;

The data mostly consists of a simple title or author name + a short description with < 1k text.

I tried to play a little bit with the involved memory settings and the PARALLEL parameter but haven't any success. So here come my questions:

is there a way to pause and resume an indexing process (I have the CTX_SYS role at hand) ?
has anyone a hint which parameter could be tweaked (esp. the memory size)?
is it possible to export and import a text index? -> then I could carry out the indexing on my local machine and simply copy it to our server
can an indexer run with "lower priority"?
it is possible that the indexer has been disturbed by locking operations (it's a staging machine that other's access in parallel). Is there a way to lock the involved tables, create the index and unlock them afterwards ?

Speed up Oracle Text indexing or let the indexer work only on low load times

1 Answers