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 部分！