Лучший способ оператора SQL 'LIKE' с большим количеством предложений
Мне нужно извлечь информацию из текстового поля, которое может содержать одно из многих значений. SQL выглядит так:
SELECT fieldname
FROM table
WHERE textfield LIKE '%val1%'
OR textfield LIKE '%val2%'
OR textfield LIKE '%val3%'
.
.
OR textfield LIKE '%valn%'
Мой вопрос: насколько это эффективно, когда количество предложений LIKE приближается к сотне. Есть ли лучший способ сделать это?
Использование ORACLE 11g
Я пытался объединить «%val1%» .. «%valn%» в шаблон регулярного выражения, но он стал только более сложным и нечитаемым.
Это классический пример того, почему regexp_like так полезен. Меньше пикселей, меньше ошибок.
Когда вы говорите «насколько эффективно», вы имеете в виду, как это повлияет на скорость выполнения? Независимо от того, что вы делаете, это будет медленно, потому что для этого LIKE %foo% потребуется полное сканирование таблицы, потому что вы не можете использовать для нее индексы. Неважно, 1 их или 100.
Вам следует пересмотреть свою модель/дизайн данных. Никогда не бывает хорошей ситуации, когда вам приходится анализировать большие текстовые поля в поисках конкретной информации. Разбейте его на отдельные дочерние строки в дочерней таблице, которые затем можно легко проиндексировать.
Ответы 2
Если вы сохраняете искомые значения в столбце val столбца separate_table, вы можете упростить запрос до
SELECT a.fieldname
FROM table a join separate_table s on a.textfield like '%' || s.val || '%'
Эффективность? Ничто не является очень эффективным при таком поиске. Возможно, вы могли бы взглянуть на Oracle Text и посмотреть, какой тип поиска он предлагает.
Если вы ищете слова в каком-то тексте, вы можете использовать Oracle Text для индексации текста и CONTAINS для его фильтрации.
Учитывая примерные данные:
CREATE TABLE table_name ( fieldname, textfield ) AS
SELECT 1, 'This is some text that does not contain your values.' FROM DUAL UNION ALL
SELECT 2, 'This is some text where val1 is contained.' FROM DUAL UNION ALL
SELECT 3, 'This has val2 and val3.' FROM DUAL UNION ALL
SELECT 4, 'This is some text that does not contain your values.' FROM DUAL UNION ALL
SELECT 5, 'This has upper-case VAL1.' FROM DUAL UNION ALL
SELECT 6, 'This has mixed-case VaLn.' FROM DUAL UNION ALL
SELECT 7, 'This has val100.' FROM DUAL;
Затем вы можете создать индекс Oracle Text:
CREATE INDEX table_name__textfield__textidx
ON table_name(textfield) INDEXTYPE IS CTXSYS.CONTEXT;
и используйте запрос:
SELECT fieldname, textfield
FROM table_name
WHERE CONTAINS(textfield, 'val1 OR val2 OR val3 OR valn', 1) > 0
Какие выходы:
Строка, содержащая val100, не соответствует val1, поскольку это разные слова. Если вы хотите сопоставить основу слов, используйте % в фильтре «Содержит»:
SELECT fieldname, textfield
FROM table_name
WHERE CONTAINS(textfield, 'val1% OR val2% OR val3% OR valn%', 1) > 0
Какие выходы:
регулярное выражение может быть более эффективным, чем несколько LIKE. Есть ли у вас полнотекстовый индекс в столбце текстового поля?