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

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

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

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

Posted via ActualForum NNTP Server 1.5

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

Откуда: loopback
Сообщений: 51160
Сомневаюсь что это вообще возможно. Текстовые индексы обычно не отсекают ветви поиска как это делают 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

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

Откуда: Мск
Сообщений: 5785
Андрей_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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Oracle Ответить