Oracle: полнотекстовый поиск с условием

Текст Oracle

1. Вы можете повысить производительность, создав индекс CONTEXT с помощью ФИЛЬТР ПО:

create index my_idx on my_table(text) indextype is ctxsys.context filter by group_id;

В моих тестах filter by определенно улучшил производительность, но все же было немного быстрее, если просто использовать индекс btree для group_id.

2. В индексах CTXCAT используются «подиндексы», и похоже, что они работают так же, как и индексы с несколькими столбцами. Кажется, это вариант (4), который вы ищете:

begin
  ctx_ddl.create_index_set('my_table_index_set');
  ctx_ddl.add_index('my_table_index_set', 'group_id');
end;
/

create index my_idx2 on my_table(text) indextype is ctxsys.ctxcat
    parameters('index set my_table_index_set');

select * from my_table where catsearch(text, 'blah', 'group_id = 43') > 0

Это, вероятно, самый быстрый способ. Использование приведенного выше запроса к 120 МБ случайного текста, аналогичного вашему сценарию A и B, потребовало только 18 согласованных операций получения. Но с другой стороны, создание индекса CTXCAT заняло почти 11 минут и использовало 1,8 ГБ места.

(Примечание: кажется, что Oracle Text здесь работает правильно, но я не знаком с Text и не могу гарантировать, что это не ненадлежащее использование этих индексов, как сказал @NullUserException.)

Многостолбцовые индексы и объединение индексов

Для ситуации, которую вы описываете в своем редактировании, обычно не будет существенной разницы между использованием индекса на (A, B) и объединением отдельных индексов на A и B. Я построил несколько тестов с данными, похожими к тому, что вы описали, и для присоединения к индексу требуется только 7 согласованных операций получения по сравнению с 2 согласованными операциями получения для индекса с несколькими столбцами.

Причина этого в том, что Oracle извлекает данные блоками. Блок обычно имеет размер 8 КБ, а индексный блок уже отсортирован, поэтому вы, вероятно, сможете уместить значения от 500 до 2000 в несколько блоков. Если вы беспокоитесь о производительности, обычно имеет значение только ввод-вывод для чтения и записи блоков. Независимо от того, должен ли Oracle объединять несколько тысяч строк, это несущественное количество процессорного времени.

Однако это не относится к индексам Oracle Text. Вы можете соединить индекс CONTEXT с индексом btree ("битмап и"?), но производительность низкая.

Я бы поставил индекс на group_id и посмотрел, достаточно ли этого. Вы не говорите, сколько строк мы говорим или какая производительность вам нужна.

Помните, что порядок, в котором обрабатываются предикаты, не обязательно совпадает с порядком, в котором вы написали их в запросе. Не пытайтесь перехитрить оптимизатора, если у вас нет для этого реальной причины.

Краткая версия: в этом нет необходимости. Оптимизатор запросов достаточно умен, чтобы решить, как лучше выбрать ваши данные. Просто создайте индекс btree для group_id, т.е.:

CREATE INDEX my_group_idx ON my_table (group_id); 

Длинная версия: я создал скрипт (testperf.sql), который вставляет 136 строк фиктивных данных.

DESC my_table;

Name     Null     Type      
-------- -------- --------- 
ID       NOT NULL NUMBER(4) 
GROUP_ID          NUMBER(4) 
TEXT              CLOB      

На group_id есть индекс btree. Чтобы убедиться, что индекс действительно будет использоваться, запустите это как пользователь dba:

EXEC DBMS_STATS.GATHER_TABLE_STATS('<YOUR USER HERE>', 'MY_TABLE', cascade=>TRUE);

Вот сколько строк имеет каждый group_id и соответствующий процент:

GROUP_ID               COUNT                  PCT                    
---------------------- ---------------------- ---------------------- 
1                      1                      1                      
2                      2                      1                      
3                      4                      3                      
4                      8                      6                      
5                      16                     12                     
6                      32                     24                     
7                      64                     47                     
8                      9                      7         

Обратите внимание, что оптимизатор запросов будет использовать индекс только в том случае, если он считает это хорошей идеей, то есть вы извлекаете до определенного процента строк. Итак, если вы спросите у него план запроса на:

SELECT * FROM my_table WHERE group_id = 1;
SELECT * FROM my_table WHERE group_id = 7;

Вы увидите, что для первого запроса будет использоваться индекс, тогда как для второго запроса будет выполнено полное сканирование таблицы, так как имеется слишком много строк для эффективного индекса при group_id = 7.

Теперь рассмотрим другое условие - WHERE group_id = Y AND text LIKE '%blah%' (поскольку я не очень знаком с ctxsys.context).

SELECT * FROM my_table WHERE group_id = 1 AND text LIKE '%ipsum%';

Взглянув на план запроса, вы увидите, что он будет использовать индекс для group_id. Обратите внимание, что порядок ваших условий не важен:

SELECT * FROM my_table WHERE text LIKE '%ipsum%' AND group_id = 1;

Создает тот же план запроса. И если вы попытаетесь запустить тот же запрос на group_id = 7, вы увидите, что он возвращается к полному сканированию таблицы:

SELECT * FROM my_table WHERE group_id = 7 AND text LIKE '%ipsum%';

Обратите внимание, что Oracle автоматически собирает статистику каждый день (она запланирована каждую ночь и по выходным), чтобы постоянно повышать эффективность оптимизатора запросов. Короче говоря, Oracle делает все возможное для оптимизации оптимизатора, так что вам это не нужно.

У меня нет под рукой экземпляра Oracle для тестирования, и я не использовал полнотекстовое индексирование в Oracle, но в целом у меня была хорошая производительность с встроенными представлениями, которые могут быть альтернативой сортировке индекса, который вы имели в виду. Допустим ли следующий синтаксис, когда используется contains()?

Это встроенное представление дает вам значения PK строк в группе 43:

             (
             select T.pkcol
             from T
             where group = 43
             )

Если группа имеет нормальный индекс и не имеет низкой кардинальности, получение этого набора должно быть быстрым. Затем вы снова соедините этот набор с T:

           select * from T
           inner join
            (
             select T.pkcol
             from T
             where group = 43
             ) as MyGroup

           on T.pkcol = MyGroup.pkcol
           where contains(text, '%blah%') > 0

Будем надеяться, что оптимизатор сможет использовать индекс PK для оптимизации соединения, а затем применить предикат contains только к строкам группы 43.