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);