Unterschied zwischen Ziffer und Unterabfrage?

Den WITH - Klausel ist für subquery factoring, auch bekannt als common table expressions oder CTEs:

Die MIT query_name-Klausel können Sie einen Namen zuweisen, um eine Unterabfrage-block. Können Sie verweisen Sie auf die Unterabfrage-block mehrere Orte in der Abfrage durch Angabe query_name. Oracle-Datenbank optimiert die Abfrage, indem Sie die Behandlung der Abfrage, die als Namen entweder ein inline-Ansicht oder eine temporäre Tabelle.

In Ihrem zweiten Beispiel, was Sie genannt haben temp_table ist eine inline-Ansicht, nicht eine temporäre Tabelle.

In vielen Fällen die Wahl, welche zu verwenden, kommt es auf Ihren bevorzugten Stil, und die Allgemeine Tabellenausdrücke können machen den code besser lesbar, vor allem mit mehreren Stufen von Unterabfragen (Meinungen variieren natürlich). Wenn Sie beziehen sich nur auf den CTE/inline-anzeigen, sobald Sie Sie wahrscheinlich nicht sehen, einen Unterschied in der Leistung, und das Optimierungstool können am Ende mit dem gleichen plan.

Sind Sie besonders nützlich, wenn Sie verwenden müssen, um dieselbe Unterabfrage in mehr als einem Ort, wie in einer union. Sie können ziehen Sie eine inline-Ansicht aus in einen CTE, so ist der code nicht wiederholt, und es erlaubt die Optimierungstools zu materialisieren, wenn es denkt, dass wäre von Vorteil.

Beispielsweise das erfundene Beispiel:

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

könnte umgestaltet werden, um:

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

Die Unterabfrage nicht mehr wiederholt werden. Je komplizierter der code wiederholt wird, desto nützlicher ist Sie von einer Wartung Sicht die Verwendung einer CTE. Und desto teurer wird die Unterabfrage, die mehr Leistung nutzen Sie könnte sehen mit einem CTE, obwohl das Optimierungstool ist in der Regel ziemlich gut, um herauszufinden, was Sie tun sowieso.

Darüber hinaus, wenn die Unterabfrage enthält analytische Funktionen (LEAD/LAG/etc) und wenn Sie möchten, filtern Sie die Ergebnisse mit der analytischen Funktion, mit der SUBQUERY Ansatz, müssten Sie zum einfügen der Ergebnisse in eine temp-Tabelle und führen Sie die Filterung usw. auf die temp-Tabelle in der Erwägung, dass die Verwendung eines WITH - Klausel, die Sie verwenden können, ist das Ergebnis für die Filterung/Gruppierung/etc. in der gleichen Abfrage

;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