Forum Stats

  • 3,815,220 Users
  • 2,258,979 Discussions
  • 7,893,004 Comments

Discussions

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

MoneDR
MoneDR Member Posts: 2 Red Ribbon
edited Nov 8, 2019 1:26PM in Text

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

Answers

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2019 12:05PM

    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
  • MoneDR
    MoneDR Member Posts: 2 Red Ribbon
    edited Nov 8, 2019 1:15PM

    Thank you very much for your fast answer.