Реализация фильтра типа "Не фильтровать ничего либо фильтровать по значению" на SQL, без преобразований запроса

Пост обновлен. Добавил очень важную информацию, о том когда этот способ использовать нельзя. Рекомендую ознакомится всем, кто собирается использовать этот маленький трюк

При написании запросов для статистик (и не только) часто возникает необходимость сделать необязательный фильтр по значению, например фильтр по типу контента, в котором можно выбрать варианты: Все типы, Музыка, Видео, Картинка.
Обычно или пишут два разных запроса, которые отличаются только наличием фильтра (например: ...where type_id=:type_id) или, что бывает гораздо чаще, условие-фильтр хранят в отдельной переменной и в зависимости от входящих данных подставляют его в строку запроса или не подставляют
например (if type_id is not null then filter:='type_id=:type_id' else filter:='' endif;)

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

Можно легко избавится от этих недостатков, используя функцию decode или конструкцию case when .... then ... else .... end. По сути decode(a, b, c, d) это функция, реализующая конструкцию if a=b then c else d end if; Подробнее, о ней можно прочитать в документации. У функции есть два серьезных ограничения - она умеет работать только с числовыми типами данных (NUMBER, BINARY_FLOAT, или BINARY_DOUBLE) и в ней не работает сравнение с null.

Идея очень проста: проверять переменную на null прямо внутри запроса, с помощью такого кода ...where decode(nvl(:type_id, 0),0,0,type_id)=nvl(:type_id, 0). Если перевести этот код на человеческий язык, то получится: если переменная равна null (или 0) тогда сравниваем ее саму с собой, в противном случае сравниваем ее с полем, по которому предусмотрет фильтр. В первом случае, для всех строк получится выражение 0=0 - всегда истина, фильт не применится, а во втором type_id = :type_id выдаст только те строки, где это условие выполняется. Функция nvl используется для обхода ограничения decode, которое состоит в том что в ней не работает сравнение с null.


with sample_table as (
select 1 id, 1 type_id from dual union all
select 2 id, 1 type_id from dual union all
select 3 id, 1 type_id from dual union all
select 4 id, 2 type_id from dual union all
select 5 id, 2 type_id from dual union all
select 6 id, 2 type_id from dual union all
select 7 id, 3 type_id from dual union all
select 8 id, 3 type_id from dual union all
select 9 id, 3 type_id from dual
)
select * from sample_table where decode(nvl(:type_id, 0),0,0,type_id)=nvl(:type_id, 0);

Как реализовать такую конструкцию для нечисловых типов данных - придумайте сами.

Любые вопросы, мнения, эмоции или угрозы вы можете оставить в комментариях

В каких случаях использовать и в каких не использовать этот прием

В некоторых случаях этот прием может значительно повлиять на план выполнения запроса и как следствие на производительность самого запроса.
Приведу примеры, когда фильтр нельзя применять и когда можно. Создадим большую таблицу BIG_TABLE

CREATE TABLE BIG_TABLE as SELECT * FROM ALL_OBJECTS;

И создадим по ней индекс:

CREATE INDEX BIG_TABLE_INDEX1 ON BIG_TABLE ("OWNER", "OBJECT_NAME") ;

Пример когда нельзя использовать фильтр decode.

Задача: Если указан параметр :owner, то выводить объекты данног пользователя, в обратном случае выводить все объекты

.Напишем три запроса. Первый не будет фильтровать ничего второй будет фильтровать по полю owner, а третий будет основаный на вышеприведенном способе, будет делать или первое или второе.

1) SELECT * FROM BIG_TABLE;

В этом запросе индекс использоватся не будет так как нужны данные всей таблицы (в такой ситуации использование индекса неэффективно).

2) SELECT * FROM BIG_TABLE where owner=:owner;

А в этом будет - так как нужна только часть данных использование индекса эффективние полного сканирования таблицы.

3) SELECT * FROM BIG_TABLE where decode(:owner, null, 0,owner)=nvl(:owner,0);

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

Пример когда можно использовать decode фильтр

Задача: для объектов определенного пользователя возвращать список объектов определенного типа, если он указан. В противном случае выводить объекты всех типов.

Модифицируем запросы первого примера:

1) SELECT * FROM BIG_TABLE where owner=:owner;



Индекс используется как и в первый раз.

2) SELECT * FROM BIG_TABLE where owner=:owner and objet_type=:obj_type;

Обратите внимание что условие objet_type=:obj_type стоит не в пути доступа а в условиях фильтра. Это значит, что запрос вначале выбирает строки соответствующие условию owner=:owner используя индекс, а потом фильтрует их по условию objet_type=:obj_type

3) SELECT * FROM BIG_TABLE where decode(:obj_type, null, 0,objet_type)=nvl(:objet_type,0);


Этот план практически не отличается от предидущего, за исключением того что в условиях фильтра стоит условие decode(:obj_type, null, 0,objet_type)=nvl(:objet_type,0). В этом случае, запрос 2 и запрос 3 практически не отличаются, поэтому можно смело использовать decode.

Другие случаи, когда не стоит использовать этот фильтр.
Поскольку оптимизатор будет принимать во внимание максимальное количество строк, возвращенное подзапросом, то это может повлиять на порядок и способ соединения таблиц.

Вывод: фильтр decode можно использовать в случаях когда условия фильтрации не является путем доступа, а является фильтром.