WHERE vs. HAVING с GROUP BY

Вот в чем дело:

1. Согласно плану выполнения Oracle, оба запроса выполняют полное сканирование таблицы. То есть они читают ВСЕ РЯДЫ таблицы. Никакой разницы нет.

2. Запрос HAVING выполняет GROUP BY (хеширование), в результате чего получается 3 строки. Затем он применяет фильтр к этим трем строкам и возвращает результат.

3. Запрос WHERE применяет фильтр к каждой строке (100 000 в спецификации) после ее чтения, уменьшая их до 15 000. Наконец, он группирует их (хеширование) в 1 строку и возвращает одну строку.

Я думаю, что в случае, который вы описываете, запрос WHERE применяет фильтр ко всем 100000 строкам, в то время как запрос HAVING откладывает фильтр и применяет его только к 3 строкам. Это ускоряет выполнение запроса HAVING.

Не думайте, что этот результат будет применяться к каждому подобному запросу. Oracle очень умно использует статистику таблиц. В будущем план изменится в соответствии с реальными данными, которые вы добавляете в таблицу. План с 5 рядами ни в коем случае не соответствует плану на 100 000 строк.

Воспринимайте этот результат с недоверием. Сценарии реального мира намного сложнее.

Одно из ваших предположений неверно: HAVING медленнее, чем WHERE, потому что он фильтрует результаты только после доступа к строкам и хеширование.

Это та часть хеширования, которая делает условия HAVING более дорогими, чем условия WHERE. Хеширование требует записи данных, что может быть более затратным как физически, так и алгоритмически.

Теория

Хеширование требует как записи, так и чтения данных. В идеале хеширование данных будет выполняться за время O(n). Но на практике будут возникать хеш-коллизии, которые замедляют работу. И на практике не все данные умещаются в памяти.

Эти две проблемы могут иметь катастрофические последствия. В худшем случае при ограниченной памяти хеширование требует нескольких проходов, а сложность приближается к O(n^2). А запись на диск во временном табличном пространстве на порядки медленнее, чем запись в память.

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

Это может быть особенно актуально в вашей среде. Операция TABLE ACCESS STORAGE FULL подразумевает, что вы используете Exadata. В зависимости от платформы вы можете использовать SQL в кремнии. Эти высокоуровневые условия могут идеально транслироваться в низкоуровневые инструкции, выполняемые на устройствах хранения. Это означает, что ваша оценка затрат на выполнение пункта может быть на несколько порядков завышенной.

Упражняться

Создайте образец таблицы со 100 000 строками:

create table customer(id number, status varchar2(100));

insert into customer
select
    level,
    case
        when level <= 15000 then 'Deceased'
        when level between 15001 and 50001 then 'Active'
        else 'Dormant'
    end
from dual
connect by level <= 100000;

begin
    dbms_stats.gather_table_stats(user, 'customer');
end;
/

Выполнение кода в цикле показывает, что версия WHERE примерно в два раза быстрее, чем версия HAVING.

--Run times (in seconds): 0.765, 0.78, 0.765
declare
    type string_nt is table of varchar2(100);
    type number_nt is table of number;
    v_status string_nt;
    v_count number_nt;
begin
    for i in 1 .. 100 loop
        SELECT status, count(status)
        bulk collect into v_status, v_count
        FROM customer
        GROUP BY status
        HAVING status != 'Active' AND status != 'Dormant';
    end loop;
end;
/

--Run times (in seconds): 0.39, 0.39, 0.39
declare
    type string_nt is table of varchar2(100);
    type number_nt is table of number;
    v_status string_nt;
    v_count number_nt;
begin
    for i in 1 .. 100 loop
        SELECT status, count(status)
        bulk collect into v_status, v_count
        FROM customer
        WHERE status != 'Active' AND status != 'Dormant'
        GROUP BY status;
    end loop;
end;
/