Разница между предложением WITH и подзапросом?

Ответ 1

Предложение WITH предназначено для факторинга подзапроса, также известного как общие табличные выражения или CTE:

Предложение WITH query_name позволяет назначить имя блоку подзапроса. Затем вы можете связать блок подзапроса с несколькими местами в запросе, указав имя запроса. Oracle Database оптимизирует запрос, обрабатывая имя запроса как встроенное представление, так и временную таблицу.

В вашем втором примере то, что вы назвали temp_table, представляет собой встроенное представление, а не временную таблицу.

Во многих случаях выбор для использования сводится к вашему предпочитаемому стилю, и CTE могут сделать код более читаемым, особенно с несколькими уровнями подзапросов (мнения, разумеется, меняются). Если вы ссылаетесь только на просмотр CTE/inline, как только вы, вероятно, не увидите никакой разницы в производительности, и оптимизатор может оказаться в том же плане.

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

Например, этот надуманный пример:

select curr from (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr >= 0
union all
select -1 * curr from (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr < 0

может быть реорганизовано в:

with temp_table as (
  select curr from tableone t1
  left join tabletwo t2 on (t1.empid = t2.empid)
)
select curr from temp_table
where curr >= 0
union all
select -1 * curr from temp_table
where curr < 0

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

Ответ 2

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

Ответ 3

Кроме того, если подзапрос содержит аналитические функции (LEAD/LAG/и т.д.) И если вы хотите отфильтровать результат аналитической функции - с SUBQUERY подхода SUBQUERY, вам нужно будет вставить результаты в временную таблицу и выполнить фильтрацию и т.д. для временной таблицы, тогда как с помощью предложения WITH вы можете использовать результат для фильтрации/группировки /etc в том же запросе

;WITH temp AS
(
    SELECT 
        ID
        , StatusID
        , DateChanged
        , LEAD(StatusID,1) OVER (PARTITION BY ID ORDER BY ID, DateChanged, StatusID) NextStatusID
    FROM 
        myTable 
    WHERE 
        ID in (57,58)
)
SELECT
    ID
    , StatusID
    , DateChanged
FROM
    temp
WHERE
    temp.NextStatusID IS NULL