Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Добрый день!

БД на Oracle 19.5.
Есть таблица (50 миллионов строк), в которой нужно производить поиск подстроки (значения задаются пользователем) по нескольким текстовым полям (name1, name2, name3), например:

select *
from my_table br
where br.name1 like '%12 34 567890%'
   or br.name2 like '%12 34 567890%'
   or br.name3 like '%12 34 567890%'


Поиск работает, но медленно. IN MEMORY не помогло (ускорило раза в 2 всего). Обычные индексы тут не помогут из-за левого %, поэтому были созданы текстовые индексы на каждое из полей поиска (name1, name2, name3).

Соответственно, запрос стал выглядеть следующим образом:
select *
from my_table br
where CONTAINS(br.name1, '%12 34 567890%') > 0
   or CONTAINS(br.name2, '%12 34 567890%') > 0
   or CONTAINS(br.name3, '%12 34 567890%') > 0


Вроде бы проблема решилась, но недавно оказалось, что в текстовых индексах есть ограничения на повторяющиеся значения, и при поиске некоторых значений, например:
select *
from my_table br
where CONTAINS(br.name1, '%34 57 123890%') > 0


возникает ошибка:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms


Пробовали увеличивать параметр MAX_TERMS с 2000 до 50000, но не помогло, пересоздавали индекс так:
DECLARE
  PROCEDURE TryDropPreference(pPreference IN VARCHAR2)
  IS
  BEGIN
    FOR rec IN 
    (
      SELECT *
        FROM ctx_user_preferences p
       WHERE p.pre_name = UPPER(pPreference)
    )
    LOOP
      ctx_ddl.drop_preference(pPreference);
    END LOOP;
  END;
  
  PROCEDURE AddCtxIndex(pIndex IN varchar2, pTable IN VARCHAR2, pColumn IN VARCHAR2, pOnCommit IN VARCHAR2)
  IS
    lCnt NUMBER;
    lCommand VARCHAR2(4000 CHAR);
    lPref VARCHAR2(1000 CHAR);
    lLex VARCHAR2(1000 CHAR);
    lStore VARCHAR2(1000 CHAR);
  BEGIN
    SELECT COUNT(1)
      INTO lCnt
      FROM user_indexes i
     WHERE i.table_name = pTable
       AND i.index_name = pIndex
       AND i.index_type = 'DOMAIN';

    IF lCnt = 0 THEN
      lPref := 'PREF_'||pTable||'_'||pColumn;
      
      TryDropPreference(lPref);

      ctx_ddl.create_preference(lPref, 'BASIC_WORDLIST');
      ctx_ddl.set_attribute(lPref,'PREFIX_INDEX','TRUE');
      ctx_ddl.set_attribute(lPref,'SUBSTRING_INDEX', 'YES');
      ctx_ddl.set_attribute(lPref, 'WILDCARD_MAXTERMS', 50000);

      lLex := 'LEX_'||pTable||'_'||pColumn;
      
      TryDropPreference(lLex);

      ctx_ddl.create_preference(lLex,'BASIC_LEXER');
      ctx_ddl.set_attribute(lLex,'PRINTJOINS','_-''"$');

      lStore := 'PREF_STORE_'||pTable||'_'||pColumn;
      
      TryDropPreference(lStore);

      ctx_ddl.create_preference(lStore,'BASIC_STORAGE');
      ctx_ddl.set_attribute(lStore,'I_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'K_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'R_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'N_TABLE_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');
      ctx_ddl.set_attribute(lStore,'I_INDEX_CLAUSE','tablespace TIMELINE_EXCHANGE storage (initial 1M)');

      lCommand := '
      create index '||pIndex||' on '||pTable||' ('||pColumn||') indextype is ctxsys.context PARAMETERS (''lexer '||lLex
      ||' wordlist '||lPref||' storage '||lStore||' memory 200M '||pOnCommit||''')';

      EXECUTE IMMEDIATE lCommand;
      dbms_output.put_line(lCommand);
    END IF;
  END;
  
 
BEGIN
  AddCtxIndex('MY_TABLE_CTX','MY_TABLE','NAME1','');
END;
/


Подскажите, пожалуйста. Может быть я как-то неправильно создаю индексы? Может у кого-нибудь есть опыт использования текстовых индексов для решения подобных проблем?

Или может реализовывали создание своих поисковых систем? Я уже думаю над созданием отдельной таблицы, в которую наинсертить все возможные подстроки, создать на нее индекс и искать в ней по like только с правым %, т.е. например, для значения "12 34 567890" во вспомогательную таблицу наинсертятся значения:
12 34 567890
2 34 567890
34 567890
4 567890
567890
67890
7890
890
90
0

Только вот пока не очень представляю, какие сложности могут возникнуть над сопровождением такой таблицы.
18 фев 21, 18:02    [22283018]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5785
Андрей_7777,

CONTAINS(br.name1, '%34 57 123890%') > 0

Вам надо почитать про операторы contains. То что вы вводите ищет на самом деле: name like '%34' or name like '57' or name like 123890%'
18 фев 21, 18:34    [22283039]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54875
Андрей_7777
Или может реализовывали создание своих поисковых систем? Я уже думаю над созданием отдельной таблицы, в которую наинсертить все возможные подстроки, создать на нее индекс и искать в ней по like только с правым %, т.е. например, для значения "12 34 567890" во вспомогательную таблицу наинсертятся значения:
12 34 567890
2 34 567890
34 567890
4 567890
567890
67890
7890
890
90
0

Только вот пока не очень представляю, какие сложности могут возникнуть над сопровождением такой таблицы.
для начала - вы ее размеры примерно вычислили?
18 фев 21, 18:41    [22283042]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5785
Помимо этого, вас надо ещё прочитать про то как добавить пробел в "токены" (printjoins, skipjoins)
18 фев 21, 18:41    [22283043]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
miksoft
Member

Откуда:
Сообщений: 38702
Андрей_7777
по нескольким текстовым полям (name1, name2, name3)
А что это за поля? они поддаются нормализации?
18 фев 21, 20:42    [22283096]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Правильный Вася
Member

Откуда:
Сообщений: 398
miksoft
Андрей_7777
по нескольким текстовым полям (name1, name2, name3)
А что это за поля? они поддаются нормализации?

Шаблон поиска очень похож на номер паспорта.
Наверняка персональные данные длинной строкой.
19 фев 21, 01:31    [22283213]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Sayan Malakshinov
Помимо этого, вас надо ещё прочитать про то как добавить пробел в "токены" (printjoins, skipjoins)


Sayan Malakshinov, cпасибо большое.

Попробовал добавить пробел в токен следующим образом (перед символом _ указал пробел):
      ctx_ddl.create_preference(lLex,'BASIC_LEXER');
      ctx_ddl.set_attribute(lLex,'PRINTJOINS',' _-''"$');

Но это не помогло, проблемный select заваливается с той же ошибкой.

Также пробовал исключать пробел:
      ctx_ddl.create_preference(lLex,'BASIC_LEXER');
      ctx_ddl.set_attribute(lLex,'PRINTJOINS','_-''"$');
      ctx_ddl.set_attribute(lLex,'SKIPJOINS',' ');
;


Тоже не помогло, видимо из-за этого:
whitespace
Specify the characters that are treated as blank spaces between tokens.
BASIC_LEXER uses whitespace characters in conjunction with punctuations and newline characters to identify character strings that serve as sentence delimiters for sentence and paragraph searching.
The predefined default values for whitespace are space and tab. These values cannot be changed. Specifying characters as whitespace characters adds to these defaults.


Не знаете, как сделать, чтобы пробел обрабатывался, как обычный символ?

Сообщение было отредактировано: 19 фев 21, 06:54
19 фев 21, 06:57    [22283232]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
andreymx
Андрей_7777
Или может реализовывали создание своих поисковых систем? Я уже думаю над созданием отдельной таблицы, в которую наинсертить все возможные подстроки, создать на нее индекс и искать в ней по like только с правым %, т.е. например, для значения "12 34 567890" во вспомогательную таблицу наинсертятся значения:
12 34 567890
2 34 567890
34 567890
4 567890
567890
67890
7890
890
90
0

Только вот пока не очень представляю, какие сложности могут возникнуть над сопровождением такой таблицы.
для начала - вы ее размеры примерно вычислили?


andreymx,

Количество строк в ней примерно вычислил, несколько миллиардов получается, это не очень большой размер для нашей БД.
Я понимаю, что это кривое решение, просто рассматриваю все варианты.

Сообщение было отредактировано: 19 фев 21, 06:55
19 фев 21, 06:59    [22283233]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Правильный Вася
miksoft
пропущено...
А что это за поля? они поддаются нормализации?

Шаблон поиска очень похож на номер паспорта.
Наверняка персональные данные длинной строкой.


Все верно, это шаблон паспорта, в других полях он тоже может быть. Поля нормализации не поддаются.
19 фев 21, 07:03    [22283234]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
[quot Андрей_7777#22283232]
Sayan Malakshinov

...
Не знаете, как сделать, чтобы пробел обрабатывался, как обычный символ?


Или м.б. создать в таблице дополнительные поля, в которых будут храниться значения без пробелов, и создать на них текстовые индексы?
19 фев 21, 08:36    [22283244]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
miksoft
Member

Откуда:
Сообщений: 38702
Андрей_7777,

А таблица сильно широкая? Какая средняя длина записи?
19 фев 21, 12:50    [22283412]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
miksoft
Андрей_7777,

А таблица сильно широкая? Какая средняя длина записи?


Средняя длина поля 20 символов
19 фев 21, 14:13    [22283503]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 52504

Андрей_7777
Все верно, это шаблон паспорта

Тогда откуда приходит этот шаблон, что он проверяется на вхождение, а не на полное
совпадение? Типа "я помню только три цифры в середине, остальное забыл"?..

Posted via ActualForum NNTP Server 1.5

19 фев 21, 14:24    [22283508]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
123йй
Member

Откуда:
Сообщений: 1664
Dimitry Sibiryakov,

очень похоже что у ТС сделаны 3 поля как Документ1,Документ2,Документ3.
типа загран, паспорт и водительское.
в какое поле, что попадет никто не знает.
вот он и пытается это решить :)
19 фев 21, 14:59    [22283525]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 52504
Вопрос-то не в этом. Какой документ ни возьми, при поиске по его номеру его (номер) всегда
набирают с самой первой цифры, никто не вырывает пару из середины. Поэтому непонятно
почему у него лайк с двумя процентами.


PS: Единственная не слишком безумная причина для такого - нарушение первой НФ, в поле записаны несколько номеров через запятую.

Сообщение было отредактировано: 19 фев 21, 14:56
19 фев 21, 15:01    [22283528]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Sayan Malakshinov
Member

Откуда: Мск
Сообщений: 5785
Dimitry Sibiryakov
при поиске по его номеру его (номер) всегда
набирают с самой первой цифры, никто не вырывает пару из середины.
не говоря уже о пробелах внутри номера...
19 фев 21, 15:03    [22283529]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
123йй
Member

Откуда:
Сообщений: 1664
"Паспорт РФ 1234 567890 выдан...."
возможно
19 фев 21, 15:57    [22283560]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 52504

123йй
возможно

В этом случае топикстартер врёт и для нормализации ещё поле непаханное.

Posted via ActualForum NNTP Server 1.5

19 фев 21, 16:15    [22283575]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10125
Dimitry Sibiryakov
Вопрос-то не в этом. Какой документ ни возьми, при поиске по его номеру его (номер) всегда
набирают с самой первой цифры, никто не вырывает пару из середины.


Не забываем про силовые органы: Свидетель запомнил несколько цифр из середины номера машины :).

SY.
19 фев 21, 17:03    [22283620]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Dimitry Sibiryakov
Вопрос-то не в этом. Какой документ ни возьми, при поиске по его номеру его (номер) всегда
набирают с самой первой цифры, никто не вырывает пару из середины. Поэтому непонятно
почему у него лайк с двумя процентами.


PS: Единственная не слишком безумная причина для такого - нарушение первой НФ, в поле записаны несколько номеров через запятую.


Да, вы правы, нарушение первой НФ действительно есть.
Но тому есть веские причины - данные приходят из разных источников, в каждом источнике много разных форматов данных, в том числе и не известных заказчику. Форматы могут обновляться, а также добавляться новые (без предупреждения), а также, нередко, человеческий фактор оказывает воздействие.
Заказчик может слабо влиять на форматы предоставляемых ему данных. Поэтому и выбран такой способ хранения данных. Особо это нигде не аукается, а также у такого способа есть свои плюсы.
Я сам перфекционист по натуре и стараюсь все делать оптимально, но, есть теория, а есть жизнь, в которой не всегда возможно сделать все идеально.
19 фев 21, 18:02    [22283670]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 52504

Андрей_7777
Но тому есть веские причины - данные приходят из разных источников, в каждом источнике
много разных форматов данных, в том числе и не известных заказчику. Форматы могут
обновляться, а также добавляться новые (без предупреждения), а также, нередко,
человеческий фактор оказывает воздействие.

Весь список состоит из одного "лень". Проблема в том, что при таких условиях и ваш поиск с
фиксированным форматом не будет работать.

Posted via ActualForum NNTP Server 1.5

19 фев 21, 18:10    [22283676]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Dimitry Sibiryakov

Андрей_7777
Но тому есть веские причины - данные приходят из разных источников, в каждом источнике
много разных форматов данных, в том числе и не известных заказчику. Форматы могут
обновляться, а также добавляться новые (без предупреждения), а также, нередко,
человеческий фактор оказывает воздействие.

Весь список состоит из одного "лень". Проблема в том, что при таких условиях и ваш поиск с
фиксированным форматом не будет работать.


Весь список состоит из одного "лень".

Это не лень, просто заказчика устраивает текущее положение дел и он не хочет заниматься согласовыванием многочисленных форматов.

Проблема в том, что при таких условиях и ваш поиск с фиксированным форматом не будет работать.

Не совсем понял вас. Мне нужно всего лишь найти строки с данной подстрокой. Текстовый индекс для этого отлично подходит. Проблема только в том, что пробелы разделяют токены, а мне надо, чтобы они считались частью слов, но на сколько я понял из чтения документации это невозможно. Поэтому я хочу создать вспомогательные столбцы, в которых будут храниться значения без пробелов, и навесить на них текстовые индексы. По таким индексам будут находиться нужные мне значения (искать надо будет тоже без пробелов). Единственное, мне не нравится, то что надо создавать дополнительные столбцы, в идеале хотелось бы обойтись без них, но не знаю как это сделать, текстовый индекс по функции, например, replace(name, ' ') создать нельзя.
19 фев 21, 18:30    [22283688]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 919
Андрей_7777
, текстовый индекс по функции, например, replace(name, ' ') создать нельзя.
виртуальной колонкой обмануть не пробовали?
19 фев 21, 18:42    [22283699]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 9443
А мне вообще смысл "обмануть" не понятен.
Ну обманит его топик стартер, получит просто замену обычному индексу.

IMHO

Сообщение было отредактировано: 19 фев 21, 18:37
19 фев 21, 18:43    [22283701]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
mayton
Member

Откуда: loopback
Сообщений: 51160
Алгоритмически, я-бы свёл задачу к поиску документов с 3 токенами.

Тоесть вот такое

select *
from my_table br
where CONTAINS(br.name1, '%12 34 567890%') > 0
   or CONTAINS(br.name2, '%12 34 567890%') > 0
   or CONTAINS(br.name3, '%12 34 567890%') > 0


Заменить на
select *
from my_table br
where CONTAINS(br.name1, '12 AND 34 AND 567890') .... e.t.c.


А уже из оставшейся выборки добить обычным фильтром композицию этих токенов в правильном порядке.
19 фев 21, 18:52    [22283706]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить