Добрый день!
БД на Oracle 19.5.
Есть таблица (50 миллионов строк), в которой нужно производить поиск подстроки (значения задаются пользователем) по нескольким текстовым полям (name1, name2, name3), например:
1.
2.
3.
4.
5.
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).
Соответственно, запрос стал выглядеть следующим образом:
1.
2.
3.
4.
5.
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
Вроде бы проблема решилась, но недавно оказалось, что в текстовых индексах есть ограничения на повторяющиеся значения, и при поиске некоторых значений, например:
1.
2.
3.
select *
from my_table br
where CONTAINS(br.name1, '%34 57 123890%') > 0
возникает ошибка:
1.
2.
3.
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, но не помогло, пересоздавали индекс так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
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
Только вот пока не очень представляю, какие сложности могут возникнуть над сопровождением такой таблицы.
