Oracle 到 SQL Server 和 SQL Server 到 Oracle 遷移指南 - Pt。 2

已發表: 2022-03-11

本系列的第一部分討論了 Oracle 數據庫和 Microsoft SQL Server 在事務實現方面的差異，重點介紹了在 Oracle 到 SQL Server 遷移過程中可能遇到的陷阱，反之亦然。下一部分將介紹一些常用的 SQL 語法元素，這些元素在 Oracle-SQL Server 的鴻溝中不匹配或具有完全不同的含義或用法。

Oracle 中的序列和 SQL Server 中的標識列

在數據庫社區中存在兩個陣營之間的長期分歧：自然密鑰的支持者和人工（或“代理”）密鑰的支持者。

我自己捍衛自然鍵，但經常發現自己出於某種原因創建代理。但拋開這場爭論的實質，讓我們看看生成人工密鑰的標準機制：Oracle 序列和 SQL Server 標識列。

Oracle 序列是一流的數據庫級對象。相反，SQL Server 標識列是列類型，而不是對象。

當使用 Oracle 序列生成表鍵（通常是主鍵）時，它可以保證遞增，因此是唯一的。但不保證是連續的。事實上，即使在設計良好的實現中，也很可能存在一些差距。因此，任何 Oracle 實現都不應該依賴序列生成的值是連續的。

此外，序列是通過 Oracle 數據庫的數據字典管理的，因此創建一個專用序列來支持每個代理鍵將太耗費資源（並且很麻煩）。單個序列對象可以支持多個甚至所有代理鍵。

另一方面，當多個進程需要從一個序列中訪問NEXTVAL （下一個增量值）時，該序列將成為一個關鍵的、單次訪問的資源。它將有效地使所有訪問它的進程嚴格按順序進行，將任何多線程（單服務器或多服務器）實現轉變為單線程進程，等待時間長，內存/CPU使用率高。

這樣的實現確實發生了。這個問題的解決方案是用合理的緩存值定義有問題的序列對象——這意味著一個定義的值範圍（無論是 100 還是 10 萬）被選擇到一個調用進程的緩存中，並記錄在使用的數據字典中，並且可用於此特定進程，而無需每次調用NEXTVAL時訪問數據字典。

但這正是為什麼會創建間隙的原因，因為並非所有緩存值都可能被使用。這也意味著在並行會話中的多個進程中，一些記錄的序列值可以按時間順序倒置。除非序列值被重置或後移，否則這種反轉不會在單個進程中發生。但這最後一種情況相當於自找麻煩：它應該是不必要的，如果實施不正確，可能會導致生成重複值。

因此，使用 Oracle 序列的唯一正確方法是生成代理鍵：唯一但不假定包含任何其他可靠可用信息的鍵。

SQL Server 中的標識列

SQL Server 呢？雖然 SQL Server 2012 中引入了與 Oracle 對應的序列具有非常相似的功能和實現，但它們並不是一流的首選技術。與其他新增功能一樣，它們對於從 Oracle 進行轉換是有意義的，但是在 SQL Server 上從頭開始實施代理鍵時， IDENTITY是一個更好的選擇。

IDENTITY是表的“子”對象。它不訪問表外的任何資源，並且保證是連續的，除非被故意操縱。它是專門為這項任務而設計的，而不是為了與 Oracle 的語義兼容。

Oracle 在 12.1 版本中實現了IDENTITY功能，很自然地想知道以前沒有它是怎麼做到的，為什麼現在實現它，以及為什麼 SQL Server 從一開始就需要它（從它的 Sybase SQL Server 起源）。

原因是 Oracle 總是有一個身份關鍵特性： ROWID偽列，具有ROWID或UROWID數據類型。該值是非數字的（ ROWID和UROWID是專有的 Oracle 數據類型）並且唯一標識數據記錄。

與 SQL Server 的IDENTITY不同，Oracle 的ROWID不容易操作（可以查詢，但不能插入或修改），它是在後台為每個 Oracle 表中的每一行創建的。此外，訪問 Oracle 數據庫中任何數據行的最有效方法是通過其ROWID ，因此它被用作性能優化技術。最後，它定義了默認的查詢輸出排序順序，因為它有效地索引了行數據的低級存儲。

如果 Oracle 的ROWID如此重要，那麼 SQL Server 是如何在沒有它的情況下存活這麼多年的？通過使用IDENTITY列作為主（代理）鍵。

重要的是要注意 Oracle 和 SQL Server 在索引結構實現方面的差異。

在 SQL Server 中，第一個索引（通常是主鍵）是聚集的；這意味著最常見的情況是，主數據文件中的數據按此鍵排序。在 Oracle 方面，聚集索引的等價物是索引組織表。這是 Oracle 中的一個可選結構，僅在需要時偶爾使用——例如，用於只讀查找表。

在遷移到 SQL Server 時，Oracle 中所有基於使用ROWID的設計模式（例如重複數據刪除）都應該基於IDENTITY列來實現。

雖然從在 SQL Server 上使用IDENTITY遷移到在 Oracle 上使用IDENTITY可以生成功能正確的代碼，但這並不是最佳的，因為在 Oracle 方面， ROWID的執行效率會更高。

在進行簡單的 SQL 語法轉換以將 Oracle 序列移動到 SQL Server 時也是如此：代碼將運行，但就代碼簡單性和性能而言，使用IDENTITY是首選選項。

Microsoft SQL Server 中的篩選索引

多年前，Microsoft SQL Server 2008 引入了許多重要功能，使其成為真正一流的企業數據庫。過濾索引不止一次地拯救了我的一天。

過濾索引是具有WHERE子句的非聚集索引（即作為其自己的數據文件存在的索引）。這意味著索引文件只包含與子句相關的數據記錄。為了充分利用過濾索引，它還應該有一個INCLUDE子句，列出返回數據集時所需的所有列。當您的查詢被優化為使用包含所有需要的數據點的特定過濾索引時，數據庫引擎只需要訪問一個（小）索引文件，甚至無需查看主表數據文件。

幾年前，當我使用 TB 大小的表時，這對我來說特別有價值。有問題的客戶經常只需要訪問在任何給定時間活躍的記錄的一小部分。這種訪問的初始實現（由最終用戶 UI 操作觸發）不僅速度慢得令人痛苦，而且根本無法使用。當我添加一個帶有所需INCLUDE的過濾索引時，它變成了一個亞毫秒級的搜索。我花在這個優化任務上的時間只有一個小時。

當然，過濾索引有一些限制。它們不能包含 LOB 列，對索引本身可以包含的WHERE子句的條件有限制，並且它們會增加數據庫的存儲空間。但是如果用例適合這些參數，與過濾索引可以提供的顯著性能提升相比，存儲權衡通常非常小。

Oracle 數據庫中的過濾索引怎麼樣？

後來，我發現自己在一家財富 500 強公司的大型團隊中擔任 SQL Server 到 Oracle 遷移項目的開發人員/DBA。圍繞源數據庫（SQL Server 2008）的代碼執行不佳，性能不佳，轉換勢在必行：每天的後端同步作業運行時間超過 23 小時。它沒有過濾索引，但在新的 Oracle 11g 系統中，我看到了過濾索引非常有用的多個案例。但是Oracle 11g 沒有過濾索引！

最新的 Oracle 18c 中也沒有實現過濾索引。

但作為技術專家，我們的任務是充分利用我們所擁有的。所以我在我的 Oracle 11g 系統中實現了過濾索引的等價物（以及我稍後在 12c 中使用的相同技術）。這個想法是基於 Oracle 如何處理NULL的，並且可以在任何版本的 Oracle 中使用。

Oracle 不會以與常規數據相同的方式處理NULL值。 Oracle 中的NULL什麼都不是——它不存在。因此，如果您將索引列定義為NULLABLE並且您正在按非NULL值進行搜索，那麼您的索引數據文件將只包含感興趣的記錄。由於 Oracle 索引定義沒有INCLUDE子句，因此您需要創建一個複合索引，其中包含需要包含在結果集中的所有列。（與 SQL Server 的INCLUDE子句相比，這種技術有一些開銷，但它相當微不足道。）

這種變通方法實現確實增加了一個限制：前導索引列必須允許NULL ，因此不能是表的主鍵。但是，它可以是專門為支持此性能優化方法而創建的派生或計算列。從某種意義上說，索引的前導列在邏輯上是二元的：搜索中包含的數據為非NULL值，任何應該“不可見”的數據為NULL 。

將 SQL Server 過濾索引邏輯遷移到 Oracle 的另一個可能選項是將索引（或完整的表）實現為分區。在這種情況下，數據庫引擎將只訪問相關的索引分區——前提是通過在WHERE子句中使用準確的分區條件正確實現了查詢。

即使在規模上，這在相對靜態的數據上也能很好地工作，但如果應用於頻繁變化的數據，可能會給 DBA 團隊帶來高維護負擔。例如，在以時間為中心的應用程序中優化對當今數據的訪問時：DBA 團隊需要每天重新定義分區。雖然這種重新定義可以在夜間維護工作中編寫腳本，但它確實使您的系統更加複雜並引入了新的潛在系統故障點。

因此，每當需要將 SQL Server 過濾索引邏輯遷移到 Oracle 時，都需要非常具體和小心。

如何處理轉換

通過 Oracle 到 SQL Server 的遷移，尋找使用過濾索引進行優化的機會。您不會在 Oracle 中看到過濾索引，但您可能會看到包含NULL值的索引。不要照原樣複製它們：這可能是您在轉換中獲得性能提升和設計改進的最佳場所。

對於 SQL Server 到 Oracle 的遷移，如果您看到過濾索引，請尋找如何避免相應 Oracle 代碼中的性能瓶頸。了解如何重新設計數據流以彌補過濾索引在源實現中提供的性能提升缺失。

SQL Server 到 Oracle / Oracle 到 SQL Server 遷移挑戰揭秘

對於 Oracle 和 SQL Server 之間任一方向的遷移項目，深入了解所涉及的機制非常重要。當各自數據庫（Oracle 18c 和 Microsoft SQL Server 2017*）的當前版本包含彼此功能的詞彙等效項時（例如，在序列和身份方面），這似乎很容易獲勝。但是，將一個 RDBMS 上的良好設計直接複製到另一個 RDBMS 會導致不必要的複雜和性能不佳的代碼。

在本系列的下一部分和最後一部分中，我將介紹讀取一致性和遷移工具的使用。敬請關注！

* SQL Server 2019（或“15.x”）推出的時間還不夠長，無法被企業廣泛採用。