Производительность SQL: SELECT DISTINCT против GROUP BY

Ответ 1

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

Попробуйте заменить его на соединение, вместо этого:

select . . .,
       parentcnt
from . . . left outer join
      (SELECT PARENT_ITEM_ID, COUNT(PKID) as parentcnt
       FROM ITEM_PARENTS 
      ) p
      on items.item_id = p.parent_item_id

Ответ 2

Я уверен, что GROUP BY и DISTINCT имеют примерно один и тот же план выполнения.

Разница здесь, поскольку мы должны угадать (поскольку у нас нет планов объяснений) является IMO, что встроенный подзапрос выполняется ПОСЛЕ GROUP BY, но ПЕРЕД DISTINCT.

Итак, если ваш запрос возвращает 1M строк и объединяется в 1k строк:

• Запрос GROUP BY выполнил бы подзапрос 1000 раз,
• В то время как запрос DISTINCT выполнил бы подзапрос 1000000 раз.

План объяснения tkprof поможет продемонстрировать эту гипотезу.

Пока мы обсуждаем это, я думаю, важно отметить, что способ написания запроса вводит в заблуждение как для читателя, так и для оптимизатора: вы, очевидно, хотите найти все строки из item/item_transactions, у которых есть TASK_INVENTORY_STEP.STEP_TYPE со значением "ТИП А".

ИМО у вашего запроса был бы лучший план, и было бы легче читать, если бы оно написано следующим образом:

SELECT ITEMS.ITEM_ID,
       ITEMS.ITEM_CODE,
       ITEMS.ITEMTYPE,
       ITEM_TRANSACTIONS.STATUS,
       (SELECT COUNT(PKID) 
          FROM ITEM_PARENTS 
         WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID) AS CHILD_COUNT
  FROM ITEMS
  JOIN ITEM_TRANSACTIONS 
    ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
   AND ITEM_TRANSACTIONS.FLAG = 1
 WHERE EXISTS (SELECT NULL
                 FROM JOB_INVENTORY   
                 JOIN TASK_INVENTORY_STEP 
                   ON JOB_INVENTORY.JOB_ITEM_ID=TASK_INVENTORY_STEP.JOB_ITEM_ID
                WHERE TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
                  AND ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID)

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

Обратите внимание также, что в исходном select не используются 4 таблицы.

Ответ 3

Прежде всего следует отметить, что использование Distinct указывает на запах кода, например, на анти-шаблон. Обычно это означает, что отсутствует соединение или дополнительное соединение, которое генерирует повторяющиеся данные. Рассматривая ваш запрос выше, я предполагаю, что причина, по которой group by работает быстрее (без просмотра запроса), заключается в том, что расположение group by уменьшает количество записей, которые в итоге возвращаются. В то время как Distinct выдувает результирующий набор и выполняет сравнение строк за строкой.

Обновить для приближения

Извините, я должен был быть более ясным. Записи генерируются, когда пользователи выполняют определенные задачи в системе, поэтому нет расписания. пользователь может генерировать одну запись за день или сотни в час. важно то, что каждый раз, когда пользователь запускает поиск, обновляется записи должны быть возвращены, что заставляет меня сомневаться в том, что материализованная просмотр будет работать здесь, особенно если запрос, заполняющий его, будет долго работать.

Я верю, что это точная причина использовать материализованное представление. Таким образом, процесс будет работать таким образом. Вы берете длинный запрос как часть, которая создает материализованное представление, поскольку мы знаем, что пользователь заботится только о "новых" данных после выполнения какой-либо произвольной задачи в системе. Итак, что вы хотите сделать, это запрос к этому материализованному представлению базы данных, которое может постоянно обновляться в фоновом режиме, стратегия стратегии сохранения не должна заглушать материализованное представление (сохраняя несколько сотен записей за раз, не будет раздавить что-либо). То, что это позволит, - это Oracle, чтобы захватить блокировку чтения (обратите внимание, что нам все равно, сколько источников читает наши данные, мы только заботимся о писателях). В худшем случае пользователь будет иметь "устаревшие" данные за микросекунды, поэтому, если это не будет финансовая торговая система на Уолл-стрит или система для ядерного реактора, эти "блики" должны остаться незамеченными даже у самых орел-пользователей.

Пример кода, как это сделать:

create materialized view dept_mv FOR UPDATE as select * from dept; 

Теперь ключ к этому - до тех пор, пока вы не вызываете обновление, вы не потеряете ни одной из сохраненных данных. Вам будет нужно определить, когда вы хотите "базовую линию" получить свое материализованное представление (возможно, полночь?)

Ответ 4

Вы должны использовать GROUP BY для применения операторов агрегатов к каждой группе и DISTINCT, если вам нужно только удалить дубликаты.

Я думаю, что производительность одинакова.

В вашем случае, я думаю, вы должны использовать GROUP BY.