Share via

將 Oracle 移轉的 SQL 問題發生率降到最低

  • 發行項

本文是系列文章的第五部分 (共七部分)，將提供從 Oracle 遷移到 Azure Synapse Analytics 的指引。 本文著重於將 SQL 問題減到最少的最佳做法。

概觀

Oracle 環境的特性

Oracle 在 1979 年發行的初始資料庫產品是一個商業 SQL 關聯式資料庫，用於線上交易處理 (OLTP) 應用程式，其交易率比今天低很多。 自該初始版本開始，Oracle 環境已發展得更為複雜，並且包含許多功能。 這些功能包括用戶端-伺服器架構、分散式資料庫、平行處理、資料分析、高可用性、資料倉儲、記憶體內部資料技術，以及雲端式執行個體的支援。

提示

Oracle 早在 2000 年代初期就已倡導「資料倉儲設備」概念。

由於維護和升級舊版內部部署 Oracle 環境的成本和複雜度，許多現有的 Oracle 使用者都會想要利用雲端環境所提供的創新做法。 新式雲端環境，例如雲端、IaaS 和 PaaS，都可讓您將基礎結構維護和平台開發等工作委派給雲端提供者。

許多支援對大量資料進行複雜分析 SQL 查詢的資料倉儲都會使用 Oracle 技術。 這些資料倉儲通常會有維度資料模型，例如星形或雪花式結構描述，並針對個別部門使用資料超市。

提示

許多現有的 Oracle 安裝都是使用維度資料模型的資料倉儲。

Oracle 中的 SQL 和維度資料模型組合可簡化以 Azure Synapse 為目標的移轉，因為 SQL 和基本資料模型概念是可轉移的。 Microsoft 建議您依原樣將現有的資料模型移至 Azure，以減少風險、工作量和移轉時間。 雖然您的移轉計畫可能包含基礎資料模型中的變更，例如從 Inmon 模型移至資料保存庫，但一開始依原樣進行移轉是有意義的。 在初始移轉之後，您可以在 Azure 雲端環境中進行變更，以利用其效能、彈性延展性、內建功能和成本效益。

雖然 SQL 語言已標準化，但個別廠商有時會實作專屬延伸模組。 因此，您可能會在移轉期間發現需要在 Azure Synapse 中因應的 SQL 差異。

使用 Azure 設施來實作中繼資料驅動的移轉

您可以透過使用 Azure 環境的功能來自動化和協調移轉程序。 此方法會盡可能減少對現有 Oracle 環境的效能影響，該環境可能已接近執行容量的上限。

Azure Data Factory 是一項雲端式資料整合服務，可支援在雲端建立資料驅動工作流程，以便協調及自動進行資料移動和資料轉換。 您可以使用 Data Factory 建立並排定資料驅動的工作流程 (管線)，以從不同的資料存放區內嵌資料。 透過使用計算服務 (例如，Azure HDInsight Hadoop、Spark、Azure Data Lake Analytics 和 Azure Machine Learning)，Data Factory 可以處理或轉換資料。

Azure 也包含 Azure 資料庫移轉服務，可協助您規劃和執行 Oracle 這類環境的移轉。 適用於 Oracle 的 SQL Server 移轉小幫手 (SSMA) 可以自動化 Oracle 資料庫的移轉，且在某些情況下會包含函式和程序性程式碼。

提示

使用 Azure Data Factory 功能將移轉程序自動化。

當您打算使用 Azure 設施 (例如 Data Factory) 來管理移轉程序時，請先建立中繼資料，列出需要遷移的所有資料表及其位置。

Oracle 和 Azure Synapse 之間的 SQL DDL 差異

ANSI SQL 標準會定義資料定義語言 (DDL) 命令的基本語法。 CREATE TABLE 和 CREATE VIEW 等 DDL 命令常見於 Oracle 和 Azure Synapse，但已擴充為提供實作專屬的功能，例如索引編製、資料表散發和資料分割選項。

提示

SQL DDL 命令 CREATE TABLE 與 CREATE VIEW 具有標準核心元素，但也可用於定義實作特定的選項。

下列各節將討論遷移至 Azure Synapse 期間必須考慮的 Oracle 專屬選項。

資料表/檢視考量

當您在不同環境之間遷移資料表時，通常只有原始資料和描述其實際移轉的中繼資料。 來源系統的其他資料庫元素 (例如索引和記錄檔) 通常不會遷移，因為其在新的環境中可能不必要或會以不同的方式實作。 例如，Oracle CREATE TABLE 語法中的 TEMPORARY 選項，與 Azure Synapse 中在資料表名稱前面加上 # 字元的作法相同。

來源環境中的效能最佳化 (例如索引) 會指出您可以在新目標環境中新增效能最佳化的位置。 例如，如果來源 Oracle 環境內的查詢經常使用位元對應索引，則建議在 Azure Synapse 內建立非叢集索引。 其他原生效能最佳化技術 (如資料表複寫)，可能比直接建立「類似」索引更為適用。 SSMA for Oracle 可提供資料表散發和編製索引的移轉建議。

提示

現有的索引會指出已移轉倉儲中用於編製索引的候選項目。

SQL 檢視定義會包含定義檢視的 SQL 資料操作語言 (DML) 陳述式，通常具有一或多個 SELECT 陳述式。 當您遷移 CREATE VIEW 陳述式時，請考慮 Oracle 與 Azure Synapse 之間的 DML 差異。

不支援的 Oracle 資料庫物件類型

Oracle 特定功能通常可以由 Azure Synapse Analytics 功能取代。 但是，有些 Oracle Database 物件未在 Azure Synapse 受到直接支援。 下列不支援的 Oracle 資料庫物件清單說明如何在 Azure Synapse 中達成對等的功能：

  • 索引選項：在 Oracle 中，有數個索引選項 (例如位元對應索引、函式型索引和網域索引) 在 Azure Synapse 中沒有直接的對等項目。 雖然 Azure Synapse 不支援這些索引類型，但您可以使用使用者定義的索引類型和/或分割來達到類似減少磁碟 I/O 的效果。減少磁碟 I/O 可提高查詢效能。

    您可以藉由查詢系統目錄資料表和檢視，例如 ALL_INDEXES、DBA_INDEXES、USER_INDEXES 和 DBA_IND_COL，找出哪些資料行已編製索引及其索引類型。 或者，您可以在啟用監視時查詢 dba_index_usage 或 v$object_usage 檢視。

    Azure Synapse 功能，例如資料與結果的平行查詢處理和記憶體內部快取，可讓資料倉儲應用程式需要較少的索引來達成絕佳的效能目標。

  • 叢集資料表：Oracle 資料表可以進行組織，因此通常一起存取的資料表資料列 (以通用值為基礎) 在實體上會儲存在一起。 此策略可在擷取資料時減少磁碟 I/O。 Oracle 也有個別資料表的雜湊叢集選項，其會將雜湊值套用至叢集索引鍵，並在實體上將具有相同雜湊值的資料列儲存在一起。

    在 Azure Synapse 中，您可以藉由資料分割和/或使用其他索引來達到類似的結果。

  • 具體化檢視：Oracle 支援具體化檢視，並建議針對具有許多資料行的大型資料表 (查詢中只會定期使用幾個資料行)，使用一個或多個具體化檢視。 當基底資料表的資料已更新時，系統會自動重新整理具體化檢視。

    在 2019 年時，Microsoft 已宣佈 Azure Synapse 將支援與 Oracle 中功能相同的具體化檢視。 具體化檢視現在是 Azure Synapse 中的預覽功能。

  • 資料庫內觸發程序：在 Oracle 中，觸發程序可以設定為在觸發事件發生時自動執行。 觸發事件可以是：

    • DML 陳述式 (例如 INSERT、UPDATE 或 DELETE) 執行。 如果您定義了在客戶資料表上執行 INSERT 陳述式之前引發觸發程序，則該觸發程序會在新的資料列插入客戶資料表之前引發一次。

    • DDL 陳述式 (例如 CREATE 或 ALTER) 執行。 此觸發事件通常用於記錄稽核用途的結構描述變更。

    • 系統事件，例如 Oracle 資料庫的啟動或關機。

    • 使用者事件，例如登入或登出。

    Azure Synapse 不支援 Oracle 資料庫觸發程序。 不過，您可以使用 Data Factory 來達到對等的功能，但這麼做會要求您重構使用觸發程序的程序。

  • 同義字：Oracle 支援將同義字定義為數個資料庫物件類型的替代名稱。 這些類型包括資料表、檢視、序列、程序、預存函式、套件、具體化檢視、Java 類別結構描述物件、使用者定義物件或其他同義字。

    雖然 Azure Synapse 目前不支援定義同義字，但如果 Oracle 中的同義字代表資料表或檢視，則您可以在 Azure Synapse 中定義檢視以符合該替代名稱。 如果 Oracle 中的同義字代表函式或預存程序，則您可以將 Azure Synapse 中的同義字取代為另一個呼叫目標的函式或預存程序。

  • 使用者定義型別：Oracle 支援使用者定義物件，這些物件可以包含一系列個別欄位，且每個欄位都有自己的定義和預設值。 然後這些物件可以在資料表定義內參考，且方式與 NUMBER 或 VARCHAR 等內建資料類型相同。

    Azure Synapse 目前不支援使用者定義型別。 如果您需要遷移的資料包含使用者定義的資料類型，請將其「壓平合併」成傳統資料表定義，或如果是資料陣列，請將其正規化為個別資料表。

SQL DDL 產生

您可以編輯現有的 Oracle CREATE TABLE 和 CREATE VIEW 指令碼，以在 Azure Synapse 中達成對等的定義。 若要這樣做，您可能需要使用修改過的資料類型，並移除或修改 Oracle 特定子句，例如 TABLESPACE。

提示

使用現有的 Oracle 中繼資料，以自動產生 Azure Synapse Analytics 的 CREATE TABLE 與 CREATE VIEW DDL。

在 Oracle 環境中，系統目錄資料表會指定目前的資料表/檢視定義。 與使用者維護的文件不同，系統類別目錄資訊一律是完整的，且與目前的資料表定義同步。 您可以使用 Oracle SQL Developer 之類的公用程式來存取系統類別目錄資訊。 Oracle SQL Developer 可以產生 CREATE TABLE DDL 陳述式，讓您可以編輯並套用至 Azure Synapse 中的對等資料表，如下列螢幕擷取畫面所示。

Screenshot showing the create table statement generated by Oracle SQL Developer.

Oracle SQL Developer 會輸出下列 CREATE TABLE 陳述式，其中包含您應該移除的 Oracle 特定子句。 在 Azure Synapse 上執行修改過的 CREATE TABLE 陳述式之前，請先對應任何不支援的資料類型。

Screenshot showing the Quick DDL menu option in Oracle SQL Developer.

或者，您可以使用 SQL 查詢、SSMA 或協力廠商移轉工具，從 Oracle 目錄資料表內的資訊自動產生 CREATE TABLE 陳述式。 這種方法是為許多資料表產生 CREATE TABLE 陳述式最快速、最一致的方式。

提示

協力廠商工具和服務可以自動化資料對應工作。

協力廠商會提供工具和服務來自動化移轉，包括資料類型的對應。 若您已在 Oracle 環境中使用協力廠商 ETL 工具，請使用此工具來實作所有必要的資料轉換。

Oracle 和 Azure Synapse 之間的 SQL DML 差異

ANSI SQL 標準會定義 DML 命令的基本語法，例如 SELECT、INSERT、UPDATE 與 DELETE。 雖然 Oracle 和 Azure Synapse 都支援 DDL 命令，但在某些情況下會以不同的方式實作相同命令。

提示

標準 SQL DML 命令 SELECT、INSERT 和 UPDATE 在不同的資料庫環境中可能有其他語法選項。

下列各節將討論遷移至 Azure Synapse 期間必須考慮的 Oracle 專屬 DML 命令。

SQL DML 語法差異

Oracle SQL 與 Azure Synapse T-SQL 之間存在一些 SQL DML 語法差異：

  • DUAL 資料表：Oracle 有一個名為 DUAL 的系統資料表，其組成為一個名為 dummy 資料行，以及一筆具有 X 值的記錄。 當查詢因為語法原因而需要資料表名稱，但不需要資料表內容時，就會使用 DUAL 系統資料表。

    使用資料表的 DUAL Oracle 查詢範例為 SELECT sysdate from dual;。 Azure Synapse 對等項目為 SELECT GETDATE();。 若要簡化 DML 的移轉，您可以使用下列 DDL 在 Azure Synapse 中建立對等的 DUAL 資料表。

    CREATE TABLE DUAL
(
  DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

  • NULL 值：Oracle 中的 NULL 值是空字串，由長度為 0 的 CHAR 或 VARCHAR 字串類型表示。 在 Azure Synapse 和其他大部分的資料庫中，NULL 表示其他。 遷移資料時，或遷移處理或儲存資料的程序時，請務必仔細確認 NULL 值會經過一致的處理。

  • Oracle 外部聯結語法：雖然較新版本的 Oracle 支援 ANSI 外部聯結語法，但舊版 Oracle 系統會針對在 SQL 陳述式中使用加號 (+) 的外部聯結使用專屬語法。 如果您要遷移較舊的 Oracle 環境，可能會遇到較舊的語法。 例如：

    SELECT
    d.deptno, e.job
FROM
    dept d,
    emp e
WHERE
    d.deptno = e.deptno (+)
    AND e.job (+) = 'CLERK'
GROUP BY
    d.deptno, e.job;

    對等的 ANSI 標準語法為：

    SELECT
    d.deptno, e.job
FROM
    dept d
    LEFT OUTER JOIN emp e ON d.deptno = e.deptno
    and e.job = 'CLERK'
GROUP BY
    d.deptno,
    e.job
ORDER BY
    d.deptno, e.job;

  • DATE 資料：在 Oracle 中，DATE 資料類型可以儲存日期和時間。 Azure Synapse 會將日期和時間儲存在不同的 DATE、TIME 和 DATETIME 資料類型中。 當您遷移 Oracle DATE 資料行時，請檢查這些資料行是否同時儲存日期和時間或只儲存日期。 如果只儲存日期，則將資料行對應至 DATE，否則請對應至 DATETIME。

  • DATE 算術：Oracle 支援從另一個日期中減去一個日期，例如 SELECT date '2018-12-31' - date '2018-1201' from dual;。 在 Azure Synapse 中，您可以使用 DATEDIFF() 函式來減去日期，例如 SELECT DATEDIFF(day, '2018-12-01', '2018-12-31');。

    Oracle 可以從日期中減去整數，例如 SELECT hire_date, (hire_date-1) FROM employees;。 在 Azure Synapse 中，您可以使用 DATEADD() 函式，在日期中加入或減去整數。

  • 透過檢視更新：在 Oracle 中，您可以針對檢視執行插入、更新和刪除作業來更新基礎資料表。 在 Azure Synapse 中，您會針對基底資料表執行這些作業，而不是針對檢視。 如果 Oracle 資料表透過檢視更新，您可能必須重新設計 ETL 處理。

  • 內建函式：下表顯示某些內建函式的語法和使用方式差異。

Oracle 函式 描述 Synapse 對等項目
ADD_MONTHS 新增指定的月份數目 DATEADD
CAST 將一個內建資料類型轉換成另一個 CAST
DECODE 評估條件清單 CASE 運算式
EMPTY_BLOB 建立空的 BLOB 值 0x 常數 (空的二進位字串)
EMPTY_CLOB 建立空的 CLOB 或 NCLOB 值 '' (空字串)
INITCAP 將每個字的第一個字母變成大寫 使用者定義函數
INSTR 尋找字串中子字串的位置 CHARINDEX
LAST_DAY 取得月份的最後一個日期 EOMONTH
LENGTH 取得字串長度 (以字元數為單位) LEN
LPAD 向左填補字串到指定的長度 使用 REPLICATE、RIGHT 和 LEFT 的運算式
MOD 取得一個數字除以另一個數字的餘數 % 運算子
MONTHS_BETWEEN 取得兩個日期之間的月份數目 DATEDIFF
NVL 將 NULL 取代為運算式 ISNULL
SUBSTR 從字串中傳回子字串 SUBSTRING
日期時間的 TO_CHAR 將日期時間轉換為字串 CONVERT
TO_DATE 將字串轉換成日期時間 CONVERT
TRANSLATE 一對一單一字元替代 使用 REPLACE 或使用者定義函式的運算式
TRIM 修剪前置或後置字元 LTRIM 和 RTRIM
日期時間的 TRUNC 截斷日期時間 使用 CONVERT 的運算式
UNISTR 將 Unicode 字碼元素轉換成字元 使用 NCHAR 的運算式

函數、預存程序及序列

從 Oracle 之類的成熟環境遷移資料倉儲時，您可能需要遷移簡單資料表和檢視以外的元素。 檢查 Azure 環境中的工具是否可以取代函式、預存程序和序列的功能，因為比起重新編碼 Oracle 函式，使用內建 Azure 工具通常更有效率。

在準備階段中，請建立需要遷移的物件詳細目錄、定義處理這些物件的方法，並在移轉計畫中配置適當的資源。

Microsoft 工具 (例如 SSMA for Oracle 和 Azure 資料庫移轉服務) 或 協力廠商的移轉產品和服務，可以將函式、預存程序和序列的移轉自動化。

提示

協力廠商產品和服務可以將非資料元素的移轉自動化。

下列各節會討論函式、預存程序和序列的移轉。

函式

與大多數資料庫產品相同，Oracle 支援 SQL 實作中的系統函式和使用者定義函式。 當您將舊版資料庫平台遷移至 Azure Synapse 時，您通常可以在不需要變更的情況下遷移一般系統函式。 某些系統函式的語法可能稍有不同，但您可以將必要的變更自動化。

針對在 Azure Synapse 中沒有對等項目的 Oracle 系統函式或任意使用者定義函式，請使用目標環境語言重新編碼這些函式。 Oracle 使用者定義函式會以 PL/SQL、Java 或 C 撰寫程式碼。Azure Synapse 會使用 Transact-SQL 語言來實作使用者定義函式。

預存程序

大部分的新式資料庫產品，都支援在資料庫中儲存程序。 Oracle 為此目的提供了 PL/SQL 語言。 預存程序通常包含 SQL 陳述式和程序邏輯，並且會傳回資料或狀態。

Azure Synapse 支援使用 T-SQL 的預存程序，因此您必須以 T-SQL 重新編碼任何已遷移的預存程序。

序列

在 Oracle 中，序列是使用 CREATE SEQUENCE 所建立的具名資料庫物件。 序列透過 CURRVAL 和 NEXTVAL 方法提供唯一的數值。 您可以使用產生的唯一數字，作為主索引鍵值的代理索引鍵值。 Azure Synapse 不會實作 CREATE SEQUENCE，但您可以使用 IDENTITY 資料行或 SQL 程式碼來實作序列，以產生數列中的下一個序號。

使用 EXPLAIN 來驗證舊版 SQL

提示

使用來自現有系統查詢記錄的實際查詢來尋找潛在的移轉問題。

假設 Azure Synapse 中具有資料表和資料行名稱相同的「類似」移轉資料模型，其中一種測試舊版 Oracle SQL 是否與 Azure Synapse 相容的方式為：

  1. 從舊版系統的查詢歷程記錄中擷取一些代表性 SQL 陳述式。
  2. 在這些查詢前面加上 EXPLAIN 陳述式。
  3. 在 Azure Synapse 中執行 EXPLAIN 陳述式。

任何不相容的 SQL 皆會產生錯誤，而該錯誤資訊可用來判斷重新編碼工作的規模。 此方法不需要您將任何資料載入 Azure 環境，您只需要建立相關的資料表和檢視。

摘要

現有的舊版 Oracle 安裝實作方式通常會讓遷移至 Azure Synapse 的作業變得相對簡單。 這兩個環境都會針對大量資料使用 SQL 進行分析查詢，而且通常會採用某種形式的維度資料模型。 這些因素讓 Oracle 安裝成為遷移至 Azure Synapse 的合適候選項目。

總而言之，我們提供下列建議來最小化將 SQL 程式碼從 Oracle 遷移至 Azure Synapse 的工作量：

  • 依原樣遷移現有的資料模型，以將風險、工作量和移轉時間降到最低，即使已規劃不同的資料模型 (例如資料保存庫) 也一樣。

  • 了解 Oracle SQL 實作與 Azure Synapse 實作之間的差異。

  • 使用現有 Oracle 實作中的中繼資料和查詢記錄，來評估變更環境的影響。 規劃可因應差異的方法。

  • 自動化移轉程序，以將風險、工作量和移轉時間降到最低。 您可以使用 Microsoft 工具，例如 Azure 資料庫移轉服務和 SSMA。

  • 請考慮使用專業的協力廠商工具和服務來簡化移轉。

下一步

若要深入了解 Microsoft 和協力廠商工具，請參閱本系列中的下一篇文章：將 Oracle 資料倉儲遷移至 Azure Synapse Analytics 的工具。