Hi Community,
My team is exploring the use of Oracle Text and we have come across a bind. A overview is
We have two normalised tables TABLE_A and TABLE_B. We want to ideally search on the merged content of both the tables. ( JOIN QUERY based on certain WHERE CLAUSE).
On reading the documentation we see two possible options to make this work.
-
Using MULTICOLUMN DATASTORE preference: We create a merged table before hand and make a index on it using MUTLICOLUMN Datastore preference.
Pros → I can do targeted search ( search only in particular column ) along will all search.
→ All the columns of the both the table can be viewed in results.
. → Faster and simpler indexing.
Cons → A extra space has to be allocated to create a new denormalised table containing duplicate data of existant table. ( On Prem DBs wont prefer this )
→ On data change in actual tables, trigger has to be create to update merged table which in turn would trigger the reindex.
-
Using USER_DATASTORE preference: A procedure is written based on the same join query used before for merging the table.
Procedure Used is
CREATE OR REPLACE PROCEDURE concat_columns ( r IN ROWID , c IN OUT NOCOPY CLOB ) AS
BEGIN
SELECT
a.column_1 || ‘ ’ || a.column_2 || ‘ ’ || b.column_1 || ‘ ’ || b.column_2
INTO c
FROM
TABLE_A a,
TABLE_B b
WHERE
a.column_3 = b.column4 AND a.rowid = r;
END;
This procedure is used in user_datastore preference while indexing.
Pros → No need of an extra table to be created.
Cons → Since one concatenated clob is created per row it only contains the data. No option of target search ( column specific search).
. → Considering the index is created on TABLE_A. While the search happens across data from both the tables but when the result is shown it only shows the columns of TABLE_A but i want to view across both.
. → Indexing is slow as the procedure is executed in real time.
Are these the only two options ? Also are these the only pros and cons of the methods and is there any workaround for the problems.
Thank you for taking the time.