从Oracle到SQL Server和从SQL Server到Oracle的迁移指南（1） _数据库迁移

本文概述

了解事务结构：从万英尺观察Oracle与SQL Server
Oracle和SQL Server中的临时表
内部事务(“ 嵌套事务” )
其他因素
临时表, 表/集合变量和嵌套事务：前3个迁移痛点

“ 供应商依赖性” 对于许多企业高管来说是一个可怕的词。另一方面, 业内已经广为人知, 无法实现完全的” 供应商独立性” 。对于数据库来说尤其如此。
数量最多的企业RDBMS平台是Oracle数据库和Microsoft SQL Server(为简便起见, 在本文的其余部分中, 我将它们分别称为” Oracle” 和” SQL Server” )。当然, IBM Db2在不断缩小的大型机平台上与Oracle竞争, 但在许多领域仍然至关重要。迅速发展的开源替代产品(例如PostgreSQL)在中低端商品硬件和Web上的动态环境中获得了坚实的立足点。
但是, 当企业组织需要新的RDBMS时, 许多企业高管将面临Oracle与SQL Server的选择。最终的选择基于多个因素：许可证成本, 可用的内部专业知识和过去的经验, 与现有环境的兼容性, 合作伙伴关系, 未来的业务计划等。但是, 即使进行了最彻底的前期评估和最佳教育的决策, 有时也会有一些因素更改, 然后平台也需要更改。我之所以知道这一点, 是因为在我的职业生涯中, 我已经进行了两次这样的迁移, 一次准备了过渡可行性评估, 并且现在我正在从事跨平台功能的迁移。
Oracle和SQL Server都是” 老派” , 部分兼容ANSI的RDBMS实现。撇开过程扩展(PL / SQL和Transact-SQL具有不同的语法, 但通常在它们之间进行转换)和较新的面向对象的将来相比, SQL代码在外观上看起来很相似。这是一个危险的蜂蜜陷阱。
在Oracle和SQL Server之间(在任一方向上)进行任何迁移项目的两个最关键点是事务, 以及密切相关的临时表, 它们是解决事务范围的关键工具。我们还将介绍嵌套事务, 因为它们是另一事务范围内的事务, 因为它们是在Oracle中实施用户安全审核的关键部分。但是在SQL Server中, 由于在这种情况下它的COMMIT行为, 用户安全审核需要一种不同的方法。
了解事务结构：从万英尺观察Oracle与SQL ServerOracle事务是隐式的。这意味着你无需开始事务-你一直在进行事务。并且此事务是开放的, 直到你发出commit或rollback语句。是的, 你可以显式启动事务, 定义回滚安全点并设置内部/嵌套事务；但是重要的是, 你永远不会” 不在事务中” , 并且必须始终执行提交或回滚。还要注意, 发出数据定义语言(DDL)语句(CREATE, ALTER等；在事务中可以通过动态SQL完成)将提交发出该数据的语言的事务。
与Oracle不同, SQL Server具有显式事务。这意味着, 除非你显式启动事务, 否则所有更改将” 自动” 提交-在处理语句之后立即执行, 因为每个DML语句(INSERT, UPDATE, DELETE)都会自行创建一个事务并提交它, 除非出错出来。
这是数据存储实现方式不同的结果, 即数据如何写入数据库以及数据库引擎如何读取数据。
在Oracle中, DML语句直接在数据文件中更改记录。记录的旧副本(如果是INSERT, 则为空记录替换)被写入当前回滚文件, 并且更改的确切时间标记在记录上。
发出SELECT语句后, 将根据发出前已修改的数据进行处理。如果在发出SELECT之后修改了任何记录, 则Oracle使用回滚文件中的旧版本。
这就是Oracle实现读取一致性和非阻塞读取/写入的方式。这也是为什么在非常活跃的事务数据库上长时间运行的查询有时会遇到臭名昭著的错误ORA-01555, 快照太旧：回滚段… 太小。 (这意味着该记录的较早版本的查询所需要的回滚文件已经被重用。)这就是为什么对” 我的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中而不是以前在18c中添加本地(专用)临时表。
简而言之, 我看不到无法使用相同或更好的全局临时表来实现的Oracle私有临时表的用例。因此, 对于任何严重的转换, 我们需要了解Oracle和SQL Server全局临时表之间的区别。
Oracle和SQL Server中的全局临时表
Oracle全局临时表是在设计时由DDL语句显式创建的永久数据字典对象。仅当它是数据库级对象时才是” 全局” 的, 并且任何具有所需权限的数据库会话都可以访问它。但是, 尽管全局结构的结构是全局的, 但全局临时表中的所有数据的作用域仅限于它在其中运行的会话, 并且在任何情况下在该会话外部都不可见。换句话说, 其他会话可以在同一全局临时表的自己的副本中拥有自己的数据。因此, 在Oracle中, 全局临时表保存了会话本地数据-大多数在PL / SQL中使用, 用于简化代码和优化性能。
在SQL Server中, 全局临时表是在Transact-SQL代码块中创建的临时对象。只要创建会话处于打开状态, 它就存在, 并且对数据库中的其他会话可见(无论是结构还是数据)。因此, 它是用于跨会话共享数据的全局临时对象。
【从Oracle到SQL Server和从SQL Server到Oracle的迁移指南（1）】SQL Server中的本地临时表与全局临时表不同, 它仅在创建它的会话中可以访问。与使用全局临时表相比, SQL Server中本地临时表的使用更加广泛(并且, 对数据库性能更关键)。
那么, SQL Server中如何使用本地临时表, 以及如何将它们转换为Oracle？
SQL Server中本地临时表的关键(正确)用法是缩短或删除事务资源锁定, 尤其是：

当需要通过某种汇总处理一组记录时
何时需要分析和修改数据集
需要在同一范围内多次使用同一组数据时

在这种情况下, 将记录集选择到本地临时表中以从源表中删除锁定通常是一个更好的解决方案。
值得注意的是, SQL Server中的常用表表达式(CTE, 即WITH < alias> AS(SELECT … )语句)仅是” 语法糖” 。在执行SQL之前, 它们会转换为内联子查询。 Oracle CTE(带有/ * +实例化* /提示)经过性能优化, 并创建了实例化视图的临时版本。在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优化, 所以我希望这种方式是这样的：表变量在内存中, 而临时表在磁盘上。但是我发现直到2014年的Microsoft SQL Server版本都没有在内存中存储表变量。因此, 对临时变量进行全表扫描实际上就是对磁盘进行全表扫描。幸运的是, SQL Server 2017和更高版本支持对临时表和表变量进行声明式内存优化。
那么, 如果使用临时表可以做的更好或者更好, 那么Transact-SQL中表变量的用例是什么？表变量的键属性是变量, 因此不受事务回滚的影响, 可以作为参数传递。
Transact-SQL函数的限制非常严格：由于函数的任务是返回一些奇异的返回值, 因此按设计, 它不会产生副作用。 Transact-SQL甚至将SELECT视为副作用, 因为在SQL Server中, 对表的任何访问都会创建隐式事务和关联的事务锁。这意味着在函数内部, 我们无法访问现有临时表中的数据, 也无法创建临时表。结果, 如果我们需要将任何记录集传递给函数, 则必须使用表变量。
Oracle使用(全局)临时表和集合变量(与Transact-SQL表变量的Oracle PL / SQL等效)的注意事项有所不同。 Oracle集合变量在内存中, 而临时表位于临时表空间中。 Oracle函数允许对永久或临时表的只读访问；在Oracle中进行简单的SELECT绝不会锁定资源。
在Oracle中, 使用收集变量还是临时表的选择是基于预期的数据量, 需要保留此数据的持续时间以及内存与磁盘的分配和可用性。同样, 集合变量是将行集作为输出返回到主机程序的标准方法。
由于大多数SQL语法元素在SQL Server和Oracle之间看起来非常相似, 因此将具有表变量的代码块从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实现的事务支持的特定设计紧密结合。
在第二部分继续阅读！