Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.7K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Changing USER_DATASTORE of an existing text index with MULTI_COLUMN_DATASTORE will have any advantag
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
-
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.
-
Thank you very much for your fast answer.