МН / SQL - Дополнительные условия в где-пункте - без динамического sql?

Хотя я согласен с Тони в том, что производительность при использовании динамического SQL лучше, контекстные переменные - лучший подход, чем использование переменных связывания.

Использование IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE не идеален для обработки необязательных значений. Каждый раз, когда отправляется запрос, Oracle сначала проверяет свой общий пул, чтобы увидеть, был ли этот оператор отправлен ранее. Если да, то извлекается план выполнения запроса и выполняется SQL. Если оператор не может быть найден в общем пуле, Oracle должен пройти через процесс синтаксического анализа оператора, проработать различные пути выполнения и придумать оптимальный план доступа (также известный как «лучший путь»), прежде чем он сможет быть выполнен. Этот процесс известен как «жесткий синтаксический анализ» и может занять больше времени, чем сам запрос. Подробнее о жестком / мягком разборе в Oracle здесь и AskTom здесь .

Вкратце - это:

and (:bcode is null or q.bcode = :bcode)

... будет выполнять то же самое, динамическое или иное . Нет никаких преимуществ в использовании переменных связывания в динамическом SQL для необязательных параметров. Настройка по-прежнему нарушает SARGability ...

Параметры контекста - это функция, которая была введена в Oracle 9i . Они привязаны к пакету и могут использоваться для установки значений атрибутов (только для пользователей с разрешением EXECUTE для пакета, и вам придется предоставить схеме CREATE CONTEXT). Переменные контекста можно использовать для настройки динамического SQL, чтобы он включал только то, что необходимо для запроса на основе критериев фильтра / поиска. В сравнении, Для переменных связывания (также поддерживаемых в динамическом 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 следующие:

1. Имя переменной контекста. Не требуется создание экземпляра
2. Переменная в переменной контекста. Переменная контекста похожа на переменную сеанса, предполагая знакомство с веб-приложениями и объектами сеанса.
3. Значение для переменной, определенной в параметре №2.

Связывание против контекста

Связывание переменных означает, что Oracle ожидает заполнения ссылки на переменную - в противном случае это ошибка ORA. Например:

... L_QUERY USING IN_EXAMPLE_VALUE

... ожидает, что будет заполнена единственная ссылка на переменную привязки. Если IN_EXAMPLE_VALUE имеет значение NULL, имеет как : переменная в запросе. IE: И: переменная IS NULL

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

ВАЖНО : переменные связывания обрабатываются в порядке вхождение (известное как порядковый номер), НЕ по имени. Вы заметите, что в предложении USING нет объявления типа данных. Порядковые числа не идеальны - если вы измените их в запросе без обновления предложения USING ,

Хотя вы могли бы это сделать ...

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;

Это означает, что запрос результата будет "саргируемым" (новое слово для меня, я должен признать!), поскольку результат выполнения запроса будет (например):

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 is null» необходимы при использовании собственного динамического SQL, но их можно избежать, используя DBMS_SQL.

Примечание: использование (1 = 1 или: bindvar is null) , когда переменная связывания имеет значение null, было предложено в комментарии Михала Правды, так как это позволяет оптимизатору исключить предложение.