Планы выполнения Oracle при использовании оператора LIKE с функцией DETERMINISTIC

В приведенном ниже сценарии показаны шаги, которые я использовал для сканирования диапазона индекса в индексе ADDRESSES. Прежде чем вы посмотрите на детали, вы можете просто запустить все это. Если вы не получаете два сканирования диапазона индексов для последних двух запросов, возможно, это разница в наших версиях, настройках и т. д. Я использую 10.2.0.1.0.

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

Это странная проблема, и я не понимаю всего, что здесь происходит. Например, я не знаю, почему use_nl работает, а подсказки индекса — нет.

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

--create tables
create table customers (id number, surname varchar2(100), other varchar2(100));
create table addresses (cust_id number, other varchar2(100));

--create data and indexes
insert into customers select level, 'ASDF'||level, level from dual connect by level <= 1000000;
insert into addresses select level, level from dual connect by level <= 1000000;
create index customers_id on customers(id);
create index addresses_cust_id on addresses(cust_id);
create index customers_special_char_filter on customers(special_char_filter(surname));

--create function
create or replace function special_char_filter(surname in varchar) return varchar2 deterministic is
begin
    return replace(surname, 'bad value!', null);
end;
/

--gather stats
begin
    dbms_stats.gather_table_stats(ownname => user, tabname => 'CUSTOMERS', cascade => true);
    dbms_stats.gather_table_stats(ownname => user, tabname => 'ADDRESSES', cascade => true);
end;
/

set autotrace on;

--Index range scan on CUSTOMERS_SPECIAL_CHAR_FILTER, but full table scan on ADDRESSES
--(0.2 seconds)
SELECT *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('ASDF100000bad value!%');

--This uses the addresses index but it does an index full scan.  Not really what we want.
--I'm not sure why I can't get an index range scan here.
--Various other index hints also failed here.  For example, no_index_ffs won't stop an index full scan.
--(1 second)
SELECT /*+ index(addr addresses_cust_id) */ *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('ASDF100000bad value!%');


--Success!  With this hint both indexes are used and it's super-fast.
--(0.02 seconds)
SELECT /*+ use_nl(cust addr) */ *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('ASDF100000bad value!%');


--But forcing the index won't always be a good idea, for example when the value starts with '%'.
--(1.2 seconds)
SELECT /*+ use_nl(cust addr) */ *
FROM customers cust
JOIN addresses addr ON addr.cust_id = cust.id
WHERE special_char_filter(cust.surname) like special_char_filter('%ASDF100000bad value!%');

В запросе может вообще ничего не быть. Оптимизатор, основанный на затратах, может просто запутаться и подумать, что ПОЛНОЕ СКАНИРОВАНИЕ ТАБЛИЦЫ быстрее. Вы пытались использовать HINT в запросе, заставляя Oracle использовать ваш индекс?

Проблема в том, что Oracle не знает, что вернет «special_char_filter(?)». Если он возвращает «%», то использование индекса будет очень медленным, поскольку все будет совпадать. Если он возвращает «A%», он, вероятно, также будет медленным, поскольку (при условии равного распределения по всем буквам) будет совпадать около 4% строк. Если он вернет «%FRED%», он не вернет много строк, но использование сканирования диапазона индекса будет работать плохо, потому что строки могут находиться в начале, середине или конце индекса, поэтому он должен сделать весь индекс.

Если вы знаете, что special_char_filter всегда будет возвращать строку, в начале которой есть как минимум три «сплошных» символа, то вам, вероятно, повезет больше с

ВЫБЕРИТЕ [...] ОТ клиентов пользовательских адресов JOIN addr ON addr.cust_id = cust.id ГДЕ special_char_filter(cust.surname) как special_char_filter(?) AND substr(special_char_filter(cust.surname),1,3) = substr(special_char_filter (?),1,3)

с ФБР на substr(special_char_filter(cust.surname),1,3)

Хотя, если предварительный расчет результата в java работает, то придерживайтесь его.

Кроме того, я бы, вероятно, посмотрел на Oracle Text для совпадений.