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

Ответ 1

Текст Oracle

1 - Вы можете повысить производительность, создав индекс CONTEXT с помощью FILTER BY:

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 МБ случайного текста, подобного вашему сценарию А и В, требуется только 18 последовательных запросов. Но с другой стороны, создание индекса CTXCAT заняло почти 11 минут и использовало 1,8 ГБ пространства.

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

Множественные столбцы и индексы объединяются

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

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

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

Ответ 2

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

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

Ответ 3

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

CREATE INDEX my_group_idx ON my_table (group_id); 

Длинная версия: Я создал script (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 делает все возможное, чтобы оптимизировать оптимизатор, поэтому вам не нужно.

Ответ 4

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

В этом встроенном представлении вы получите значения 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 для оптимизации соединения, а затем применить предикат содержит только для строк группы 43.