从Oracle到SQL Server和从SQL Server到Oracle的迁移指南（2）

本文概述

• SQL Server中Oracle和Identity列中的序列
• Microsoft SQL Server中的筛选索引
• Oracle数据库中的筛选索引如何处理？
• 从SQL Server到Oracle /从Oracle到SQL Server的迁移挑战神秘化

本系列的第一部分讨论了Oracle数据库和Microsoft SQL Server在事务实现方面的差异, 重点是从Oracle到SQL Server迁移期间可能遇到的陷阱, 反之亦然。下一部分将介绍许多常用的SQL语法元素, 这些元素在Oracle-SQL Server划分中不匹配, 或者含义或用法完全不同。

SQL Server中Oracle和Identity列中的序列

数据库社区在两个阵营之间存在着长期的分歧：自然密钥的爱国者和人工(或”替代”)密钥的支持者。

我本人为自然键辩护, 但经常发现自己出于某种原因创建代理。除了讨论的实质之外, 让我们看一下生成人工密钥的标准机制：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时, 应基于IDENTITY列来实现Oracle中所有基于ROWID(例如重复数据删除)的设计模式。

从使用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”)的发布时间还不够长, 无法被企业广泛采用。