Как сделать отбор по вхождению части строки с разделителями в строку с разделителями?

Question

[KTG]

Имеется таблица, назовем её "data_table":
ID | GROUPS
-----------------
01 | 'e;z'
02 | 'a;b'
03 | 'o;p'
04 | 'o;p;z'
05 | 'd;f'

И есть список значений,
a, b, c, d, z.
Который в запросе можно представить в виде:
select 'a;b;c;d;z' from dual;
или
select 'a','b','c','d','z' from dual;
По факту, список значений намного шире и может постоянно меняться. Количество строк в таблице то же великое множество.

Необходимо вернуть все строки из таблицы 'data_table', у которых в поле 'groups' встречается хотя бы одно значение из списка.
Т.е. к возврату, из данного примера, подлежат строки:
Result
----------
01 | 'e;z' -- т.к. в списке есть 'z'
02 | 'a;b' -- т.к. в списке есть 'a' и 'b'
04 | 'o;p;z' -- т.к. в списке есть 'z'
05 | 'b;f' -- т.к. в списке есть 'b'

Возможно ли подобное написать одним запросом (использовать PL/SQL стандартные функции допускается)?

UPD: вариант создать таблицу под список значений, где каждое значения - строка и сопоставлять по вхождению имеет право на жизнь, но не рассматривается.
Список значений можно вынести во временную таблицу конструкцией WITH, но пока не представляю как развернуть неопределенное количество столбцов в строки. Неопределенное, потому что мысль использовать unpivot или union с перечислением 100-1000 значений погружает в уныние.

Answer 1

[KTG]

Буду использовать список значений в виде строки с разделителем.
Разобью её на табличные строки:

WITH
    val_list# AS
    (   
        SELECT 'a;b;c;d;z' AS lst FROM dual
    )

SELECT
      REGEXP_SUBSTR(lst, '[^;]+', 1, level) AS val
    FROM
        val_list#
    CONNECT BY level <= REGEXP_COUNT(lst, ';')+1

Дальше просто буду каждое значение из таблицы сопоставлять по вхождению в поле 'groups' таблицы 'data_table'.

Правда пока не понятно что лучше, в плане производительности.
Виртуально плодить строки в таблице 'data_table' разбивая так же на каждое найденное значение в поле 'groups'.
Или к каждой строке подключать таблицу значений.
Оба варианта пока смущают, а третий вариант еще не придумал...

Хотя было бы неплохо брать 1-ое значение, прогонять по таблице, при найденном совпадении неким образом помечать/отбрасывать строки, а уже по 2-му значению прогонять только оставшиеся и т.д.
Но как?

to be continued

Comments

[KTG]

SELECT 
      id
    , groups
    FROM data_table dt
        INNER JOIN val_table# vt# ON INSTR(dt.groups, vt#.val) != 0   
    GROUP BY id, groups

И

SELECT 
      id
    , groups
    FROM val_table# vt#
        INNER JOIN data_table dt ON groups LIKE '%'||vt#.val||'%'
    GROUP BY id, groups

можно еще так, но думаю тяжелее:

SELECT 
      id
    , groups
     FROM data_table dt
     HAVING (SELECT COUNT(vt#.val) FROM val_table# vt# WHERE groups LIKE '%'||vt#.val||'%' ) > 0
     GROUP BY id, groups

Порядок подключения таблиц в первых 2-х вариантах на план выполнения запроса не повлиял, но думаю все таки буду пользоваться исходя из размеров: к большой (data_table) подключаем маленькую (val_table#)

Answer 2

[d-stream]

Дубовый вариант - банальный like.
Нормальный - все-таки хранить данные правильно.

Comments

[KTG]

что имелось ввиду под "банальный like" - не понятно, конечное сопоставление? Или изначально всё решение задачи?

Хранить данные правильно не получится - сторонний разработчик ПО, который вряд ли будет менять логику своего продукта, в том числе и структуру БД.
Да и никто не отменял PL/SQL блоки или ЯП на уровне приложения, что бы сделать отбор.

[d-stream]

что имелось ввиду под "банальный like" - не понятно, конечное сопоставление? Или изначально всё решение задачи?

например вот так:
...where groups like '%условие1%' or groups like '%условие2%' or groups like '%условие3%'

Хранить данные правильно не получится - сторонний разработчик ПО, который вряд ли будет менять логику своего продукта, в том числе и структуру БД.
Да и никто не отменял PL/SQL блоки или ЯП на уровне приложения, что бы сделать отбор.

можно задекларить view который приведет данные к человеческому виду и уже оперировать с этим view - будет по крайней мере комфортнее и скорее всего оптимизатор сможет выдавать этот view быстро.

[KTG]

d-stream, если значений в будущем будет больше? например 1000, "OR" на каждый писать...

А так да, список значений можно хранить в материализованной вьюшке, думаю быстрее будет чем каждый раз раскладывать.
Этот вариант то же был на заметке.

Но мы легких путей не ищем.

Возможно ли подобное написать одним запросом (использовать PL/SQL стандартные функции допускается)?

[d-stream]

KTG,

если значений в будущем будет больше? например 1000, "OR" на каждый писать...

вариантов много - от динамического sql до жестоких join-ов с like

Возможно ли подобное написать одним запросом (использовать PL/SQL стандартные функции допускается)?

Да. Тем же join где условие связи использует like
Из минусов - может оказаться "тяжелым" для планировшика/оптимизатора... но это только в уж очень забубенистых случаях.

Ну и да: всегда любую логику можно привести к одному запросу - вызову процедуры или функции)