Vincent的博客

在数据模型中，当两个表存在"父子"关系时，即可以定义外键约束，这种关系限制一个表中的数据需要参考另一个表中已存在的数据，其中引用的表称为"子表"，被引用的表称为"父表"，引用关系通过在子表上创建外键约束来实现。

在Oracle的逻辑存储结构中（表空间-段-区-块），数据是存在数据段中的，通常一个表就是一个数据段，而段最终又由许多数据块组成。当数据存入数据块时，需要对块进行格式化，高水位线（High Water Mark, HWM）是段中的一个点，在这个点之上的所有数据块都是未格式化且未使用的。

当会话通过客户端连接到Oracle数据库时，根据服务器处理请求方式的不同，可以有两种连接模式，一种称为专用服务器连接（Dedicated Server），另外一种称为共享服务器连接（Shared Server）。

在数据归档、备份、测试等场景，我们经常需要将数据从一个系统移动到另一个系统，一个较常用的方案是数据的导出/导入（export/import），但是在数据量较大的场景，此方案可能比较耗时。而可传输表空间是一种以文件为单位的数据移动技术，它绕过了导出/导入的数据处理过程，直接利用操作系统复制底层数据文件来快速移动数据。

Oracle数据库在与独立I/O子系统交互时，假如Oracle发出的写磁盘命令，I/O子系统也返回成功写磁盘的消息（数据此时可能依然在I/O缓存中），如果在I/O系统实际写盘之前Oracle再次读取该数据，则I/O系统可能会返回旧版本的数据（写操作丢失了）。Oracle引入了写丢失保护机制，在读数据时校验数据块的版本，以检测是否出现写丢失。

Oracle临时表空间（Temporary Tablespace）主要用来存储数据库运行中产生的临时对象，例如SQL排序结果集，临时表等，这些对象的生存周期只有会话。本文总结了Oralce中涉及临时表空间的管理和优化操作。

如果一张表中的数据存在分级（即数据间存在父子关系），利用普通SQL语句显示数据间的层级关系非常复杂，可能需要多次连接才能完整的展示出完成的层级关系，更困难的是你可能不知道数据到底有多少层。而利用Oracle的层级查询，则可以很方便的显示出层级。

默认情况下，Oracle只有一个缓冲池 - Buffer Cache，其可以满足基本数据缓存需求。但某些数据的访问模式可能与普通数据不同，对于访问非常频繁的数据和很少访问的数据（两种极端），Oracle可以支持配置两个独立的缓冲池来存放该类数据，特殊的缓冲池针对两种访问模式做了优化，以提升系统性能。

直接路径插入（Direct Path Insert）是Oracle一种数据加载提速技术，可以在使用insert语句或SQL*Loader工具大批量加载数据时使用。直接路径插入处理策略与普通insert语句完全不同，Oracle会通过牺牲空间，安全性，并发性能来换取加载速度。

表是数据库中负责数据存储的对象，在RDBMS中，数据以行、列的形式存储在表中。Oracle中表有很多种类型，最基础且应用最常用的类型就是堆表（Heap-Organized Table），本文列举了Oracle堆表的常用管理操作。

在Oracle中，常规的DML语句只能完成单一功能，，例如insert/delete/update只能三选一，而merge into语句可以同时对一张表进行更新/插入/删除。

多表插入非常适合数据整理分配场景，如果利用单表插入，我们需要对每个条件查询一次源表并插入目标表，而多表插入可以直接定义多个条件，一次查询即可将所有数据分布到不同的表中。这里再建一张测试表income，用多表插入的方式将数据从wage加载到income，注意income的表结构与wage不同，它新增了type列来区分收入类型。在多表插入中，你必须通过子查询加载数据。数据库应用中，我们经常需要向表中插入数据，insert语句是最常用的数据插入方式，根据目标表的数量，可以分为单表插入和多表插入。

物化视图主要用在OLAP环境，可以提前运行大量运算并保存结果，为后续查询加速。1.1物化视图应用场景为什么要使用物化视图？假设一个场景，用户每天都需要分析销售数据，而每天产生的数据量都非常庞大，在原始数据上直接运行查询SQL（例如进行sum，avg的操作），速度会非常缓慢。传统的查询优化手段，例如索引、分区、并行执行等，在这种场景下都无法将速度提升到一个可接受的范围。对于这种问题，传统的解决方案是建立一张中间表，提前运行查询SQL并将结果保存下来，当用户查询的时候直接查询结果表。

视图（views）是一种基于表的"逻辑抽象"对象，由于它是从表衍生出来的，因此和表有许多相同点，我们可以和对待表一样对其进行查询/更新操作。但视图本身并不存储数据，也不分配存储空间。 本文只讨论普通的视图（Standard Views）。Oracle的视图类型还有对象视图（Object Views）物化视图（Materiallized Views）和分析视图（Analytic Views）等。目录一、视图的作用二、视图创建语法三、基础视图3.1 基础视图创建3.2 自定义视图列名3.3 强行建立视图3

a int,b int invisible, -- 建表时定义隐形列c int);-- 将已有列转换为隐形列-- 显示指定列名-- 显示指定列名通过alter table 的modify …-- 将隐形列转变为正常列三、隐形列对列排序的影响表中的列是有排序的，例如我们在使用select *查询时，列的显示顺序就是建表时列的定义顺序。但是隐形列是不参与表中列的排序的，如果某列由隐形变为显形，那么总是会附加到表的最后，因此隐形列会导致表中列排序的变化，这点需要注意。

在会话级别开启并行度后，可以通过v$session表中的pdml_status，pddl_status, pq_status来查看DML，DDL, QUERY 的会话并行状态，可能的状态有disabled/enabled/forced。使用force选项时，会强制后续语句使用并行执行，并且用指定的并行度覆盖默认并行度。如果你不知道并行度该设置为多少，可以不指定并行度，这样Oracle会自动决定并行度，例如上面的语句中，我们去掉parallel后的数字，oracle将自行决定并行度。

当指定身份列时，系统会隐式为其创建一个序列生成器（Sequence Generator SG），这个序列生成器为身份列提供值。从12c开始，Oracle提供了身份列（Identity Column）特性，在创建表时可以实现类似MySQL中的自增（auto_increment），从而为每一行数字自动生成ID值（身份）。观察上面的插入的数据，可以看到用户指定的值是可以与序列生成器的值重复的，因此。，它会与表中已有的数据生成相同的值，同时用户也可以再次插入已存在的值。示例：创建身份列时显式指定属性。

另外，只要用户会话获取了某个序列值后，这个值就只能被该会话访问，即使插入数据的事务回滚，这个序列值也不会恢复。在传统通过应用程序生成主键的场景，我们通常会采用串行（Serialization）的方式来获取唯一的整数，当某个用户获取主键值时，其他用户都必须等待。一个未使用的新序列是没有当前值的，因此在使用currval之前，必须至少引用nextval一次（生成序列值）。另外，序列值对不同的表是独立的，例如：同一个序列可以为"表A"和"表B"独立生成序列值。示例：创建一个从1开始，每次递增1的序列。

当单表数据量随着时间变的越来越大时，会给数据的管理和查询带来不便。我们可以考虑对表进行分区，利用分区表特性将数据分成小块存储，可以大幅提升查询性能，管理便捷性及数据的可用性。目录一、分区表概述1.1 分区表概念1.2 何时使用分区表1.3 分区表的优点1.3.1 提升SQL查询性能1.3.2 提升表可管理性1.3.3 提升数据可用性二、基础分区策略2.1 范围分区（Range Partition）2.2 哈希分区（Hash Partition）2.3 列表分区（List partition）三、扩展分区策略

小括号中的(start_time_column, end_time_column)显示指定用来管理时间有效性的列，也可以省略。你也可以在查询时可以通过 as of period for valid_time 来指定一个时间点，限定只有数据的有效期包含这个时间点，数据才能被查询出（即在这个时间点，数据是有效的）。数据在插入时会根据period for 子句后面的 start_time 和 end_time 划定一个数据有效期的范围，你可以控制在查询时显示所有的数据（像普通表一样），或者仅显示有效期内的数据。

采用数据库内归档是一个折中方案，通过将列标记为"不活跃(inactive)"状态完成归档，采用这种方式可以让数据更久的留在数据库中，同时应用也没有性能的损失。在开启后，在表上会新建一个隐藏列ORA_ARCHIVE_STATE，通过改变它的值来标记数据是否被归档（0-活跃，1-归档）。开启后在表上会创建隐藏列ora_archive_state，该列是隐藏的，普通desc命令无法看到，但是查询可以显示指定该列，同时也可以通过查询视图 user_tabe_cols 来查看。

ADO策略就是根据热图的统计信息，在满足一定的条件下，数据库自动帮你完成数据压缩/移动等工作。你可以通过 create table 或 alter table 的 ilm policy 子句来管理表（或者分区）上的ADO策略，包括添加、删除、启用、禁用等操作。在创建策略时你可以指定压缩等级、存储层移动，并且可以通过 after 和 on 子来指定这些动作在什么条件下发生。在创建策略时，Oracle会为ADO策略自动生成名称P1,P2 … Pn（通过视图 dba_ilmpolicies 查看）.

开启后，Oracle将开始追踪数据访问和修改信息，但是其不会追踪 system 和 sysaux 表空间的对象信息。通过这些函数，可以查看到比视图更详尽的信息，并且可以深入到视图无法查看的粒度，比如数据块级别。通过热图的访问次数排名，时间等，我们可以很容易的追踪数据库中热点数据。根据热图（Heat Map）追踪的数据访问和变更结果，可以辅助自动数据优化（ADO）自动进行数据压缩，表空间移动，完成信息生命周期（ILM）的管理。这个参数同时也会控制自动数据优化（ADO）功能的开启和关闭。

而且如果数据过于陈旧，可能归档数据与当前数据库结构早已不兼容，这使得获取归档数据需要额外的开发工作，而在线归档状态下，数据可以一直与当前数据库的格式保持同步，使用最新的应用可以直接访问历史数据。以上的示例是按访问频率进行数据分级，但有些场景可能需要其他的分级策略，例如按照数据机密程度等策略进行分级等，也可以将不同的分级策略结合使用，数据的分级方法并不固定，结合自身特点对数据进行分类。数据分级即定义数据的访问等级，哪些数据最重要，数据生命周期如何演变，什么样的数据可以归档。是否有法律法规限制数据的保存时间？

闪回技术是除了传统备份/恢复之外，Oracle所提供的额外一系列数据保护特性。利用闪回技术，你可以快速查找数据的历史版本，撤销误操作。比起从备份中恢复，闪回操作要简单许多，不通过还原备份就可以将整库回退到指定时间点等。闪回的恢复级别从行级、表级、数据库级均有覆盖。熟练掌握闪回技术可以帮助我们快速解决各种级别的数据问题。Oracle闪回技术分为以下7类：闪回查询(Flashback Query)闪回版本查询(Flashback Version Query)闪回事务查询(Flashbac...

Oracle从12c开始引入了多租户架构(Multitenant Architecture)，使用多租户特性，用户可以创建一个CDB(Container Database 容器数据库)，容器数据库可以包含0个，1个或多个PDB(Pluggable database 可插拔数据库)。PDB即是我们以前熟悉的传统数据库，用来管理schema，objects等应用数据，CDB主要用来管理共享的资源。在12c之前，同一台服务器上多个数据库需要创建多个实例，多份元数据，且每个实例都有自己独立的内存区域和后台...