This content has been marked as final. Show 2 replies
The link that you provided shows a method for determining the tokens matched. The second page of the thread in the following link shows some methods for obtaining the columns matched.
Text index for UNION queries.
Thanks for such a quick response. I was actually thinking of a solution similar to the Materialised View "Pivot" Solution you provided in that post, i.e.
Another method would be to take an entirely different approach and use a materialized view, as demonstrated below with two columns of two tables. I didn't miss your other questions. I just hadn't gotten to them yet. I respond inbetween doing other things and sometimes it takes a while to think about something and put together a good example.
Rather than text indexing a pivoted materialised view, I am thinking of taking the strongest matching row from the text query result set against the multi-column data store, pivoting that into a global temp table containing column name and value columns, and then just matching the matched words against the global temp table in memory in order to get the column names. One of a number of reasons I want the column names is for the building of an IOT RefData table I can hit before the text index, which will contain known column names and values along with an associated (and configurable) weighting value to give each word when searching the text index. Additionally, if I can build up enough RefData, I may be able to avoid needing to hit the text index in the first place (Can't really properly explain that statement without going into a lot of detail about what I'm doing though).
I did think about pivoting the entire data set on which I've created the text index (to build a column_name:column_value materialised view). However, based on the nature of the data I'm matching, I'm thinking this may be overkill for what I'm doing.
Many thanks for your response. It is always interesting and insightful to discuss these issues on here. It would be great if Oracle were to provide a built-in that would return the multi-column datastore column names that were matched though. ;)