PART1-Oracle关系数据结构-表和表簇

2. 表和表簇

2.1. 模式对象简介

数据库模式是数据结构的逻辑容器，这些数据结构称为模式对象。模式对象的例子有表和索引。模式对象是通过 SQL 创建和操作的。
一个数据库用户拥有密码和各种数据库权限。每个用户拥有一个与其同名的模式。模式包含了属于该用户的数据。例如，hr用户拥有hr模式，该模式包含如employees表这样的模式对象。在生产数据库中，模式所有者通常代表一个数据库应用程序而不是一个人。
在一个模式内，每种特定类型的模式对象都有一个唯一的名称。例如，hr.employees 指的是 hr 模式中的employees表。图 2-1 描绘了名为hr的模式所有者和hr模式内的模式对象。

2.1.1. 模式对象类型

关系数据库中最重要的模式对象是表。表以行的形式存储数据。
Oracle SQL 允许您创建和操作许多其他类型的模式对象，包括以下内容：

  • 索引: 索引是包含表或表簇中每个索引行条目的模式对象，并提供直接、快速访问行的功能。Oracle 数据库支持多种类型的索引。索引组织表是一种数据存储在索引结构中的表。
  • 分区: 分区是大型表和索引的一部分。每个分区都有自己的名称，并且可以选择具有自己的存储特性。
  • 视图: 视图是数据在一个或多个表或其他视图中的自定义表示。您可以将其视为存储的查询。视图实际上不包含数据。
  • 序列: 序列是用户创建的对象，可以由多个用户共享以生成整数。通常，序列用于生成主键值。
  • 维度: 维度定义了列集对之间的父子关系，其中列集中的所有列必须来自同一张表。维度通常用于分类客户、产品和时间等数据。
  • 同义词: 同义词是其他模式对象的别名。由于同义词只是别名，所以除了其在数据字典中的定义外，不需要任何存储。
  • PL/SQL子程序和包: PL/SQL是Oracle对SQL的过程扩展。PL/SQL子程序是一个命名的PL/SQL块，可以通过一组参数调用。PL/SQL包将逻辑相关的 PL/SQL类型、变量和子程序分组。

数据库中还存储了其他类型的对象，可以通过 SQL 语句创建和操作，但不包含在模式中。这些对象包括数据库用户、角色、上下文和目录对象。

2.1.2. 模式对象存储

一些模式对象将数据存储在称为段的逻辑存储结构中。例如，非分区的堆组织表或索引会创建一个段。其他模式对象，例如视图和序列，仅包含元数据。本节仅描述具有段的模式对象。
Oracle数据库在逻辑上将模式对象存储在表空间内。模式和表空间之间没有关系：一个表空间可以包含来自不同模式的对象，一个模式的对象可以包含在不同的表空间中。每个对象的数据实际包含在一个或多个数据文件中。
图 2-2显示了表和索引段、表空间和数据文件的可能配置。一个表的数据段跨越两个数据文件，这两个数据文件都是同一表空间的一部分。一个段不能跨越多个表空间。

2.1.3 模式对象依赖

一些模式对象引用其他对象，从而创建模式对象依赖关系。例如，一个视图包含引用表或其他视图的查询，而一个PL/SQL子程序调用其他子程序。如果对象A的定义引用了对象B，那么A是相对于B的依赖对象，而B是相对于A的被引用对象。
Oracle数据库提供了一种自动机制，以确保依赖对象始终与其引用的对象保持同步。当创建依赖对象时，数据库会跟踪依赖对象与其引用对象之间的依赖关系。当引用对象发生可能影响依赖对象的更改时，依赖对象会被标记为无效。例如，如果用户删除了一个表，那么基于该表的视图将不可用。
无效的依赖对象必须在引用对象的新定义下重新编译后才能使用。重新编译会在引用无效的依赖对象时自动进行。
以下示例脚本演示了模式对象如何创建依赖关系，该脚本创建了一个表 test_table，然后创建了一个查询该表的过程：

CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );
CREATE OR REPLACE PROCEDURE test_proc
AS BEGIN FOR x IN ( SELECT col1, col2 FROM test_table )
LOOP
-- process data
NULL;
END LOOP;
END;
/

以下查询过程 test_proc 的状态，显示其有效：

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
OBJECT_NAME STATUS
---------------- ----------
TEST_PROC VALID

在向 test_table 添加列 col3 后，该过程仍然有效，因为该过程对该列没有依赖关系：

SQL> ALTER TABLE test_table ADD col3 NUMBER;
Table altered.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
OBJECT_NAME STATUS
----------- -------
TEST_PROC VALID

然而，更改 test_proc 过程中依赖的 col1 列的数据类型会使该过程失效

SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20);
Table altered.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
OBJECT_NAME STATUS
----------- -------
TEST_PROC INVALID

运行或重新编译该过程会使其再次有效，如以下示例所示：

SQL> EXECUTE test_proc
PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
OBJECT_NAME STATUS
----------- -------
TEST_PROC VALID

2.1.4 SYS和System模式

所有的 Oracle 数据库都包含默认的管理员账户。管理员账户拥有极高的权限，仅供授权执行诸如启动和停止数据库、管理内存和存储、创建和管理数据库用户等任务的 DBA 使用。

在创建数据库时，会自动创建管理员账户SYS。该账户可以执行所有的数据库管理功能。SYS模式存储了数据字典的基本表和视图。这些基本表和视图对于 Oracle 数据库的运行至关重要。SYS 模式中的表只能由数据库操作，任何用户都不得修改。

SYSTEM 账户也会在创建数据库时自动创建。SYSTEM 模式存储了显示管理信息的附加表和视图，以及各种 Oracle 数据库选项和工具使用的内部表和视图。永远不要使用 SYSTEM 模式存储对非管理员用户感兴趣的表。

2.1.5 Sample模式

Oracle数据库可能包含示例模式，这是一组相互关联的模式，使得Oracle文档和教学材料能够展示常见的数据库任务。hr模式是一个示例模式，其中包含有关员工、部门和地点、工作历史等信息。图 2-3是hr模式中表的实体关系图。本手册中的大多数示例都使用了该模式中的对象。

2.2. 表概述

2.2.1. 列与行

表的定义包括表名和一组列。列标识了表所描述实体的属性。例如，employees 表中的 employee_id 列指的是员工实体的员工ID属性。

一般情况下，在创建表时，您为每一列指定列名、数据类型和宽度。例如，employee_id 的数据类型是 NUMBER(6)，表示该列只能包含最多 6 位数的数字数据。宽度可以由数据类型预先确定，如 DATE 类型。

表可以包含虚拟列，与非虚拟列不同的是，虚拟列不会占用磁盘空间。数据库根据需要通过计算一组用户指定的表达式或函数来推导虚拟列的值。例如，income 虚拟列可以是 salary 和 commission_pct 列的函数。

创建表后，您可以使用 SQL 插入、查询、删除和更新行。一行是对应于表中记录的一组列信息。例如，employees 表中的一行描述了特定员工的属性。

2.2.2. 示例：CREATE TABLE and ALTER TABLE 语句

Oracle SQL创建表的命令是CREATE TABLE。示例2-1展示了hr示例模式中employees表的CREATE TABLE语句。该语句指定了诸如employee_id、first_name 等列，并为每个列指定了诸如NUMBER或DATE等的数据类型。

示例 2-2 展示了一个 ALTER TABLE 语句，该语句向 employees 表添加完整性约束。完整性约束可以强制执行业务规则，防止无效信息进入表中。

示例 2-3展示了hr.employees 表的8行和6列数据。

示例 2-3 中的输出展示了表、列和行的一些重要特征，包括：

  • 表中的一行描述了一个员工的属性：姓名、工资、部门等。例如，输出中的第一行显示了名为 Steven King 的员工的记录。
  • 列描述了员工的属性。在示例中，employee_id 列是主键，这意味着每个员工都通过员工ID唯一标识。任何两个员工都保证不具有相同的员工ID。
  • 非主键列可以包含具有相同值的行。在示例中，员工101 和102 的工资值相同：17000。
  • 外键列引用同一表或不同表中的主键或唯一键。在此示例中，department_id 中的值 90 对应于 departments 表的 department_id 列。
  • 字段是行和列的交集。它只能包含一个值。例如，员工104的部门 ID 的字段包含值 60。
  • 字段可能缺少值。在这种情况下，字段被称为空值。员工 100 的 commission_pct 列的值为空，而员工 149 的字段中的值为 0.2。除非在该列上定义了 NOT NULL 或主键完整性约束，否则列允许空值，此时不允许插入行时不为该列提供值。

2.2.3. Oracle数据类型

每列都有一个数据类型，与特定的存储格式、约束和有效值范围相关联。值的数据类型将一组固定的属性与该值相关联。这些属性导致 Oracle 数据库以不同于另一种数据类型的方式处理一种数据类型的值。例如，你可以将 NUMBER 数据类型的值相乘，但不能将 RAW 数据类型的值相乘。
当创建表时，必须为每个列指定数据类型。随后插入列中的每个值都假定为该列的数据类型。
Oracle 数据库提供了几种内置数据类型。最常用的数据类型分为以下几类：

  • 字符数据类型
  • 数值数据类型
  • 日期时间数据类型
  • ROWID数据类型
  • 格式模型和数据类型

其他重要的内置类型类别包括原始数据类型（RAW）、大对象（LOBs）和集合。PL/SQL 具有用于常量和变量的数据类型，这些类型包括布尔型（BOOLEAN）、引用类型、复合类型（记录）和用户定义类型。

1. Character数据类型

字符数据类型以字符串形式存储字符（字母数字）数据。最常用的字符数据类型是 VARCHAR2，这是存储字符数据最有效的选项。
字节值对应于字符编码方案，通常称为字符集或代码页。数据库字符集在数据库创建时确定。字符集的示例包括 7 位 ASCII、EBCDIC 和 Unicode UTF-8。
字符数据类型的长度语义可以以字节或字符为单位进行测量。字节语义将字符串视为字节序列。这是字符数据类型的默认设置。字符语义将字符串视为字符序列。字符在技术上是数据库字符集的一个代码点。

2. VARCHAR2与CHAR数据类型

VARCHAR2数据类型用于存储变长的字符字面量。术语“字面量”和“常数值”是同义的，指的是固定的数据值。例如，'LILA'、'St. George Island' 和 '101' 都是字符字面量；5001 是一个数字字面量。字符字面量被包含在单引号中，这样数据库可以将其与模式对象名称区分开来。
当你创建一个带有 VARCHAR2 列的表时，你需要指定一个最大字符串长度。在示例 2-1 中，last_name 列的数据类型为 VARCHAR2(25)，这意味着存储在该列中的任何名字最多可以有 25 个字节。对于每一行，Oracle 数据库将列中的每个值存储为变长字段，除非值超过了最大长度，在这种情况下，数据库会返回错误。例如，在单字节字符集中，如果你在某一行的 last_name 列值中输入 10 个字符，那么该行的列片段只存储 10 个字符（10 字节），而不是 25 个。使用 VARCHAR2 可以减少空间消耗。与 VARCHAR2 相比，CHAR 存储固定长度的字符字符串。当你创建一个带有 CHAR 列的表时，该列需要一个字符串长度。默认长度是 1 字节。数据库使用空格填充值到指定的长度。
Oracle 数据库在进行非填充比较语义（nonpadded comparison semantics）的情况下比较 VARCHAR2 值，并在进行空白填充比较语义（blank-padded comparison semantics）的情况下比较 CHAR 值。

3. NCHAR与NVARCHAR2数据类型

NCHAR 和 NVARCHAR2 数据类型用于存储 Unicode 字符数据。Unicode 是一种通用的编码字符集，可以使用单一字符集存储任何语言的信息。NCHAR 存储对应于国家/地区字符集的固定长度字符字符串，而 NVARCHAR2 存储变长字符字符串。
在创建数据库时，你需要指定一个国家/地区字符集。NCHAR 和 NVARCHAR2 数据类型的字符集必须是 AL16UTF16 或 UTF8。这两种字符集都使用 Unicode 编码。
当你创建一个带有 NCHAR 或 NVARCHAR2 列的表时，最大大小始终基于字符长度语义。字符长度语义是 NCHAR 或 NVARCHAR2 的默认且唯一的长度语义。

4. 数值数据类型

Oracle数据库的数值数据类型用于存储固定小数和浮点数、零、以及无穷大。某些数值类型还会存储运算未定义结果的值，这被称为’非数字’或NaN。
Oracle数据库以变长格式存储数值数据。每个值都以科学记数法存储，使用1字节存储指数。数据库最多使用20字节来存储尾数，这是浮点数中包含其有效数字的部分。Oracle数据库不存储前导零和尾随零。

  • Nnumber数据类型

NUMBER 数据类型存储定点和浮点数。数据库可以存储几乎任何数量级的数字。这种数据类型在不同操作系统运行的 Oracle 数据库之间具有良好的可移植性。对于大多数需要存储数值数据的情况，推荐使用 NUMBER 数据类型。
你以 NUMBER(p,s)的形式指定定点数，其中 p 和 s 分别指以下特性：
精度 (Precision)
精度指定了总的数字位数。如果未指定精度，则列存储的值将完全由应用程序提供，不进行任何四舍五入。
小数位数 (Scale)
该语句指定了从小数点到最低有效数字的位数。正刻度表示从小数点到（并包括）最低有效数字的位数。负刻度表示从小数点到（但不包括）最低有效数字的位数。如果您指定一个没有刻度的精度，例如 NUMBER(6)，那么刻度为0。
在示例 2-1 中，工资列的类型是 NUMBER(8,2)，因此精度是 8，刻度是2。因此，数据库将 100,000 的工资存储为100000.00。

  • 浮点数据类型

Oracle 数据库专门为浮点数提供了两种数值数据类型：BINARY_FLOAT 和 BINARY_DOUBLE。这些类型支持 NUMBER 数据类型提供的所有基本功能。然而，NUMBER 使用十进制精度，而 BINARY_FLOAT 和 BINARY_DOUBLE 使用二进制精度，这使得算术计算速度更快，并且通常减少存储需求。
BINARY_FLOAT 和 BINARY_DOUBLE 是近似数值数据类型。它们存储的是十进制值的近似表示，而不是精确表示。例如，值 0.1 不能被 BINARY_DOUBLE 或 BINARY_FLOAT 精确表示。它们经常用于科学计算。它们的行为类似于 Java 和 XMLSchema 中的 FLOAT 和 DOUBLE 数据类型。

5. 日期时间数据类型

日期时间数据类型包括DATE和TIMESTAMP。Oracle 数据库为时间戳提供了全面的时区支持。

  • 日期数据类型

日期数据类型用于存储日期和时间。尽管日期时间可以用字符或数字数据类型表示，但日期数据类型具有特殊的相关属性。在示例2-1中，hire_date列具有日期数据类型。
数据库内部将日期存储为数字。日期存储在每个固定长度为7字节的字段中，分别对应世纪、年、月、日、时、分和秒。
注意：日期完全支持算术运算，因此可以像处理数字一样对日期进行加减运算。参见Oracle Database Advanced Application Developer's Guide。
数据库根据指定的格式模型显示日期。格式模型是一个描述字符字符串中日期时间格式的字符文本。标准日期格式为DD-MON-RR，它以01-JAN-11的形式显示日期。
RR类似于YY（年份的最后两位数字），但返回值的世纪根据指定的两位数字年份和当前年份的最后两位数字而变化。假设在1999年，数据库显示01-JAN-11。如果日期格式使用RR，那么11表示2011年，而如果格式使用YY，那么11表示1911年。您可以在实例级别和会话级别更改默认日期格式。
Oracle数据库以24小时格式存储时间——HH:MI:SS。如果未输入时间部分，则日期字段中的时间默认为00:00:00 A.M。在仅输入时间的情况下，日期部分默认为当月的第一天。

  • 时间数据类型

TIMESTAMP数据类型是DATE数据类型的扩展。除了存储DATE数据类型的信息外，还存储了分秒。TIMESTAMP数据类型对于需要存储精确时间值的应用程序非常有用，例如必须跟踪事件顺序的应用程序。
DATETIME数据类型中的TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE能够识别时区。当用户选择数据时，它的值会根据用户会话的时区进行调整。这种数据类型对于跨地理区域收集和评估日期信息非常有用。

6. ROWID数据类型

数据库中存储的每一行都有一个地址。Oracle数据库使用ROWID数据类型来存储数据库中每一行的地址（rowid）。Rowid分为以下几类：
物理rowid：存储堆组织表、表簇以及表和索引分区中的行地址。
逻辑rowid：存储索引组织表中的行地址。
外部rowid： 是外部表中的标识符，例如通过网关访问的DB2表。这些不是标准的Oracle数据库rowid。
一种称为通用rowid（UROWID）的数据类型支持所有种类的rowid。

  • ROWID的用途

Rowids在Oracle数据库中被内部用于索引的构建。B-tree索引是最常见的类型，包含一个按范围划分的键的有序列表。每个键都与一个rowid关联，该rowid指向相关行的地址，以便快速访问。最终用户和应用程序开发人员还可以利用rowid进行几个重要的功能：
（1）Rowid是访问特定行的最快方式；
（2）Rowid提供了查看表结构的能力；
（3）Rowid是给定表中行的唯一标识符；
你还可以创建使用ROWID数据类型定义列的表。例如，可以定义一个异常表，其中包含一个ROWID数据类型的列，用于存储违反完整性约束的行的rowid。使用ROWID数据类型定义的列与其他表列类似：可以更新值等操作。

  • ROWID伪列

在Oracle数据库中，每个表都有一个名为ROWID的伪列（pseudocolumn）。伪列类似于表列，但实际上并不存储在表中。您可以从伪列中进行查询（SELECT），但不能插入（INSERT）、更新（UPDATE）或删除（DELETE）它们的值。伪列也类似于没有参数的SQL函数。没有参数的函数通常在结果集中的每一行返回相同的值，而伪列通常为每一行返回不同的值。

7. 格式模型和数据类型

格式模型是一个字符字面量，用于描述存储在字符字符串中的日期时间或数值数据的格式。格式模型不会改变数据库中值的内部表示。当将字符字符串转换为日期或数字时，格式模型决定了数据库如何解释该字符串。在 SQL 中，可以将格式模型作为 TO_CHAR 和 TO_DATE 函数的参数，以格式化从数据库返回的值或存储到数据库中的值。

以下语句选择了部门80的员工薪水，并使用 TO_CHAR 函数将这些薪水转换为具有数字格式模型 '$99,990.99' 的字符值：

SQL> SELECT last_name employee, TO_CHAR(salary, '$99,990.99') 2 FROM employees 3 WHERE department_id = 80 AND last_name = 'Russell';
EMPLOYEE TO_CHAR(SAL
------------------------- ----------Russell
$14,000.00

以下示例使用 TO_DATE 函数和格式掩码 'YYYY MM DD' 将字符串 '1998 05 20' 转换为 DATE 值来更新入职日期：

SQL> UPDATE employees 2 SET hire_date = TO_DATE('1998 05 20','YYYY MM DD') 3 WHERE last_name = 'Hunold';

2.2.4. 整数约束

完整性约束是对表中一个或多个列的值进行限制的命名规则。这些规则防止无效数据进入表中。此外，当存在某些依赖关系时，约束还可以防止删除表。如果约束被启用，则数据库会在数据输入或更新时进行检查。不符合约束的数据将被阻止进入。如果约束被禁用，则不符合约束的数据可以允许进入数据库。

在2-8页的示例2-1中，CREATE TABLE语句为last_name、email、hire_date和job_id列指定了NOT NULL约束。约束子句确定了列和约束条件。这些约束确保指定的列不包含空值。例如，尝试插入没有job ID的新员工时将生成错误。

可以在创建表时或创建表后创建约束。如果需要，约束可以被暂时禁用。数据库将约束存储在数据字典中。

2.2.5. 对象表

Oracle对象类型是具有名称、属性和方法的用户定义类型。对象类型使得可以在数据库中将现实世界的实体如客户和采购订单建模为对象。
对象类型定义了逻辑结构，但并不创建存储空间。示例2-5创建了一个名为department_typ的对象类型。

Example 2–5 Object Type
CREATE TYPE department_typ AS OBJECT ( d_name VARCHAR2(100), d_address VARCHAR2(200) );
/

对象表是一种特殊类型的表，其中每一行代表一个对象。示例2-6中的CREATE TABLE语句创建了一个名为departments_obj_t的对象表，其对象类型为department_typ。这个表的属性（列）是从对象类型的定义中派生而来的。INSERT语句将一行数据插入到这个表中。

Example 2–6 Object Table
CREATE TABLE departments_obj_t OF department_typ; INSERT INTO departments_obj_t VALUES ('hr', '10 Main St, Sometown, CA');

像关系列一样，对象表可以包含同一声明类型的对象实例的行。默认情况下，对象表中的每个行对象都有一个关联的逻辑对象标识符（OID），用于在对象表中唯一标识它。对象表的OID列是一个隐藏列。

2.2.6. 临时表

Oracle数据库的临时表仅在事务或会话的持续时间内存在数据。临时表中的数据对会话是私有的，这意味着每个会话只能看到和修改自己的数据。
临时表在需要缓冲结果集的应用程序中非常有用。例如，一个排课应用程序允许大学生创建可选的学期课程时间表。每个时间表在临时表中用一行表示。在会话期间，时间表数据是私有的。当学生决定了一个时间表时，应用程序将选定时间表的行移动到永久表中。在会话结束时，临时数据中的时间表数据会自动被丢弃。

2.2.6.1. 临时表的创建

CREATE GLOBAL TEMPORARY TABLE 语句用于创建临时表。ON COMMIT 子句指定表数据是事务特定（默认）还是会话特定。与其他一些关系数据库中的临时表不同，在 Oracle 数据库中创建临时表时，实际上是创建了一个静态的表定义。临时表是一个持久化对象，在数据字典中有描述，但在会话插入数据之前是空的。您为数据库本身创建临时表，而不是为每个 PL/SQL 存储过程创建。由于临时表是静态定义的，您可以使用 CREATE INDEX 语句为其创建索引。在临时表上创建的索引也是临时的。索引中的数据与临时表中的数据具有相同的会话或事务范围。您也可以在临时表上创建视图或触发器。

2.2.6.2. 临时表中的段分配

与永久表类似，临时表在数据字典中有定义。当首次插入数据时，临时段会被分配。在会话加载数据之前，该表看起来是空的。对于事务特定的临时表，在事务结束时会释放临时段；对于会话特定的临时表，在会话结束时释放临时段。

2.2.7. 外部表

外部表可以像访问数据库中的表一样访问外部数据源中的数据。您可以使用 SQL、PL/SQL 和 Java 查询外部数据。外部表对于查询扁平文件非常有用。例如，一个基于 SQL 的应用程序可能需要访问文本文件中的记录。记录的格式如下：

100,Steven,King,SKING,515.123.4567,17-JUN-03,AD_PRES,31944,150,90 101,Neena,Kochhar,NKOCHHAR,515.123.4568,21-SEP-05,AD_VP,17000,100,90 102,Lex,De Haan,LDEHAAN,515.123.4569,13-JAN-01,AD_VP,17000,100,90

您可以创建一个外部表，将文件复制到外部表定义指定的位置，并使用 SQL 查询文本文件中的记录。
在数据仓库环境中，外部表也非常有价值，用于执行常见的 ETL 任务。例如，外部表可以使数据加载阶段与转换阶段的流水线化，无需在数据库内部对数据进行暂存，这样可以准备好在数据库内部进行进一步处理。详见《数据仓库和商业智能概述》，第17-15页。

2.2.7.1 外部表的创建

创建外部表实际上是在数据字典中创建元数据。与普通表不同，外部表不描述存储在数据库中的数据，也不描述数据如何在外部存储。相反，外部表的元数据描述了外部表层如何向数据库呈现数据。

CREATE TABLE ... ORGANIZATION EXTERNAL 语句有两个部分。外部表定义描述了列的类型。这种定义类似于视图，使得 SQL 可以查询外部数据而不将其加载到数据库中。语句的第二部分将外部数据映射到列上。

外部表通常是只读的，除非使用 ORACLE_DATAPUMP 访问驱动程序创建了 CREATE TABLE AS SELECT 语句。外部表的限制包括不支持索引列、虚拟列和列对象。

2.2.7.2 外部表访问驱动程序

访问驱动程序是一个API，用于解释数据库中的外部数据。访问驱动程序在数据库内部运行，数据库使用该驱动程序来读取外部表中的数据。访问驱动程序和外部表层负责执行对数据文件中的数据所需的转换，以使其与外部表定义匹配。图 2–4 描述了外部数据访问的过程。

Oracle提供了 ORACLE_LOADER（默认）和 ORACLE_DATAPUMP 访问驱动程序用于外部表。对于这两种驱动程序，外部文件不是 Oracle 数据文件。
ORACLE_LOADER 允许使用 SQL*Loader 对外部文件进行只读访问。使用 ORACLE_LOADER 驱动程序无法创建、更新或追加外部文件中的数据。
ORACLE_DATAPUMP 驱动程序允许您卸载外部数据。这个操作涉及从数据库读取数据，并将数据插入一个或多个外部文件表示的外部表中。创建外部文件后，数据库无法更新或追加数据到这些文件中。该驱动程序还允许您加载外部数据，这涉及读取外部表并将其数据加载到数据库中。

2.2.8. 表存储

Oracle数据库使用表空间中的数据段来存储表数据。如《用户段》第12-21页所述，一个段包含由数据块组成的区。
表的数据段（或处理表集群时的集群数据段）位于表所有者的默认表空间中，或者在 CREATE TABLE 语句中指定的表空间中。

2.2.8.1. 表组织结构

默认情况下，表被组织为堆（heap），这意味着数据库将行放置在最合适的位置，而不是按照用户指定的顺序。因此，堆组织的表是一个无序的行集合。当用户添加行时，数据库将行放置在数据段中第一个可用的空闲空间中。不能保证按照插入顺序检索行。

hr.departments表是一个堆组织的表。它包含部门ID、名称、经理ID和位置ID列。随着行的插入，数据库将它们存储在适合的位置。表段中的一个数据块可能包含如示例2-7所示的无序行。

Example 2–7 Rows in Departments Table
50,Shipping,121,1500 120,Treasury,,1700 70,Public Relations,204,2700 30,Purchasing,114,1700 130,Corporate Tax,,1700 10,Administration,200,1700 110,Accounting,205,1700

表中所有行的列顺序是相同的。数据库通常按照它们在 CREATE TABLE 语句中列出的顺序存储列，但不能保证这种顺序。例如，如果表中有一个 LONG 类型的列，那么 Oracle 数据库总是将这列存储在行的最后。此外，如果向表中添加新列，则新列将成为存储的最后一列。

表可以包含虚拟列，与普通列不同，虚拟列不会在磁盘上占用空间。数据库按需通过计算一组用户指定的表达式或函数来推导虚拟列的值。您可以为虚拟列创建索引、收集统计信息，并创建完整性约束。因此，虚拟列与非虚拟列非常相似。

2.2.8.2. 行存储

数据库将行存储在数据块中。包含少于256列数据的表的每一行都包含在一个或多个行片段中。
如果可能，Oracle数据库将每一行存储为一个行片段。然而，如果所有行数据不能插入到单个数据块中，或者对现有行的更新导致行超过其数据块的容量，那么数据库将使用多个行片段存储该行。
表集群中的行包含与非集群表中的行相同的信息。此外，表集群中的行还包含引用其所属集群键的信息。

2.2.8.3. 行片段的ROWID

rowid 实际上是一个行的10字节物理地址。堆组织表中的每一行都有一个唯一的 rowid 对应于该表，代表行片段的物理地址。对于表集群，位于同一数据块中的不同表的行可以具有相同的 rowid。Oracle 数据库内部使用 rowid 来构建索引。例如，B-tree 索引中的每个键都与一个 rowid 相关联，rowid 指向关联行的地址，以便快速访问。物理 rowid提供了对表行最快的访问，能够使数据库在一次 I/O 操作中检索到一行数据。

2.2.8.4. Null值存储

空值（null）表示列中没有值。空值表示缺失、未知或不适用的数据。如果空值位于具有数据值的列之间，则它们存储在数据库中。在这些情况下，它们需要1个字节来存储列的长度（零）。行中尾部的空值不需要存储，因为新的行头部信号表示前一行中的剩余列为空。例如，如果表的最后三列为空，则不会为这些列存储数据。

2.2.9. 表压缩

数据库可以使用表压缩来减少表所需的存储空间。压缩可以节省磁盘空间，减少数据库缓冲区缓存中的内存使用，并在某些情况下加速查询执行。表压缩对数据库应用程序是透明的。

2.2.9.1. 基础行压缩和高级行压缩

基于字典的表压缩为堆组织表提供了良好的压缩比率。Oracle数据库支持以下类型的基于字典的表压缩：

  • 基础表压缩

这种类型的压缩适用于大批量加载操作。数据库不会压缩通过传统的DML修改的数据。您必须使用直接路径加载、ALTER TABLE . . . MOVE操作或在线表重定义来实现基本压缩。

  • 高级行压缩

这种类型的压缩适用于OLTP（联机事务处理）应用程序，并且可以压缩任何SQL操作所处理的数据。

对于基本和高级行压缩，数据库将压缩的行以行主要格式存储。每行的所有列一起存储，接着是下一行的所有列，依此类推（参见第12-9页上的图12-7）。重复值被替换为对存储在块开头的符号表的简短引用。因此，重新创建未压缩数据所需的信息存储在数据块本身中。

压缩的数据块看起来与普通数据块非常相似。大多数数据库功能和函数在常规数据块上的操作同样适用于压缩块。

您可以在表空间、表、分区或子分区级别声明压缩。如果在表空间级别指定，则该表空间中创建的所有表默认均为压缩表。

以下语句将OLTP压缩应用于orders表：ALTER TABLE oe.orders COMPRESS FOR OLTP;

以下是一个部分CREATE TABLE语句的示例，指定一个分区使用OLTP压缩，另一个分区使用基本压缩：

CREATE TABLE sales (
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
...
)
PCTFREE 5 NOLOGGING NOCOMPRESS
PARTITION BY RANGE (time_id) (
PARTITION sales_2010 VALUES LESS THAN (TO_DATE(...)) COMPRESS BASIC,
PARTITION sales_2011 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP
);

2.2.9.2. 混合列压缩 (Hybrid Columnar Compression)

混合列压缩（Hybrid Columnar Compression）将同一列的一组行一起存储。数据块不按行优先格式存储数据，而是同时使用行和列的方法的组合。

将具有相同数据类型和相似特征的列数据一起存储，极大地增加了通过压缩实现的存储节省。数据库对通过任何 SQL 操作的数据进行压缩，尽管直接路径加载的压缩级别更高。数据库操作透明地针对压缩对象工作，因此不需要进行应用程序更改。

  1. 混合列压缩的类型

如果您的底层存储支持混合列存压缩（Hybrid Columnar Compression），那么根据您的需求，可以指定以下压缩类型：

  • 数据仓库压缩

这种类型的压缩优化了存储空间，适用于数据仓库应用程序。

  • 在线归档压缩

这种类型的压缩优化了最大的压缩级别，适用于历史数据和不会改变的数据。

为了实现数据仓库或在线归档压缩，您必须使用直接路径加载、ALTER TABLE... MOVE操作，或在线表重新定义。

混合列式压缩（Hybrid Columnar Compression，简称HCC）针对在Exadata存储上的数据仓库和决策支持应用进行了优化。Exadata最大限度地提升了使用混合列式压缩的表上的查询性能，利用了Exadata存储服务器内置的处理能力、内存和Infiniband网络带宽。

其他Oracle存储系统也支持混合列式压缩，并且可以提供与Exadata存储相同的空间节省效果，但无法提供相同级别的查询性能。对于这些存储系统，混合列式压缩非常适合用于对不经常访问的旧数据进行数据库内归档。

  1. 压缩单元

混合列式压缩（Hybrid Columnar Compression）使用一个称为压缩单元的逻辑结构来存储一组行。当您将数据加载到表中时，数据库以列格式存储一组行，并将每一列的值存储并压缩在一起。在数据库压缩了一组行的列数据之后，数据库会将这些数据放入压缩单元中。

例如，您将混合列式压缩（Hybrid Columnar Compression）应用于daily_sales表。每天结束时，您会填充表格，记录销售的商品和销售数量，使用商品ID和日期作为复合主键。表2-1显示了daily_sales中部分行的数据子集。

假设表2-1中的行存储在一个压缩单元中。混合列式压缩（Hybrid Columnar Compression）将每一列的值存储在一起，然后使用多种算法来压缩每一列。数据库根据多种因素选择算法，包括列的数据类型、列中实际值的基数以及用户选择的压缩级别。

如图2-5所示，每个压缩单元可以跨多个数据块。特定列的值可能会跨多个数据块，也可能不会。

混合列式压缩（Hybrid Columnar Compression）对行锁定有影响（见第9-18页的“行锁（TX）”）。当更新未压缩数据块中的某行时，仅锁定被更新的行。相比之下，如果更新压缩单元中的任何一行，数据库必须锁定该压缩单元中的所有行。使用混合列式压缩进行行更新会导致行ID（rowid）发生变化。

2.3. 表簇概述

表簇（Table Cluster）是一组共享公共列并在相同数据块中存储相关数据的表。当表被簇集时，一个数据块可以包含来自多个表的行。例如，一个数据块可以同时存储来自employees表和departments表的行，而不仅仅是来自单个表的行。

簇键（Cluster Key）是簇集表中共有的列。例如，employees表和departments表共享department_id列。您在创建表簇以及向表簇添加每个表时需要指定簇键。

簇键值是特定行集合的簇键列的值。所有包含相同簇键值的数据（例如department_id=20）会物理上存储在一起。无论有多少行在不同的表中包含该值，每个簇键值在簇和簇索引中都只存储一次。

打个比方，假设一位人力资源经理有两个书柜：一个放着装有员工档案的盒子，另一个放着装有部门档案的盒子。用户经常要求查找某个特定部门所有员工的档案。为了使检索更加方便，经理将所有盒子重新安排在一个书柜中。她按照部门ID来划分这些盒子。因此，部门20所有员工的档案以及部门20的档案都放在一个盒子里；部门100所有员工的档案和部门100的档案则放在另一个盒子里，依此类推。

当表主要被查询（但不被修改）且表中的记录经常一起查询或连接时，可以考虑对表进行簇集。由于表簇将不同表的相关行存储在相同的数据块中，正确使用表簇相比非簇集表具有以下优点：

  • 簇集表连接时的磁盘I/O减少。
  • 簇集表连接时的访问时间提高。
  • 存储相关表和索引数据所需的存储空间减少，因为簇键值不再为每一行重复存储。

通常，在以下情况下不适合对表进行簇集：

  • 表频繁更新。
  • 表频繁需要全表扫描。
  • 表需要截断。

2.3.1. 索引簇概述

索引簇（Indexed Cluster）是使用索引来定位数据的表簇。簇索引是簇键上的B树索引。必须在插入任何簇集表的行之前创建簇索引。假设您创建了名为employees_departments_cluster的簇，并使用department_id作为簇键，如示例2-8所示。由于未指定HASHKEYS子句，此簇为索引簇。之后，您在该簇键上创建了一个名为idx_emp_dept_cluster的索引。

--Example 2–8 Indexed Cluster 
CREATE CLUSTER employees_departments_cluster (department_id NUMBER(4)) SIZE 512; 
CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;

然后，您在簇中创建employees和departments表，并指定department_id列作为簇键，如下所示（省略号表示列规范的位置）：

CREATE TABLE employees ( ... ) CLUSTER employees_departments_cluster (department_id);
CREATE TABLE departments ( ... ) CLUSTER employees_departments_cluster (department_id);

最后，您向employees和departments表中添加行。数据库将每个部门的所有员工和部门的行物理存储在相同的数据块中。数据库将这些行以堆的形式存储，并通过索引进行定位。

图2-6显示了包含employees和departments的employees_departments_cluster表簇。数据库将部门20的员工行存储在一起，部门110的员工行也存储在一起，以此类推。如果这些表没有被簇集，那么数据库不会确保相关的行存储在一起。

B树簇索引将簇键值与包含数据的数据库块地址关联起来。例如，键值20的索引条目显示了包含部门20员工数据的块的地址：
20, AADAAAA9d

簇索引的管理方式与非簇集表上的索引类似，可以存在于与表簇不同的表空间中。

2.3.2. Hash簇概述

哈希簇（Hash Cluster）与索引簇类似，但索引键被哈希函数替代，不存在单独的簇索引。在哈希簇中，数据本身即为索引。

在使用索引表或索引簇时，Oracle数据库通过存储在单独索引中的键值来定位表行。为了查找或存储索引表或表簇中的行，数据库必须执行至少两个I/O操作：

  • 一个或多个I/O操作来查找或存储索引中的键值
  • 一个I/O操作来读取或写入表或表簇中的行

在哈希簇中，Oracle数据库对行的簇键值应用哈希函数。生成的哈希值对应于簇中的一个数据块，数据库根据发出的语句读取或写入该数据块。

哈希簇是一种可选的数据存储方式，用于提高数据检索性能。当满足以下条件时，哈希簇可能会有利：

  • 表的查询频率远高于修改频率。
  • 哈希键列经常以相等条件进行查询，例如 WHERE department_id=20。对于此类查询，簇键值会被哈希化。哈希键值直接指向存储行的磁盘区域。
  • 您可以合理估计哈希键的数量以及每个键值存储的数据量。
  1. 创建Hash簇

簇键（Cluster Key），与索引簇的键类似，是由簇集中的表共享的单列或复合键。哈希键值是插入到簇键列中的实际值或可能值。例如，如果簇键是 department_id，则哈希键值可以是 10、20、30 等等。

Oracle数据库使用一种哈希函数，该函数接受无限数量的哈希键值作为输入，并将它们排序到有限数量的桶中。每个桶都有一个唯一的数字ID，称为哈希值。每个哈希值映射到存储与哈希键值（例如部门10、20、30等）对应的行的数据块地址。

要创建哈希簇，您使用与创建索引簇相同的 CREATE CLUSTER 语句，并加上哈希键。簇的哈希值数量取决于哈希键。在示例2-9中，预计部门的数量为100，因此 HASHKEYS 设置为100。

--Example 2–9 Hash Cluster
CREATE CLUSTER employees_departments_cluster (department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;

在创建 employees_departments_cluster 之后，您可以在该簇中创建 employees 和 departments 表。然后，您可以像在示例2-8中描述的索引簇一样，将数据加载到哈希簇中。

  1. 查询Hash簇

数据库决定如何哈希用户输入的键值，而不是用户自己决定。例如，假设用户经常执行如下查询，输入不同的部门ID编号作为p_id：

SELECT * FROM employees WHERE department_id = :p_id;
SELECT * FROM departments WHERE department_id = :p_id;
SELECT * FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id = :p_id;

如果用户查询 department_id=20 的员工，则数据库可能将该值哈希到桶77。如果用户查询 department_id=10 的员工，则数据库可能将该值哈希到桶15。数据库使用内部生成的哈希值来定位包含请求部门的员工行的数据块。

图2-7展示了一个哈希簇段，表示为一排水平排列的数据块。如图所示，查询可以通过单次I/O操作来检索数据。

哈希簇的一个限制是无法对非索引簇键进行范围扫描（见第3-7页的“索引范围扫描”）。假设在示例2-9中创建的哈希簇没有单独的索引。对于部门ID在20到100之间的查询，由于哈希算法无法对20到100之间的每一个可能值进行哈希，因此不能使用哈希算法。由于没有索引，数据库必须执行全表扫描。

  1. 哈希簇变体

单表哈希簇（Single-Table Hash Cluster）是哈希簇的一种优化版本，支持一次处理一个表。在单表哈希簇中，哈希键与行之间存在一对一的映射。单表哈希簇在用户需要通过主键快速访问表时非常有用。例如，用户经常根据 employee_id 在 employees 表中查找员工记录。

排序哈希簇（Sorted Hash Cluster）以使数据库能够高效地返回按排序顺序排列的行。数据库在内部执行优化排序。对于总是以排序顺序消费数据的应用程序，这种技术可以加快数据检索速度。例如，一个应用程序可能总是按 order_date 列对 orders 表进行排序。

  1. 哈希簇存储

Oracle数据库为哈希簇分配空间的方式不同于索引簇。在示例2-9中，HASHKEYS 指定了预计存在的部门数量，而 SIZE 指定了与每个部门相关的数据大小。数据库根据以下公式计算存储空间值：

HASHKEYS * SIZE / database_block_size

因此，如果示例2-9中的块大小为4096字节，则数据库将为哈希簇分配至少200个块。

Oracle数据库不限制可以插入到簇中的哈希键值数量。例如，即使 HASHKEYS 为100，也没有任何限制阻止您在 departments 表中插入200个唯一部门。然而，当哈希值的数量超过哈希键的数量时，哈希簇的检索效率会降低。

为说明检索问题，假设图2-7中的块100完全填满了部门20的行。用户向 departments 表中插入一个新部门，department_id 为43。部门数量超过了 HASHKEYS 的值，因此数据库将 department_id 43 哈希到哈希值77，这与 department_id 20 使用的哈希值相同。将多个输入值哈希到相同的输出值称为哈希冲突（hash collision）。

当用户插入部门43的行时，数据库无法将这些行存储在已经满的块100中。数据库将块100链接到一个新的溢出块，例如块200，并将插入的行存储在新块中。现在块100和块200都可以存储任一部门的数据。如图2-8所示，对部门20或部门43的查询现在需要两个I/O操作来检索数据：块100及其关联的块200。您可以通过使用不同的 HASHKEYS 值重新创建簇来解决此问题。

posted @ 2024-07-21 17:10  脆皮老弟  阅读(12)  评论(0)  编辑  收藏  举报