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 может быть любым..