PL/SQL - Необязательные условия в where-clause - без динамического sql?
У меня есть запрос, где не все условия необходимы. Вот пример того, как он выглядит, когда используются все условия:
select num
from (select distinct q.num
from cqqv q
where q.bcode = '1234567' --this is variable
and q.lb = 'AXCT' --this is variable
and q.type = 'privt' --this is variable
and q.edate > sysdate - 30 --this is variable
order by dbms_random.value()) subq
where rownum <= 10; --this is variable
Детали, обозначенные как --this is variable, являются частями, которые, ну, меняются! Если условие НЕ указано, то значение по умолчанию отсутствует. Например, если вход задает "*" для q.type(но оставляет все остальное одинаковым), тогда запрос должен соответствовать всем для типа и выполнять как:
select num
from (select distinct q.num
from cqqv q
where q.bcode = '1234567' --this is variable
and q.lb = 'AXCT' --this is variable
--and q.type = 'privt' --this condition ignored because of "type=*" in input
and q.edate > sysdate - 30 --this is variable
order by dbms_random.value()) subq
where rownum <= 10; --this is variable
Я знаю, что можно использовать динамический sql для создания этого запроса "на лету", но мне интересно, какие проблемы с производительностью могут возникнуть, и если есть лучший способ сделать это.
Ответы
Ответ 1
Решение, на котором я остановился, - это тот, который генерирует динамический SQL-запрос, который может выглядеть следующим образом:
select num
from (select distinct q.NUM
from cqqv q
where (q.bcode = :bcode)
and (1=1 or :lb is null)
and (1=1 or :type is null)
and (q.edate> :edate)
order by dbms_random.value()) subq
where rownum <= :numrows
(в этом примере условия bcode и edate НЕ были необязательными, но lb и type были)
Я думаю, что это (или очень похожее) то, что предлагал Michal Pravda, и наш DBA здесь предпочитает это решение по решению контекстной переменной. Спасибо за все, что помогло и предложило совет!
Ссылка, найденная нашим администратором базы данных, которая подробно описывает это решение:
Спросите Tom: о популярности и естественном выборе
Ответ 2
Пока вы можете это сделать...
select num
from (select distinct q.num
from cqqv q
where 1=1
and (:bcode is null or q.bcode = :bcode)
and (:lb is null or q.lb = :lb)
and (:type is null or q.type = :type)
and (:edate is null or q.edate > :edate - 30)
order by dbms_random.value()) subq
where rownum <= :numrows
... производительность с использованием динамического SQL обычно будет лучше, так как она создаст более целевой план запроса. В вышеприведенном запросе Oracle не может определить, следует ли использовать индекс на bcode или lb или тип или edate, и, вероятно, каждый раз будет выполнять полное сканирование таблицы.
Конечно, вы должны использовать переменные связывания в динамическом запросе, а не конкатенировать литеральные значения в строке, в противном случае производительность (и масштабируемость и безопасность) будет очень плохой.
Чтобы быть ясным, динамическая версия, которую я имею в виду, будет работать следующим образом:
declare
rc sys_refcursor;
q long;
begin
q := 'select num
from (select distinct q.num
from cqqv q
where 1=1';
if p_bcode is not null then
q := q || 'and q.bcode = :bcode';
else
q := q || 'and (1=1 or :bcode is null)';
end if;
if p_lb is not null then
q := q || 'and q.lb = :lb';
else
q := q || 'and (1=1 or :lb is null)';
end if;
if p_type is not null then
q := q || 'and q.type = :type';
else
q := q || 'and (1=1 or :type is null)';
end if;
if p_edate is not null then
q := q || 'and q.edate = :edate';
else
q := q || 'and (1=1 or :edate is null)';
end if;
q := q || ' order by dbms_random.value()) subq
where rownum <= :numrows';
open rc for q using p_bcode, p_lb, p_type, p_edate, p_numrows;
return rc;
end;
Это означает, что в результате запроса будет "sargable" (новое слово для меня, которое я должен признать!), поскольку в результате выполнения запроса будет (например):
select num
from (select distinct q.num
from cqqv q
where 1=1
and q.bcode = :bcode
and q.lb = :lb
and (1=1 or :type is null)
and (1=1 or :edate is null)
order by dbms_random.value()) subq
where rownum <= :numrows
Однако я согласен с тем, что в этом примере может потребоваться до 16 жестких разборов. Классы "and: bv null" требуются при использовании собственного динамического SQL, но их можно избежать с помощью DBMS_SQL.
Примечание: использование (1=1 or :bindvar is null), когда переменная связывания имеет значение null, было предложено в комментарии Michal Pravda, поскольку это позволяет оптимизатору устранить предложение.
Ответ 3
Хотя я согласен с Тони в том, что производительность использования динамического SQL лучше, переменные контекста являются лучшим подходом, чем использование переменных привязки.
Использование IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE не идеально подходит для обработки необязательных значений. Каждый раз, когда запрос отправляется, Oracle сначала проверяет в своем общем пуле, чтобы убедиться, что выражение было представлено ранее. Если это так, выполняется выполнение плана выполнения запроса и выполняется SQL. Если оператор не может быть найден в общем пуле, Oracle должен пройти процесс анализа оператора, разработки различных путей выполнения и составления оптимального плана доступа (лучший путь AKA) до того, как его можно будет выполнить. Этот процесс известен как "жесткий синтаксический анализ" и может занимать больше времени, чем сам запрос. Подробнее о жестком/мягком анализе в Oracle здесь, и AskTom здесь.
Короче говоря:
and (:bcode is null or q.bcode = :bcode)
... будет выполнять то же самое, динамическое или иное. Нет смысла использовать переменные связывания в динамическом SQL для необязательных параметров. Настройка все еще разрушает SARGability...
Контекстные параметры - это функция, которая была введена в Oracle 9i. Они привязаны к пакету и могут использоваться для установки значений атрибутов (только для пользователей с разрешения EXECUTE на пакете, и вам нужно предоставить CREATE CONTEXT для схемы). Контекстные переменные могут использоваться для адаптации динамического SQL, поэтому он включает только то, что необходимо для запроса на основе критериев фильтра/поиска. Для сравнения, переменные Bind (также поддерживаемые в динамическом SQL) требуют указания значения, которое может привести к испытаниям IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE в поисковом запросе. На практике для каждой процедуры или функции следует использовать отдельную переменную контекста, чтобы исключить риск загрязнения ценности.
Здесь ваш запрос с использованием переменных контекста:
L_CURSOR SYS_REFCURSOR;
L_QUERY VARCHAR2(5000) DEFAULT 'SELECT num
FROM (SELECT DISTINCT q.num
FROM CQQV q
WHERE 1 = 1 ';
BEGIN
IF IN_BCODE IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'BCODE',
IN_BCODE);
L_QUERY := L_QUERY || ' AND q.bcode = SYS_CONTEXT(''THE_CTX'', ''BCODE'') ';
END IF;
IF IN_LB IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'LB',
IN_LB);
L_QUERY := L_QUERY || ' AND q.lb = SYS_CONTEXT(''THE_CTX'', ''LB'') ';
END IF;
IF IN_TYPE IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'TYPE',
IN_TYPE);
L_QUERY := L_QUERY || ' AND q.type = SYS_CONTEXT(''THE_CTX'', ''TYPE'') ';
END IF;
IF IN_EDATE IS NOT NULL THEN
DBMS_SESSION.SET_CONTEXT('THE_CTX',
'EDATE',
IN_EDATE);
L_QUERY := L_QUERY || ' AND q.edate = SYS_CONTEXT(''THE_CTX'', ''EDATE'') - 30 ';
END IF;
L_QUERY := L_QUERY || ' ORDER BY dbms_random.value()) subq
WHERE rownum <= :numrows ';
FOR I IN 0 .. (TRUNC(LENGTH(L_QUERY) / 255)) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(L_QUERY, I * 255 + 1, 255));
END LOOP;
OPEN L_CURSOR FOR L_QUERY USING IN_ROWNUM;
RETURN L_CURSOR;
END;
В примере все еще используется переменная привязки для rownum, , потому что значение не необязательно.
DBMS_SESSION.SET_CONTEXT('THE_CTX', 'LB', IN_LB);
Параметры SET_CONTEXT следующие:
- Имя переменной контекста. Не было задействовано создание экземпляра
- Переменная внутри переменной контекста. Переменная контекста похожа на переменную сеанса, предполагая знакомство с веб-приложениями и объектами сеанса.
- Значение для переменной, определенной в параметре # 2.
Связывание с контекстом
Связанные переменные означают, что Oracle ожидает переменную ссылку на заполнение - в противном случае это ошибка ORA. Например:
... L_QUERY USING IN_EXAMPLE_VALUE
... ожидает, что имеется одна ссылка переменной привязки, которая будет заполнена. Если IN_EXAMPLE_VALUE имеет значение null, имеет < <<27 > в запросе. IE: AND :variable IS NULL
Использование контекстной переменной означает отсутствие необходимости включать внешнюю/избыточную логику, проверяя, имеет ли значение значение null.
ВАЖНО: переменные связывания обрабатываются в порядке их появления (так называемый порядковый номер), НЕ по имени. Вы не заметите, что в предложении USING нет описания типа данных. Ординалы не идеальны - если вы измените их в запросе без обновления предложения USING, он разорвет запрос до тех пор, пока он не будет исправлен.
Ответ 4
Я бы просто сделал это
select num
from (select distinct q.num
from cqqv q
where q.bcode = '1234567' --this is variable
and q.lb = 'AXCT' --this is variable
and q.type = nvl(<variable-type>, q.type) --this condition ignored because of "type=*" in input
and q.edate > sysdate - 30 --this is variable
order by dbms_random.value()) subq
where rownum <= 10; --this is variable
Следует только гарантировать, что тип переменной имеет значение null, когда фильтрацию q.TYPE следует игнорировать.
Ответ 5
где (columnA = прошлоValue или прошлоValue = -1)
когда переданное значение в sql равно -1, columnA может быть любым..
