Поиск нескольких строк в нескольких столбцах в ORACLE SQL

Учитывая примерные данные:

CREATE TABLE person (fname, lname, street, city, return_from_select ) AS
SELECT 'John',       'Doe',  'mainstreet', 'New York', 'yes' FROM DUAL UNION ALL
SELECT 'John',       'Doe',  'street',     'New York', 'no'  FROM DUAL UNION ALL
SELECT 'Doe',        'John', 'mainstreet', 'New York', 'yes' FROM DUAL UNION ALL
SELECT 'mainstreet', 'John', 'doe',        'New York', 'yes' FROM DUAL;

У вас должна быть возможность создать многостолбцовый индекс Oracle Text:

BEGIN
  ctx_ddl.create_preference('person_lexer', 'BASIC_LEXER');  
  ctx_ddl.set_attribute('person_lexer', 'mixed_case', 'NO'); 

  ctx_ddl.create_preference('person_datastore', 'MULTI_COLUMN_DATASTORE'); 
  ctx_ddl.set_attribute(
    'person_datastore',
    'columns',
    'fname, lname, street, city'
  ); 
END;
/

CREATE INDEX person_idx
  ON person (fname) INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('datastore person_datastore lexer person_lexer');

Затем используйте запрос:

SELECT *
FROM   person
WHERE  CONTAINS(fname, 'mainstreet AND John AND Doe') > 1

или:

SELECT *
FROM   person
WHERE  CONTAINS(
         fname,
         REPLACE('mainstreet John Doe', ' ', ' AND ')
       ) > 1;

или

SELECT *
FROM   person
WHERE  CONTAINS(
         fname,
         REGEXP_REPLACE('mainstreet John Doe', '\s+', ' AND ')
       ) > 1;

Что все выводит:

Вы можете разбить строку поиска и выполнить поиск по объединенным столбцам для каждого из них:

select *
  from (select p.*,
               '|'||fname||'|'||lname||'|'||street||'|'||city||'|' searchlist
          from person)
 where searchlist like '%|mainstreet|%
   and searchlist like '%|John|%
   and searchlist like '%|Doe|%

Он не будет хорошо работать с большими объемами (поскольку не сможет использовать индекс), но при достаточно скромном размере таблицы будет проще всего.

Если вам нужна скорость при большом объеме, лучше всего создать дочернюю таблицу с каждым словом (fname, lname, street, city и т. д.), которую затем можно будет нормально индексировать и выполнять поиск с предикатом равенства.