Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Текстовые индексы. Быстрый поиск вида 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

Откуда: Мск
Сообщений: 5780
Андрей_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

Откуда: Запорожье
Сообщений: 54831
Андрей_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

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

Откуда:
Сообщений: 38693
Андрей_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

Откуда:
Сообщений: 38693
Андрей_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

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

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

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

Posted via ActualForum NNTP Server 1.5

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

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

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

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


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

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

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

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

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

123йй
возможно

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

Posted via ActualForum NNTP Server 1.5

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

Откуда: Middlebury, CT USA
Сообщений: 10121
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

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

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

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

IMHO

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

Откуда: loopback
Сообщений: 51144
Алгоритмически, я-бы свёл задачу к поиску документов с 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]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Dimitry Sibiryakov
Member

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

mayton
я-бы свёл задачу к поиску документов с 3 токенами.

Аффтару это не нужно, ему всего лишь хочется чтобы поиск по вхождению подстроки в поле
выполнялся быстрее, чем full table scan.

Posted via ActualForum NNTP Server 1.5

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

Откуда: loopback
Сообщений: 51144
Сомневаюсь что это вообще возможно. Текстовые индексы обычно не отсекают ветви поиска как это делают B+Tree,
Они просто делают full-index-scan максимально дешевым. Но это моё предположение. На самом деле я не знаю
как устроен CTX_SYS внутри.
19 фев 21, 19:21    [22283722]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
кит северных морей
Андрей_7777
, текстовый индекс по функции, например, replace(name, ' ') создать нельзя.
виртуальной колонкой обмануть не пробовали?


Не получилось:
DRG-11304: function-based indexes are not supported by this indextype
19 фев 21, 19:36    [22283732]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
flop01
Member

Откуда:
Сообщений: 3
Можно поиграться с user_lexer и user_filter (procedure_filter)
Зачем нужны % c обоих сторон в contains?
21 фев 21, 03:47    [22284329]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
flop01
Member

Откуда:
Сообщений: 3
Узрел еще, что индексов 3 шт.
Я бы тогда сделал один индекс: user_datastore + section_group
В процедуре для user_datastore формировал типа такого:
NAME1 NAME2 NAME3
11 22 123456; 33 44 567890 25 МЮ 01725 null

<DOC>
 <NAME1>1122123456 123456 3344567890 567890</NAME1>
 <NAME2>25МЮ01725 01725</NAME2>
 <NAME3></NAME3>
</DOC>

И искал бы без %
21 фев 21, 13:54    [22284391]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Sayan Malakshinov
Member

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

раз уж хотите возиться с таким "garbage in, garbage out", то можно сделать простой препроцессинг:
+ убираем пробелы и конкатенируем все три поля
drop table my_table purge;
create table my_table
as
select 
  rownum as id
 ,'y' as TEXT_INDEX_ME -- опционально - индексируем только где TEXT_INDEX_ME = 'y'
 ,decode(mod(rownum,3),0,column_value, 'name1') as name1
 ,decode(mod(rownum,3),1,column_value, 'name2') as name2
 ,decode(mod(rownum,3),2,column_value, 'name3') as name3
from sys.odcivarchar2list(
 'blabla 12 34 5678A0A' 
,'blabla 12 34 567890x' 
,'blabla 12 34 567890y' 
,'blabla 12 34 567890z' 
,'blabla 12 34 567890 xyz'
,'blabla 12 34 567891 A'
,'blabla 34 57 12389A'
,'blabla 34 57 123890'
,'blabla 34 57 123890x'
,'blabla 34 57 123890y'
);

create or replace procedure TEXT_IDX_BUILDER(rid in rowid, tlob in out nocopy varchar2) is
begin
  for c1 in ( select t.name1,t.name2,t.name3
              from my_table t
              where rowid = rid 
              and t.TEXT_INDEX_ME='y' -- optional - index only when TEXT_INDEX_ME = 'y'
  )
  loop
      -- concat name1,name2,name3:
      tlob:=tlob||c1.name1||';'||c1.name2||';'||c1.name3;
      -- remove whitespaces:
      tlob:=replace(tlob,' ');
  end loop;
EXCEPTION
  WHEN OTHERS THEN
  NULL;
end TEXT_IDX_BUILDER;
/
begin
  begin
    ctx_ddl.drop_preference('MY_TEXT_DATASTORE');
  exception when others then null;
  end;
  ctx_ddl.create_preference('MY_TEXT_DATASTORE', 'user_datastore'); 
  ctx_ddl.set_attribute('MY_TEXT_DATASTORE', 'procedure', 'TEXT_IDX_BUILDER'); 
  ctx_ddl.set_attribute('MY_TEXT_DATASTORE', 'output_type', 'VARCHAR2'); 
end;
/
CREATE INDEX my_table_text_idx_1 ON my_table(name1) 
INDEXTYPE IS "CTXSYS"."CONTEXT" 
PARAMETERS('Datastore MY_TEXT_DATASTORE 
            SYNC (EVERY "sysdate+((1/24/60)*5)") 
            TRANSACTIONAL
            ')
parallel 2;
/
+ результаты
SQL> select * from my_table br;

        ID T NAME1                          NAME2                          NAME3
---------- - ------------------------------ ------------------------------ ------------------------------
         1 y name1                          blabla 12 34 5678A0A           name3
         2 y name1                          name2                          blabla 12 34 567890x
         3 y blabla 12 34 567890y           name2                          name3
         4 y name1                          blabla 12 34 567890z           name3
         5 y name1                          name2                          blabla 12 34 567890 xyz
         6 y blabla 12 34 567891 A          name2                          name3
         7 y name1                          blabla 34 57 12389A            name3
         8 y name1                          name2                          blabla 34 57 123890
         9 y blabla 34 57 123890x           name2                          name3
        10 y name1                          blabla 34 57 123890y           name3

10 rows selected.

SQL> select * from my_table br where CONTAINS(br.name1, '%1234567890%') > 0;
        ID T NAME1                          NAME2                          NAME3
---------- - ------------------------------ ------------------------------ ------------------------------
         2 y name1                          name2                          blabla 12 34 567890x
         3 y blabla 12 34 567890y           name2                          name3
         4 y name1                          blabla 12 34 567890z           name3
         5 y name1                          name2                          blabla 12 34 567890 xyz

SQL> select token_text from DR$MY_TABLE_TEXT_IDX_1$I;

TOKEN_TEXT
------------------------------
BLABLA1234567890X
BLABLA1234567890XYZ
BLABLA1234567890Y
BLABLA1234567890Z
BLABLA1234567891A
BLABLA12345678A0A
BLABLA3457123890
BLABLA3457123890X
BLABLA3457123890Y
BLABLA345712389A
NAME1
NAME2
NAME3

13 rows selected.
22 фев 21, 16:37    [22284792]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Sayan Malakshinov,

то что надо, спасибо большое.
23 фев 21, 00:03    [22284987]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Sayan Malakshinov
Member

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

расширенный пример:
+
drop index my_table_text_idx_1;
drop table my_table purge;
create table my_table
as
select 
  rownum as id
 ,'y' as TEXT_INDEX_ME -- опционально - индексируем только где TEXT_INDEX_ME = 'y'
 ,decode(mod(rownum,3),0,column_value, 'name1') as name1
 ,decode(mod(rownum,3),1,column_value, 'name2') as name2
 ,decode(mod(rownum,3),2,column_value, 'name3') as name3
from sys.odcivarchar2list(
 'blabla 12 34 5678A0A' 
,'blabla 12 34 567890x' 
,'blabla 12 34 567890y' 
,'blabla 12 34 567890z' 
,'blabla 12 34 567890 xyz'
,'blabla 12 34 567891 A'
,'blabla 34 57 12389A'
,'blabla 34 57 123890'
,'blabla 34 57 123890x'
,'blabla 34 57 123890y'
);

create or replace procedure TEXT_IDX_BUILDER(rid in rowid, tlob in out nocopy varchar2) is
begin
  for c1 in ( select t.name1,t.name2,t.name3
              from my_table t
              where rowid = rid 
              and t.TEXT_INDEX_ME='y' -- optional - index only when TEXT_INDEX_ME = 'y'
  )
  loop
      -- concat name1,name2,name3:
      tlob:=tlob||c1.name1||';'||c1.name2||';'||c1.name3;
      -- remove whitespaces:
      tlob:=replace(tlob,' ');
  end loop;
EXCEPTION
  WHEN OTHERS THEN
  NULL;
end TEXT_IDX_BUILDER;
/
begin
  begin
    ctx_ddl.drop_preference('MY_TEXT_DATASTORE');
  exception when others then null;
  end;
  ctx_ddl.create_preference('MY_TEXT_DATASTORE', 'user_datastore'); 
  ctx_ddl.set_attribute('MY_TEXT_DATASTORE', 'procedure', 'TEXT_IDX_BUILDER'); 
  ctx_ddl.set_attribute('MY_TEXT_DATASTORE', 'output_type', 'VARCHAR2'); 
end;
/
begin
  begin
    ctx_ddl.drop_preference('my_wordlist');
  exception when others then null;
  end;
    ctx_ddl.create_preference('my_wordlist', 'BASIC_WORDLIST');
    ctx_ddl.set_attribute('my_wordlist','PREFIX_INDEX','TRUE');
    ctx_ddl.set_attribute('my_wordlist','PREFIX_MIN_LENGTH',3);
    ctx_ddl.set_attribute('my_wordlist','PREFIX_MAX_LENGTH', 4);
    ctx_ddl.set_attribute('my_wordlist','SUBSTRING_INDEX','TRUE');
    ctx_ddl.set_attribute('my_wordlist','STEMMER','ENGLISH');
end;
/
CREATE INDEX my_table_text_idx_1 ON my_table(name1) 
INDEXTYPE IS "CTXSYS"."CONTEXT" 
PARAMETERS('Datastore MY_TEXT_DATASTORE 
            wordlist my_wordlist
            SYNC (EVERY "sysdate+((1/24/60)*5)") 
            TRANSACTIONAL
            ')
parallel 2
/
col name1 for a30;
col name2 for a30;
col name3 for a30;
select * from my_table br where CONTAINS(br.name1, '%1234567890%') > 0;
select token_text from DR$MY_TABLE_TEXT_IDX_1$I;

col PAT_PART1 for a20;
col PAT_PART2 for a20;
select * from DR$MY_TABLE_TEXT_IDX_1$P;


SQL> select * from DR$MY_TABLE_TEXT_IDX_1$P;

PAT_PART1            PAT_PART2
-------------------- --------------------
BLABLA123456789      0XYZ
BLABLA               1234567890X
BLABLA               1234567890XYZ
BLABLA               1234567890Y
BLABLA               1234567890Z
BLABLA               1234567891A
BLABLA               12345678A0A
BLABLA3457           123890
BLABLA3457           123890X
BLABLA3457           123890Y
BLABLA3457           12389A
... <skipped> ...
B                    LABLA3457123890Y
B                    LABLA345712389A
NA                   ME1
NA                   ME2
NA                   ME3
                     NAME1
                     NAME2
                     NAME3
BLABLA1234567890     XYZ

159 rows selected.
т.е. это, конечно, будет быстро, но места сожрет оочень много...
Я бы лучше разгреб этот мусор и, как минимум, сконкатенировал всякие номера в слова, чтобы избежать % в начале поисковой строки - сразу кучу проблем избежите: и с пробелами, и с substring_index
23 фев 21, 01:58    [22285008]     Ответить | Цитировать Сообщить модератору
 Re: Текстовые индексы. Быстрый поиск вида like '%&%'  [new]
Андрей_7777
Member

Откуда:
Сообщений: 28
Sayan Malakshinov,

спасибо, но разгрести мусор не получится.

Около 100 Гб займут текстовые индексы по данной таблице, это совсем не много по меркам заказчика, т.к. на текущий момент БД занимает 15 ТБ и в будущем сильно увеличится.

Заказчика гораздо больше волнует время выполнения запросов и время перестроения индексов после добавления/изменения данных.
24 фев 21, 13:35    [22285654]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить