Oracle 到 SQL Server 和 SQL Server 到 Oracle 遷移指南
已發表: 2022-03-11“供應商依賴”對許多企業高管來說是一個可怕的詞。 另一方面，業界已經普遍認識到，完全的“供應商獨立”是不可能實現的。 在數據庫的情況下尤其如此。
兩個最普及的企業 RDBMS 平台是 Oracle 數據庫和 Microsoft SQL Server（為簡潔起見，在本文的其餘部分，我將分別稱它們為“Oracle”和“SQL Server”）。 當然，IBM Db2 與 Oracle 在不斷縮小但在許多領域仍然至關重要的大型機平台展開競爭。 快速發展的開源替代品，如 PostgreSQL，正在中低級商品硬件和 Web 上的動態環境中站穩腳跟。
但Oracle 與 SQL Server是許多業務主管在其組織需要新的 RDBMS 時面臨的選擇。 最終選擇基於多種因素：許可成本、可用的內部專業知識和過去的經驗、與現有環境的兼容性、合作夥伴關係、未來的業務計劃等。但即使進行了最徹底的前期評估和受過最佳教育的決策，有時也會考慮因素改變，然後平台也需要改變。 我知道這一點，因為在我的職業生涯中，我已經實施了兩次這樣的遷移，一次準備了遷移可行性的評估，而我正致力於跨平台的功能遷移。
Oracle 和 SQL Server 都是“老派”，部分符合 ANSI 的 RDBMS 實現。 撇開過程擴展（PL/SQL 和 Transact-SQL 有不同的語法，但通常很容易在新的面向對象的未來之間進行轉換）不談，SQL 代碼可能看起來很相似。 這是一個危險的蜜罐。
對於 Oracle 和 SQL Server 之間的任何遷移項目（在任一方向），最關鍵的兩個點是事務和密切相關的臨時表，它們是解決事務範圍的關鍵工具。 我們還將介紹嵌套事務——它們存在於另一個事務的範圍內——因為它們是在 Oracle 中實施用戶安全審計的關鍵部分。 但在 SQL Server 中，用戶安全審核需要一種不同的方法，因為它在該上下文中的COMMIT行為。
理解事務結構：一萬英尺觀察 Oracle 與 SQL Server
Oracle 事務是隱式的。 這意味著您無需開始交易——您始終處於交易狀態。 在您發出提交或回滾語句之前，此事務是打開的。 是的，您可以顯式啟動事務，定義回滾安全點，並設置內部/嵌套事務； 但重要的是您永遠不會“不在事務中”，您必須始終發出提交或回滾。 另請注意，發出數據定義語言 (DDL) 語句（ CREATE 、 ALTER等；在事務中可以通過動態 SQL 完成）提交發出它的事務。
與 Oracle 不同，SQL Server 具有顯式事務。 這意味著除非您顯式啟動事務，否則您的所有更改都將“自動”提交 - 在處理您的語句時立即提交，因為每個 DML 語句（ INSERT 、 UPDATE 、 DELETE ）都會自行創建一個事務並提交它，除非它出錯出去。
這是數據存儲實現方式不同的結果——數據如何寫入數據庫以及數據庫引擎如何讀取數據。
在 Oracle 中，DML 語句直接更改數據文件中的記錄。 記錄的舊副本（或空記錄替換，在INSERT的情況下）被寫入當前回滾文件，並在記錄上標記更改的確切時間。
SELECT語句發出時，會根據發出前已修改的數據對其進行處理。 如果在發出SELECT後修改了任何記錄，Oracle 將使用回滾文件中的舊版本。
這就是 Oracle 實現讀一致性和非阻塞讀/寫的方式。 這也是為什麼在非常活躍的事務數據庫上長時間運行的查詢有時會遇到臭名昭著的錯誤 ORA-01555, snapshot too old: rollback segment ... too small 。 （這意味著查詢舊版本記錄所需的回滾文件已被重用。）這就是問題“我的 Oracle 事務應該多長時間？”的正確答案的原因。 是“只要需要，不再需要”。
SQL Server 的實現有所不同：數據庫引擎僅直接向/從數據文件寫入和讀取。 每個 SQL 語句（ SELECT / INSERT / UPDATE / DELETE ）都是一個事務，除非它是將多個語句組合在一起的顯式事務的一部分，從而允許回滾更改。
每個事務都會鎖定它需要的資源。 當前版本的 Microsoft SQL Server 在僅鎖定所需資源方面進行了高度優化，但所需資源由 SQL 代碼定義——因此優化查詢至關重要）。 也就是說，與 Oracle 不同，SQL Server 中的事務應該盡可能短，這就是為什麼自動提交是默認行為的原因。
Oracle 和 SQL Server 中的哪個 SQL 構造受到其事務實現差異的影響？ 臨時表。
Oracle 和 SQL Server 中的臨時表
當 ANSI SQL 標准定義本地和全局臨時表時，它沒有明確說明它們應該如何實現。 Oracle 和 SQL Server 都實現了全局臨時表。 SQL Server 還實現了本地臨時表。 Oracle 18c 還實現了“真正的”本地臨時表（他們稱之為“私有臨時表”）。這使得 SQL Server 代碼到 Oracle 18c 的轉換明顯比舊版本更簡單——完善了 Oracle 之前添加的一些相關的自動遞增標識列等功能。
但從純粹的功能分析的角度來看，私有臨時表的引入可能是喜憂參半，因為它使 SQL Server 到 Oracle 的遷移問題看起來比實際要少。 這是另一個蜜餞，因為它可能會帶來一些新的挑戰。 例如，不能對私有臨時表進行設計時代碼驗證，因此使用它們的任何代碼都將總是更容易出錯。 如果您使用過動態 SQL，我們可以這樣說：私有臨時表的調試同樣複雜，但沒有明顯的獨特用例。 因此，為什麼 Oracle 只在 18c 而不是之前添加了本地（私有）臨時表。
簡而言之，我沒有看到 Oracle 中無法使用相同或更好的全局臨時表實現的私有臨時表的用例。 所以對於任何嚴重的轉換，我們都需要了解Oracle和SQL Server全局臨時表的區別。
Oracle 和 SQL Server 中的全局臨時表
Oracle 全局臨時表是由 DDL 語句在設計時顯式創建的永久數據字典對象。 它只是“全局”的，因為它是一個數據庫級對象，並且可以由任何具有所需權限的數據庫會話訪問。 然而，儘管它的結構是全局的，但全局臨時表中的所有數據都僅限於它在其中操作的會話，並且在任何情況下都不可見於該會話之外。 換句話說，其他會話可以在同一個全局臨時表的自己的副本中擁有自己的數據。 因此，在 Oracle 中，全局臨時表保存會話本地數據——主要用於 PL/SQL 中以簡化代碼和優化性能。
在 SQL Server 中，全局臨時表是在 Transact-SQL 代碼塊中創建的臨時對象。 只要它的創建會話是打開的，它就存在，並且它在結構和數據中對數據庫中的其他會話都是可見的。 因此，它是一個用於跨會話共享數據的全局臨時對象。
SQL Server 中的本地臨時表與全局臨時表的不同之處在於只能在創建它的會話中訪問。 在 SQL Server 中，本地臨時表的使用比使用全局臨時表更廣泛（而且，我想說，對數據庫性能更重要）。
那麼，本地臨時表在SQL Server中是如何使用的，又應該如何翻譯成Oracle呢？
SQL Server 中本地臨時表的關鍵（和正確）使用是縮短或刪除事務資源鎖，尤其是：
- 當一組記錄需要通過某種聚合來處理時
- 當需要分析和修改一組數據時
- 當需要在同一範圍內多次使用同一組數據時
在這些情況下，將這組記錄選擇到本地臨時表中以從源表中刪除鎖定通常是更好的解決方案。
值得注意的是，SQL Server 中的公用表表達式（CTE，即WITH <alias> AS (SELECT...)語句）僅僅是“語法糖”。 它們在 SQL 執行之前被轉換為內聯子查詢。 Oracle CTE（帶有/*+ materialize */提示）進行了性能優化，並創建了物化視圖的臨時版本。 在 Oracle 的執行路徑中，CTE 只訪問一次源數據。 基於這種差異，SQL Server 使用本地臨時表而不是對同一個 CTE 的多個引用可能會更好地執行，就像在 Oracle 查詢中所做的那樣。
由於事務實現之間的差異，臨時表也提供不同的功能。 因此，將 SQL Server 臨時表“按原樣”移動到 Oracle（即使 Oracle 18c 實現了私有臨時表）不僅會損害性能，而且會在功能上出錯。
另一方面，從 Oracle 遷移到 SQL Server 時，需要注意事務長度、全局臨時表的可見性範圍以及帶有“物化”提示的 CTE 塊的性能。
在這兩種情況下，只要遷移的代碼包含臨時表，我們就不應該談論代碼翻譯，而應該談論系統重新實現。
輸入表變量
開發人員可能會想：表變量呢？ 我們是否需要進行任何更改，或者我們可以在 Oracle 到 SQL Server 的遷移步驟中“按原樣”移動表變量嗎？ 好吧，這取決於代碼中使用它們的原因和方式。
讓我們看看如何同時使用臨時表和表變量。 我將從 Microsoft SQL Server 開始。
Transact-SQL 中表變量的實現在某種程度上與臨時表相匹配，但增加了它自己的一些功能。 關鍵區別在於能夠將表變量作為參數傳遞給函數和存儲過程。
這是理論，但實際使用方面的考慮要多一些。
當我來自一個根深蒂固的 Oracle 背景時，我首先負責認真的 Transact-SQL 優化，我希望它是這樣的：表變量在內存中，而臨時表在磁盤上。 但我發現 Microsoft SQL Server 到 2014 年的版本並沒有將表變量存儲在內存中。 因此，對臨時變量的全表掃描確實是對磁盤的全表掃描。 值得慶幸的是，SQL Server 2017 及更高版本支持對臨時表和表變量進行聲明性內存優化。
那麼，如果一切都可以使用臨時表完成或更好地完成，那麼 Transact-SQL 中表變量的用例是什麼？ 表變量的關鍵屬性是它是一個變量，因此不受事務回滾的影響，可以作為參數傳遞。
Transact-SQL函數具有非常嚴格的限制：由於函數的任務是返回某個奇異的返回值，因此它（按設計）不能有副作用。 Transact-SQL 甚至將SELECT視為副作用，因為在 SQL Server 中，對錶的任何訪問都會創建隱式事務和關聯的事務鎖。 這意味著在函數內部，我們不能訪問現有臨時表中的數據，也不能創建臨時表。 因此，如果我們需要將任何一組記錄傳遞給函數，我們就必須使用表變量。
Oracle 對使用（全局）臨時表和集合變量（Oracle PL/SQL 等效於 Transact-SQL 表變量）的考慮是不同的。 Oracle 集合變量在內存中，而臨時表位於臨時表空間中。 Oracle 函數允許對永久或臨時表進行只讀訪問； Oracle 中的簡單SELECT永遠不會鎖定資源。
在 Oracle 中，使用集合變量還是臨時表的選擇取決於預期的數據量、需要保留這些數據的持續時間以及內存與磁盤分配和可用性。 此外，集合變量是將行集作為輸出返回到主機程序的標準方法。
由於 SQL Server 和 Oracle 之間的大多數 SQL 語法元素看起來非常相似，因此將帶有表變量的代碼塊從 SQL Server Transact-SQL 轉換為 Oracle PL/SQL 是一個更簡單且在語法上更寬容的過程。 它可以通過基本的驗證測試，但在功能上不正確，除非採取臨時表重新實現步驟，如上所述。 另一方面，從 Oracle 轉移到 SQL Server 的代碼涉及更多的修改步驟，只是為了在語法上有效。 為了在功能上也正確，它需要解決使用臨時表和 CTE 的深入案例。
內部交易（“嵌套交易”）
就 Oracle 到 SQL Server 的遷移挑戰而言，下一個主要關注領域是嵌套事務。
與臨時表一樣，如果 Transact-SQL 代碼包含任何事務（無論是否嵌套），或者 Oracle 代碼包含任何嵌套事務，那麼我們討論的不僅僅是純代碼遷移，而是功能重新實現。
首先，讓我們看看 Oracle 嵌套事務的行為方式以及我們傾向於如何使用它們。
Oracle 中的嵌套事務
Oracle 嵌套事務是完全原子的並且獨立於外部範圍。 在純交互式 Oracle SQL 查詢中，嵌套事務沒有實際用途。 當您在交互模式下使用 Oracle 時，您只需在看到您進入某個狀態時手動提交您的更改。 如果你做了一些你不能提交的更改，直到你完成最後一步——比如說，你不確定——可能需要回滾的步驟，但你想保留你已經完成的工作，您將創建一個安全點以回滾到它，而無需提交或回滾整個事務。
那麼，嵌套事務在哪裡使用呢？ 在 PL/SQL 代碼中。 更具體地說，在自治程序中——那些用PRAGMA AUTONOMOUS_TRANSACTION聲明的程序。 這意味著當調用此代碼（作為命名存儲過程或匿名）時，事務的提交或回滾獨立於調用此代碼的事務。
使用嵌套事務的目標是提交或回滾一個獨立的工作單元，而不管調用代碼會發生什麼。 當內部事務可以提交或回滾時，它將用於檢查（或保留）共享資源的可用性——例如在實現房間預訂系統時。 僅提交內部事務的主要用途是活動監控、代碼跟踪和安全訪問審計（即，不允許用戶進行更改，但嘗試進行更改。）
SQL Server Transact-SQL 代碼中的嵌套事務完全不同。
SQL Server 中的嵌套事務
在 Transact-SQL 中，內部事務是否提交完全取決於最外面的事務。 如果內部事務已回滾，則它只是回滾。 但是如果一個內部事務已經提交，它仍然沒有完全提交，因為如果它的外部範圍事務的任何級別被回滾，它可以被回滾。
那麼，如果內部事務的提交可以通過回滾外部事務來撤消，那麼內部事務有什麼用呢？ 答案與本地臨時表的用例相同：釋放資源鎖定。 不同之處在於它不是全局鎖釋放，而是直接外部（直接“父”）事務範圍內的鎖。 它在復雜的 Transact-SQL 代碼中用於為外部事務釋放內部資源。 它是一種性能優化和資源管理工具。
由於 Oracle 和 SQL Server 內部/嵌套事務具有不同（甚至可能相反）的行為和完全不同的用例，從一個平台遷移到另一個平台不僅需要重新編寫，還需要完全重新構建任何包含嵌套事務塊的範圍.
其他因素
這些以臨時表和事務為中心的考慮是否是 Oracle 到 SQL Server 遷移中唯一需要解決的問題？ 雖然它們可能是最重要的，但肯定還有其他的，每個都有自己的怪癖，值得一提。 以下是我發現的最容易被誤解的主題的其餘部分：
- SQL Server 中的標識列
- Oracle 中的序列
- Oracle 中的同義詞
- 過濾索引
- 讀取一致性（僅限 Oracle 到 SQL Server）
- 使用遷移工具
本系列的下一部分將繼續探索這些，尤其是前三個。
臨時表、表/集合變量和嵌套事務：前 3 大遷移痛點
我從臨時表、表變量/集合和嵌套事務開始，因為這些是轉換項目中最常見和最明顯的故障點。 Oracle 數據庫或 Microsoft SQL Server 中的任何重要係統無疑都會使用其中的一些，並且使用這些元素與各自 RDBMS 實現的事務支持的特定設計非常緊密地結合在一起。
請繼續閱讀第 2 部分！