I am developing a web service where a term of search is given, then I return all the matches from employees table. In MySQL I used to do the following:
SELECT * FROM employees WHERE MATCH(name, lastname, nickname) AGAINST('billy jops');
The query finds records that match "billy jops", "billy" or "jops" in name, lastname or description columns of employees tables.
Now I need to emulate this searching pattern in Oracle or at least a little bit closer.
MATCHES SQL Query
I have read about Querying with Oracle Text, and looks like MATCHES SQL Query is what I need. However, looks like the search index is applied just to an unique column:
create table queries (
query_id number,
query_string varchar2(80)
);
insert into queries values (1, 'oracle');
insert into queries values (2, 'larry or ellison');
insert into queries values (3, 'oracle and text');
insert into queries values (4, 'market share');
create index queryx on queries(query_string)
indextype is ctxsys.ctxrule;
select query_id from queries
where matches(query_string,
'Oracle announced that its market share in databases
increased over the last year.')>0
This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document), but not 2 (neither the word larry nor the word ellison appears, and not 3 (there is no text in the document, so it does not match the query).
Could you please let me know how to adjust this query to be compatible with more than one column?
Best Answer
One method is to use the MULTI_COLUMN_DATASTORE. Below is an example:
Create the datastore with the desired columns:
Create the Text index using this datastore:
Or if you want the index maintained on commit:
Searching for the words HELLO or WORLD:
Notice that you do not need to specify all columns in the query, but you need to specify an operator between words.