Różnica między widokiem wbudowanym a klauzulą ​​WITH?

Widoki wbudowane pozwalają wybierać podzapytanie, jakby to była inna tabela:

SELECT
    *
FROM /* Selecting from a query instead of table */
    (
        SELECT
            c1
        FROM
            t1
        WHERE
            c1 > 0
    ) a
WHERE
    a.c1 < 50;

Widziałem to przy użyciu różnych terminów: widoki wbudowane, klauzula WITH, CTE i tabele pochodne. Wydaje mi się, że różnią się składnią specyficzną dla tego samego dostawcy.

Czy to błędne założenie? Czy występują między nimi jakieś różnice techniczne / wydajności?

Istnieją pewne ważne różnice między widokami wbudowanymi (tabele pochodne) a klauzulą ​​WITH (CTE) w Oracle. Niektóre z nich są dość uniwersalne, tj. Mają zastosowanie do innych RDBMS.

1. WITH może być używany do tworzenia rekurencyjnych podkwerend, wbudowany widok -nie (o ile wiem, to samo dotyczy wszystkich RDBMS, które obsługują CTE)
2. Podzapytanie w WITHklauzuli jest najprawdopodobniej najpierw wykonywane fizycznie; w wielu przypadkach wybór między WITHwidokiem wbudowanym a widokiem wbudowanym powoduje, że optymalizator wybiera różne plany wykonania (wydaje mi się, że jest on specyficzny dla dostawcy, a może nawet dla wersji).
3. Podzapytanie w WITHmożna zmaterializować jako tabelę tymczasową (nie wiem, czy jakikolwiek inny dostawca oprócz Oracle obsługuje tę funkcję).
4. Do kwerendy w WITHmożna odwoływać się wiele razy, w innych podkwerendach oraz w kwerendzie głównej (prawdziwe dla większości RDBMS).

Inne odpowiedzi całkiem dobrze pokrywają różnice w składni, więc nie będę w to wchodził. Zamiast tego ta odpowiedź dotyczy tylko wydajności Oracle.

Optymalizator Oracle może zmaterializować wyniki CTE w wewnętrznej tabeli tymczasowej. Używa do tego heurystyki zamiast optymalizacji opartej na kosztach. Heurystyka jest czymś w rodzaju „Zmaterializuj CTE, jeśli nie jest to trywialne wyrażenie, a do CTE odwołuje się więcej niż raz w zapytaniu”. Istnieje kilka zapytań, dla których materializacja poprawi wydajność. Istnieje kilka zapytań, w przypadku których materializacja radykalnie obniży wydajność. Poniższy przykład jest nieco wymyślony, ale dobrze ilustruje ten punkt:

Najpierw utwórz tabelę z kluczem podstawowym zawierającym liczby całkowite od 1 do 10000:

CREATE TABLE N_10000 (NUM_ID INTEGER NOT NULL, PRIMARY KEY (NUM_ID));

INSERT /*+APPEND */ INTO N_10000
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10000
ORDER BY LEVEL;

COMMIT;

Rozważ następujące zapytanie, które korzysta z dwóch tabel pochodnych:

SELECT t1.NUM_ID
FROM 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t1
LEFT OUTER JOIN 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Możemy spojrzeć na to zapytanie i szybko ustalić, że nie zwróci on żadnych wierszy. Oracle powinna być w stanie również użyć tego indeksu do ustalenia tego. Na moim komputerze zapytanie kończy się niemal natychmiast z następującym planem:

Nie lubię się powtarzać, więc spróbujmy tego samego zapytania z CTE:

WITH N_10000_CTE AS (
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Oto plan:

To naprawdę zły plan. Zamiast używać indeksu, Oracle materializuje 10000 X 10000 = 100000000 wierszy do tabeli tymczasowej tylko po to, by ostatecznie zwrócić 0 rzędów. Koszt tego planu wynosi około 6 M, co jest znacznie wyższe niż w przypadku innego zapytania. Zapytanie zajęło 68 sekund, aby zakończyć na moim komputerze.

Zwróć uwagę, że zapytanie mogło się nie powieść, jeśli nie ma wystarczającej ilości pamięci lub wolnego miejsca w tymczasowym obszarze tabel.

Mogę użyć nieudokumentowanej INLINEwskazówki, aby uniemożliwić optymalizatorowi zmaterializowanie CTE:

WITH N_10000_CTE AS (
  SELECT /*+ INLINE */ n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

To zapytanie może korzystać z indeksu i kończy się niemal natychmiast. Koszt zapytania jest taki sam jak poprzednio, 11. Zatem w przypadku drugiego zapytania heurystyka zastosowana przez Oracle spowodowała, że ​​wybrał zapytanie o szacowanym koszcie 6 M zamiast zapytania o szacowanym koszcie 11.

W przypadku SQL Server WITH CTEokreśla tymczasowy nazwany zestaw wyników, ale jest wymagany tylko dla pierwszego CTE. to znaczy

WITH CTE AS (SELECT .... FROM), 
CTE2 AS (SELECT .... FROM)

SELECT CTE.Column, CTE2.Column
FROM CTE
INNER JOIN CTE2 on CTE.Column = CTE2.Column

Ale to nie jest podkwerenda lub skorelowane podkwerenda. Są rzeczy, które możesz zrobić z CTE, czego nie możesz zrobić z zapytaniem podrzędnym w SQL Server, np. Zaktualizuj tabele, do których odwołuje się CTE. Oto przykład aktualizacji tabeli za pomocą CTE.

Podkwerenda byłoby czymś w rodzaju

SELECT
   C1,
   (SELECT C2 FROM SomeTable) as C2
FROM Table

Lub skorelowane pod-zapytanie jest tym, co podałeś w swoim PO, jeśli miałbyś odwoływać się / dołączać / ograniczać swoje wyniki na podstawie a.c1.

Więc na pewno nie są tym samym, chociaż w wielu przypadkach można użyć jednej lub więcej z tych metod, aby osiągnąć ten sam wynik. To zależy tylko od tego, jaki jest ten efekt końcowy.

Główną różnicą między withklauzulą ​​a podzapytaniem w Oracle jest to, że można odwoływać się do zapytania w klauzuli wiele razy. Następnie możesz dokonać optymalizacji przy pomocy materializepodpowiedzi, zmieniając go w tabelę tymczasową . Możesz także wykonywać zapytania rekurencyjne, odwołując się do niej w withklauzuli. Nie możesz tego zrobić w widoku wbudowanym.

Więcej informacji można znaleźć tutaj i tutaj .

Należy uważać na CTE na serwerze SQL, a nie tylko na Oracle, zdarzają się przypadki, w których zapytania działają znacznie gorzej przy użyciu CTE w porównaniu do podkwerend, krzyżowego zastosowania itp.

Jak zawsze ważne jest przetestowanie dowolnego zapytania w różnych warunkach obciążenia, aby ustalić, które z nich działa najlepiej.

Podobnie jak @scsimon z oracle, czasami serwer MS SQL nie robi tego, czego oczekujesz w odniesieniu do użycia indeksu.

Jeśli zamierzasz korzystać z tych samych danych więcej niż jeden raz, CTE mogą być bardziej przydatne, jeśli używasz ich tylko raz, często w dużych zestawach danych szybsze jest podzapytanie.

np. wybierz * z (moje podzapytanie) dołącz do czegoś innego ...