Oracle WITH AS用法優點缺點分析

一、簡介

Oracle WITH AS是一種SQL語法，用於在一個查詢中定義一個臨時的命名結果集，並在查詢中引用該結果集，它是Oracle中實現遞歸查詢的一種方式。當一次查詢需要多次引用同一個子查詢時，使用WITH AS語法可以便於編寫和維護SQL查詢語句。

二、優點

1、提高查詢效率

使用WITH AS語法可以提高SQL查詢效率。因為在多次使用同一個子查詢時，如果每次都重新執行該子查詢，會造成SQL語句的冗餘和性能底下。而使用WITH AS可以將該子查詢的結果集存儲在內存中，並在多次引用時直接使用它，避免重複執行子查詢，從而大大提高SQL查詢效率。

2、簡化SQL語句

使用WITH AS語法可以讓SQL語句更易於編寫和維護。當在一個查詢中需要多次引用同一個子查詢結果集時，不使用WITH AS語法需要在SQL語句中多次複製子查詢的代碼，而使用WITH AS可以將子查詢的結果集給命名為一個臨時表，並在後續查詢中僅僅需要引用這一個表，從而避免了多次複製和粘貼的操作，使SQL語句更為簡潔。

3、支持遞歸查詢

WITH AS語法支持遞歸查詢，是一種實現樹形結構查詢的有效手段。通過建立遞歸查詢的一個公共表達式，使得在同一個查詢中實現多個層次或深度的查詢，簡化了查詢的邏輯實現，使得SQL查詢變得更加易於理解和處理。

三、缺點

1、內存佔用較高

在WITH AS語法中，子查詢的結果集會被存儲在內存中，如果該子查詢結果集較大，會佔用大量的內存資源，導致內存使用率較高，如果處理大量複雜的數據，可能會導致內存不足的問題。

2、可讀性較差

尤其是當WITH AS語法的數據表達比較複雜的時候，SQL語句的可讀性會比較差，閱讀、理解、維護難度較大。所以，對需要複雜嵌套的SQL語句，需要慎重使用WITH AS語法。

3、語句冗餘

使用WITH AS語句處理較小複雜度的遞歸查詢比較常見。然而，在處理遞歸查詢時，因為遞歸查詢需要使用自己的結果集，所以必須要將公共子查詢嵌套在WITHIN 語句中。這樣就會出現 SQL 語句嵌套較深，變得複雜，語句冗餘等問題。同時，還有FROMtoWHERE的查詢範圍問題，對於常規sql語句，查詢的結果集是由FROM 語句中的表決定的，而對於WITH AS 語句，結果集是由查詢部分決定的，同時查詢部分的執行次數，也將會影響到結果集的大小和查詢數據的時間。

四、代碼

下面是一個使用WITH AS語法實現遞歸查詢的示例代碼：

WITH recursivequery AS
(
  SELECT id, name, parent_id FROM organization_structure WHERE id = 1

  UNION ALL

  SELECT o.id, o.name, o.parent_id FROM organization_structure o, recursivequery r WHERE o.parent_id = r.id
)
SELECT * FROM recursivequery;