использовать переменную привязки с оператором LIKE %

В моем PL/SQL есть этот большой динамический SQL, использующий переменные связывания. Я использую пакет DBMS_SQL для привязки и выполнения сгенерированного запроса.

В зависимости от определенных критериев в динамический SQL добавляются операторы where. Когда я просто использую "in" или "=" для сопоставления моих переменных связывания, все работает нормально, и результаты возвращаются довольно быстро.

Пример:

(servedparty = :bv_ or servedpartyimsi = :bv_)

Однако, когда я делаю следующее:

(servedpartyimei like :bv_)

и укажите значение вроде 12345679890%, запрос занимает очень-очень много времени.

Я также пробовал что-то вроде этого

(servedpartyimei like :bv_||'%')

а затем укажите значение без «%», но это дает те же результаты

Когда я выполняю запрос без переменных связывания, а просто помещаю жестко закодированные значения, результат также возвращается немедленно.

Я делаю что-то не так здесь? Не привязывать переменные, такие как оператор LIKE? Есть идеи?

Спасибо.


oracle plsql bind-variables sql-like
person Tijs    schedule 17.11.2016    source источник
comment
Какой тип данных servedpartyimei? Вы показали значение поиска, которое является числом (что имеет смысл для IMEI, я думаю?), но вы обрабатываете его как строку. Возможно, также стоит указать, какую версию Oracle вы используете; а можно посмотреть планы выполнения для быстрой и медленной версий?   -  person Alex Poole    schedule 17.11.2016
comment
IMEI хранятся в виде строк в базе данных. Версия базы данных 11.2.   -  person Tijs    schedule 17.11.2016
comment
Вам все еще нужно смотреть на планы выполнения, чтобы увидеть, что он делает. Возможно, есть также фильтры для других (индексированных) столбцов, которые он считает более подходящими, или не использует индекс, который вы ожидаете, по какой-то другой причине. Мы не можем догадаться, что он делает, вам нужно это выяснить.   -  person Alex Poole    schedule 17.11.2016
comment
Изучил планы выполнения и они похожи. Когда я запускаю ту же часть PL/SQL в другой среде (проблема возникает в нашем DEV и теперь попробовала ее в TEST), результаты возвращаются немедленно. Так что это, вероятно, будет вещью окружающей среды. Проверим у DBA   -  person Tijs    schedule 18.11.2016

Ответы (3)


arrow_upward
5
arrow_downward

Я думаю, что вы страдаете от ошибки Oracle 9197434 (BIND PEEKING НЕ ПРОИСХОДИТ ПРИ ИСПОЛЬЗОВАНИИ DBMS_SQL)

Насколько я знаю, это не исправлено.

Без просмотра переменных связывания Oracle не знает, какое значение будет в правой части вашего условия LIKE. Например, это может быть просто «%». Таким образом, Oracle делает предположения о том, сколько строк будет LIKE типичным значением переменной связывания. Эти предположения довольно консервативны и, вероятно, вынуждают Oracle отказаться от быстрого плана, который вы хотите (вероятно, с использованием индекса), в пользу медленного плана, который вы получаете (вероятно, с использованием хэш-соединения).

Я бы порекомендовал вам использовать Native Dynamic SQL (т. е. EXECUTE IMMEDIATE), если это возможно, поскольку он не страдает от этой ошибки. В противном случае вам может понадобиться HINT ваш SQL.

person Matthew McPeak    schedule 17.11.2016
comment
Версия 11.2 с той же ошибкой — ошибка № 13386678 (ПРОГНОЗ СВЯЗИ НЕ РАБОТАЕТ В DBMS_SQL). Oracle закрыл эту ошибку как невозможно исправить. Итак, вам нужен обходной путь. - person Matthew McPeak; 17.11.2016

arrow_upward
4
arrow_downward

Когда вы используете LIKE с переменной связывания, в некоторых старых версиях Oracle приходится делать предположения о том, сколько строк будет совпадать. Я не могу вспомнить, какое значение он выбирает (оно может варьироваться в зависимости от версии), это может быть 5%, 10%, что угодно. Теперь это может быть далеко не в соответствии с реальностью и привести к плохому плану.

Существует недокументированный (и неподдерживаемый) параметр оптимизатора под названием _like_with_bind_as_equality, который делает то, что следует из его названия, т. е. если установлено значение true, предполагается, что количество строк, возвращаемых column like :bv, совпадает с будет возвращен column = :bv. Так что, если вы используете это, вы можете получить более быстрый план. Вы можете установить его через alter session.

person Tony Andrews    schedule 17.11.2016
comment
Пытался сделать это, но, к сожалению, это не имеет никакого эффекта. - person Tijs; 18.11.2016

arrow_upward
1
arrow_downward

Я столкнулся с той же проблемой, я попробовал, как указано ниже, и, похоже, работает нормально.

V_QUERY := V_QUERY||' И FIRST_NAME НРАВИТСЯ ''%''||:VAR2||''%''';

Спасибо

person Amol Shewale    schedule 04.04.2020