Oracle "matches" multiple columns

Question

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?

Answer 1

One method is to use the MULTI_COLUMN_DATASTORE. Below is an example:

drop table t1 purge;

create table t1 (column1 varchar2(100), column2 varchar2(100));
insert into t1 values ('HELLO', 'WORLD');
insert into t1 values ('SAY', 'HELLO');
insert into t1 values ('SOMETHING', 'ELSE');
insert into t1 values ('WORLD', 'CUP');
commit;

Create the datastore with the desired columns:

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'column1, column2');
end;
/

Create the Text index using this datastore:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore');

Or if you want the index maintained on commit:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore sync(on commit)');

Searching for the words HELLO or WORLD:

select * from t1 where contains (column1, 'HELLO or WORLD') > 0;

COLUMN1    COLUMN2  
---------- ----------
HELLO      WORLD     
SAY        HELLO     
WORLD      CUP     

Notice that you do not need to specify all columns in the query, but you need to specify an operator between words.

Answer 2

I want to share another alternative:

Create a new column having the data of all columns used to find:

ALTER TABLE employees ADD criterio varchar2(100);

update employees set criterio = nickname || ' ' || name || ' ' || lastname;

Create the index using the new column Oracle Text:

create index employees_criterio on employees(criterio) 
indextype is ctxsys.context 
parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

Now an example:

SELECT SCORE(1), name from employees
       WHERE CONTAINS(criterio, 'billy or cool', 1) > 0
       ORDER BY SCORE(1) DESC;

However, using @Balazs_Papp's answer you not need to create another column and updating data once it changes.