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”）推出的时间还不够长，无法被企业广泛采用。