Changing USER_DATASTORE of an existing text index with MULTI_COLUMN_DATASTORE will have any advantag

MoneDR

Hello,

I am using Oracle12.2 and I have one oracle text index defined for a dummy column that concatenate 2 VARCHAR2 columns of the same table. The index creation use USER_DATASTORE and has defined an user datastore procedure. No filter is defined for the text index. It will be any gain (performance/resources) if I will change the text index to use the MULTI_COLUMN_DATASTORE instead of USER_DATASTORE? (there are 5 text indexes on database that are in this situation).

Thank you very much for your help

Current code:

CREATE OR REPLACE PROCEDURE T_CONCAT (p_rowid IN ROWID, p_clob IN OUT CLOB) AS v_clob CLOB;

BEGIN

    FOR c1 IN (SELECT COL1, COL1 || ' ' || COL2  AS data FROM TABLE_T WHERE ROWID = p_rowid)

    LOOP

        v_clob := v_clob || c1.data;

       

    END LOOP;

    p_clob := v_clob;

END;

/

ctx_ddl.create_preference('TABLE_T_DATASTORE', 'USER_DATASTORE');

ctx_ddl.set_attribute('TABLE_T_DATASTORE', 'PROCEDURE', 'T_CONCAT');

CREATE INDEX TEXT_IDX_T ON TABLE_T (CTXSEARCH)

INDEXTYPE IS CTXSYS.CONTEXT

PARAMETERS ('DATASTORE TABLE_T_DATASTORE STOPLIST TSTOP LEXER TLEX SYNC (ON COMMIT)') PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);

Roger Ford-Oracle

You're unlikely to see a huge benefit from moving to MULTI_COLUMN_DATASTORE. You might see a small improvement from not having to invoke the procedure.

Note that your parameter list in the USER_DATASTORE should read:

(p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB) AS v_clob CLOB;

to avoid the need to copy the clob when passed in or out of the procedure.

If you do move to MULTI_COLUMN_DATASTORE, set the DELIMITER attribute to 'NEWLINE'.  Otherwise it will insert section tags between the fields, which would be a change in behavior.

MoneDR

Thank you very much for your fast answer.