你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

最大程度减少 Oracle 迁移中的 SQL 问题

项目
06/01/2023

本文是一个包含七部分内容的系列的第五部分，该系列提供有关如何从 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 语言已标准化，但各个供应商有时会实现专有扩展。因此，在迁移过程中可能会发现 SQL 差异，这些差异需要 Azure Synapse 的解决方法。

使用 Azure 设施实现元数据驱动的迁移

可以使用 Azure 环境的功能来自动执行和协调迁移进程。此方法将对现有 Oracle 环境的性能影响降至最低，尤其是在该环境已接近运行容量上限时。

Azure 数据工厂是一种基于云的数据集成服务，支持在云中创建数据驱动的工作流，以协调和自动执行数据移动和数据转换。可以使用数据工厂创建和计划数据驱动工作流（管道），这些工作流从不同的数据存储中引入数据。数据工厂可使用如 Azure HDInsight Hadoop、Spark、Azure Data Lake Analytics 和 Azure 机器学习等计算服务处理和转换数据。

Azure 还包括 Azure 数据库迁移服务，可帮助你规划和执行从 Oracle 等环境进行的迁移。适用于 Oracle 的 SQL Server 迁移助手 (SSMA) 可以自动迁移 Oracle 数据库，某些情况下包括函数和程序代码迁移。

提示

使用 Azure 数据工厂功能自动执行迁移过程。

在计划使用 Azure 设施（如数据工厂）来管理迁移进程时，请先创建列出所有要迁移的数据表及其位置的元数据。

Oracle 与 Azure Synapse 之间的 SQL DDL 差异

ANSI SQL 标准定义了数据定义语言 (DDL) 命令的基本语法。某些 DDL 命令（例如 CREATE TABLE 和 CREATE VIEW）在 Oracle 和 Azure Synapse 中都很常见，但这些命令也进行了扩展，提供特定于实现的功能，例如索引、表分布和分区选项。

提示

SQL DDL 命令 CREATE TABLE 和 CREATE VIEW 具有标准核心元素，但也用于定义特定于实现的选项。

以下部分讨论了在迁移到 Azure Synapse 期间需要考虑的特定于 Oracle 的选项。

表/视图注意事项

在不同环境之间迁移表时，通常只有原始数据和描述它的元数据进行物理迁移。源系统中的其他数据库元素（例如索引和日志文件）通常不会迁移，因为在新环境中它们可能是不必要的或实现方式不同。例如，Oracle 的 CREATE TABLE 语法中的 TEMPORARY 选项等效于在 Azure Synapse 中为表名添加 # 字符作为前缀。

源环境中的性能优化（例如索引）体现了可以在新目标环境中添加性能优化的位置。例如，如果源 Oracle 环境中的查询中经常使用位图索引，则表明应在 Azure Synapse 中创建非聚集索引。其他原生性能优化技术（如表复制）可能比直接创建“同类”索引更适用。适用于 Oracle 的 SSMA 可提供针对表分布和索引的迁移建议。

提示

现有索引指示已迁移的仓库中的候选索引。

SQL 视图定义包含用于定义视图的 SQL 数据操作语言 (DML) 语句，通常包含一个或多个 SELECT 语句。迁移 CREATE VIEW 语句时，考虑 Oracle 与 Azure Synapse 之间的 DML 差异。

不支持的 Oracle 数据库对象类型

通常可以使用 Azure Synapse 功能替换特定于 Oracle 的功能。但 Azure Synapse 不直接支持某些 Oracle 数据库对象。以下不受支持的 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 数据库触发器。但你可以使用数据工厂实现等效功能，不过这样做需要重构使用触发器的进程。
同义词：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 生成 CREATE TABLE 和 CREATE VIEW DDL。

在 Oracle 环境中，系统目录表指定当前表/视图定义。与用户维护的文档不同，系统目录信息始终是完整的并与当前表定义同步。可以使用 Oracle SQL Developer 等实用工具访问系统目录信息。 Oracle SQL Developer 可生成 CREATE TABLE DDL 语句，你可编辑这些语句，以应用于 Azure Synapse 中的等效表，如以下屏幕截图所示。

Oracle SQL Developer 输出以下 CREATE TABLE 语句，其中包含应移除的特定于 Oracle 的子句。在 Azure Synapse 上运行修改过的 CREATE TABLE 语句之前，请映射所有不受支持的数据类型。

屏幕截图显示 Oracle SQL Developer 中的快速 DDL 菜单选项。

或者，可以使用 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 函数	说明	Azure 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 环境中的工具是否可以替换其功能，因为使用内置 Azure 工具通常比重新编码 Oracle 函数更高效。

作为准备阶段的一部分，请创建需要迁移的对象清单，定义处理它们的方法，并在迁移计划中分配适当的资源。

Microsoft 工具（如适用于 Oracle 的 SSMA 和 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 是否兼容的一种方法是：

从旧版系统查询历史记录日志捕获一些有代表性的 SQL 语句。
用 EXPLAIN 语句为这些查询添加前缀。
在 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 的工具。

通过