管理表（二）

远歌已逝

已于 2024-12-03 14:04:32 修改

阅读量1k

点赞数 11

分类专栏：数据库文章标签： oracle 数据库

于 2024-12-03 08:02:29 首次发布

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/144195923

版权

数据库专栏收录该内容

32 篇文章

订阅专栏

一般信息

一个表可以有多少个索引，就有多少个列的排列（以及这些列上的函数的排列）。
随着基于函数的索引的出现，理论上可以创建的索引的真实数量变得无限！
但是，实际的限制将再次限制您将创建和维护的索引的实际数量。
表的数量没有限制，即使在单个数据库中也是如此。
然而，实际限制将使这个数字保持在合理的范围内。
您不会有数百万个表（创建和管理这么多表是不切实际的），但您可能有数千个表。

ROWID简介

ROWID 的两种格式：扩展 ROWID 格式 和 受限 ROWID 格式

1. ROWID 概述

ROWID 是 Oracle 数据库中用于唯一标识一行数据的内部标识符。每一行在数据库中都有一个唯一的 ROWID，它指向该行在数据库文件中的物理位置，允许 Oracle 高效地访问和定位数据。ROWID 由数据库在每次插入数据时生成，并且通常作为索引的一部分存储在表中。

2. ROWID 格式

在 Oracle 中，ROWID 有两种格式：扩展 ROWID 和 受限 ROWID。

扩展 ROWID 格式

存储大小：扩展 ROWID 需要 10 个字节的存储空间。
显示格式：扩展 ROWID 采用 Base64 编码，显示为 18 个字符。

扩展 ROWID 由以下四个部分组成：

数据对象编号（Data Object Number）：
- 这个编号唯一地标识一个数据对象，比如表或索引。
- 在数据库中，每个数据对象都有一个唯一的 ID。
- 占用 32 位（4 字节）。
相对文件编号（Relative File Number）：
- 这个编号标识该数据对象所在的文件。每个表空间中的文件都有一个唯一的编号。
- 占用 10 位（约 1 字节）。
区块编号（Block Number）：
- 这是文件中包含该行数据的区块的位置。Oracle 会将数据存储在固定大小的区块中（通常是 8KB）。
- 占用 22 位（约 3 字节）。
行号（Row Number）：
- 这是该行在区块中的位置，标识该行在区块头部的目录槽位置。
- 占用 16 位（2 字节）。

所有这些部分加起来，共占用 80 位（10 字节）。

Base64 编码

为了将 ROWID 以可读格式存储和显示，Oracle 使用 Base64 编码。
Base64 编码包含 64 个字符：A-Z, a-z, 0-9, 和 /，总共有 64 个字符的组合方式。

示例：
假设我们查询了 hr.departments 表，返回的 ROWID 可能像这样：

SELECT department_id, rowid 
FROM hr.departments;

查询结果：

DEPARTMENT_ID | ROWID
--------------|--------------------
10            | AAABQMAAFAAAAA6AAA
20            | AAABQMAAFAAAAA6AAB
30            | AAABQMAAFAAAAA6AAC
...

在这个例子中：

AAABQM 是 数据对象编号。
AAF 是 相对文件编号。
AAAAA6 是 区块编号。
AAA 是行号。

8个字节转18位字符详解

在 Oracle 中，ROWID 是一种唯一标识数据库表中每一行的标识符。扩展 ROWID 和其显示格式的存储大小是由它的内部结构决定的。下面是如何计算这些存储和显示格式的详细说明。

1. 扩展 `ROWID` 的存储空间

扩展 ROWID 的存储空间总共需要 10 字节，这是由 ROWID 内部的结构组成的。具体来说，扩展 ROWID 包含以下部分：

数据对象号（Data Object Number）：占 4 字节（32 位）。
相对文件号（Relative File Number）：占 2 字节（10 位用于标识文件，另外 6 位填充）。
块号（Block Number）：占 4 字节（22 位用于标识块，剩余 10 位填充）。
行号（Row Number）：占 2 字节（16 位用于标识行）。

这些部分共同构成了扩展 ROWID 的 80 位（10 字节），加起来的字节数就是：

32 位（4 字节）— 数据对象号
10 位（2 字节）— 相对文件号
22 位（4 字节）— 块号
16 位（2 字节）— 行号

所以总共是： 32 + 10 + 22 + 16 = 80 位 = 10 字节

Base64 编码：

扩展 ROWID 内部是 80 位（10 字节），它会通过 Base64 编码转换为 18 个字符

Base64 编码每 3 个字节（24 位）用 4 个字符表示。换句话说，对于每 3 字节的输入数据，Base64 输出会生成 4 个字符。根据这个规则，可以推算出编码所需字符数。

受限 ROWID 格式（不重要）

在 Oracle 8 之前的版本（如 Oracle 7）中，ROWID 使用了 受限格式。受限 ROWID 具有以下特点：

存储大小：受限 ROWID 仅占用 6 个字节。
没有数据对象编号：受限 ROWID 没有包含数据对象编号，因此只能用于标识存储在同一个段（segment）中的行。这个格式仅在 Oracle 7 或更早的版本中使用，因为那个时候每个数据库的文件都是唯一的。

这种格式的 ROWID 可以包含：

文件编号、区块编号、行号，但没有 数据对象编号，所以在多表空间的系统中不适用。

虽然 Oracle 8 移除了这个限制并引入了扩展的 ROWID 格式，但在某些情况下（例如，在非分区索引上的非分区表），Oracle 仍然会使用受限 ROWID 格式，尤其是在同一段中的行引用时。

3. ROWID 用于定位行

ROWID 是 Oracle 中用于快速访问行的最有效方式。通过 ROWID，Oracle 可以非常高效地定位表中的某一行。其工作原理如下：

数据对象编号：确定包含行的表空间（每个数据对象都有一个唯一的编号）。根据该编号，Oracle 可以确定表空间的位置。
相对文件编号：在指定的表空间内，ROWID 会包含文件编号，该文件编号唯一标识该数据对象的文件位置。
区块编号：ROWID 会提供文件中的区块位置。数据库中的数据存储在固定大小的区块中，每个区块会包含多行数据。
行号：每个区块内部，Oracle 会将行存储在区块头的目录中，ROWID 提供了行在该区块的准确位置。

通过这些信息，Oracle 可以快速定位某一行数据，而不需要全表扫描。

4. ROWID 查询示例

通过查询 ROWID，我们可以快速找到某一行的物理位置。例如，假设我们想查看 hr.departments 表中某个部门的 ROWID：

SELECT department_id, rowid 
FROM hr.departments
WHERE department_id = 10;

5. ROWID 的实际应用

快速访问：ROWID 提供了最快速的行访问方法，因为它包含了访问该行所需的所有物理位置数据。
高效的删除和更新：通过 ROWID，我们可以准确定位到表中的一行，执行快速的删除或更新操作。
索引扫描：ROWID 通常与索引一起使用，特别是当你使用主键索引时，Oracle 会存储每个表行的 ROWID，以加快查询速度。

ROWID 是访问数据库中单行数据最快的方法之一，因为它直接指向数据的物理存储位置。

表的基础知识

1. 行的结构（Structure of a Row）

数据库块（Database Block）：Oracle 的数据存储结构是以块为单位的，每个块的默认大小为 8KB，行数据存储在这些数据库块中。
行头（Row Header）：
- 行头包含的信息：每一行的数据都有一个行头，用来存储该行的列数、链表信息（如果行数据太大无法在一个块中存放，会使用链表指向其他块），以及行锁状态（该行是否被锁定）。
列长度与列值：
- 每列的数据由两部分组成：列长度（Column Length） 和 列值（Column Value）。
  - 列长度：Oracle 存储列数据时需要先存储该列的长度，通常是 1 个字节（如果列的大小超过 250 字节，则需要 3 个字节来存储长度）。
  - 列值：列的实际数据值紧跟在列长度之后存储。
相邻行的存储：
- 相邻的行是紧凑存储的，不需要额外的空间进行分隔。每个行都有一个目录槽（Row Directory Slot），该槽指向该行数据的开始位置。

2. 创建表（Creating a Table）

基本语法：使用 CREATE TABLE 语句来创建表，表可以是 关系表 或 对象表：
- 关系表：用于存储普通的用户数据。
- 对象表：用于存储对象类型的实例，允许在表的列中使用对象类型定义，但此类表的讨论不在此范围内。

创建表示例：

例如，创建一个员工表 employees 的 SQL 语句：

CREATE TABLE hr.employees(
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
) TABLESPACE USERS;

3. 表的存储选项（Storage Options）

STORAGE 子句

STORAGE 子句用于定义表的物理存储特性，控制数据的存储空间和扩展方式：
- INITIAL：为表分配初始的存储空间，例如 INITIAL 200K。
- NEXT：定义在需要更多空间时下一个扩展的大小，例如 NEXT 200K。
- PCTINCREASE：扩展空间的增长百分比。例如，PCTINCREASE 0 表示每次扩展大小保持不变。
- MINEXTENTS 和 MAXEXTENTS：分别定义表的最小和最大扩展数。例如，MINEXTENTS 1 表示至少有一个扩展，MAXEXTENTS 5 表示最多有 5 个扩展。
示例：
```
CREATE TABLE hr.departments (
    department_id NUMBER(4),
    department_name VARCHAR2(30),
    manager_id NUMBER(6),
    location_id NUMBER(4)
)
STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)
TABLESPACE data;
```

块利用参数（Block Utilization Parameters）

PCTFREE：定义每个数据块中预留的空间比例，用于将来对行进行更新时使用。例如，PCTFREE 10 表示每个块会保留 10% 的空间供更新操作使用，默认值是 10。
PCTUSED：定义每个数据块的最小使用空间比例。当数据块的已用空间低于此值时，该块将成为新行的候选插入块。默认值是 40。
INITRANS 和 MAXTRANS：分别定义数据块中分配的初始和最大事务数：
- INITRANS：数据块初始化时分配的事务数。通常默认为 1。
- MAXTRANS：允许并发更新该数据块的最大事务数。默认值根据数据块大小来决定。

表空间（TABLESPACE）子句

TABLESPACE 子句指定表将存储在哪个表空间中。如果不指定表空间，表将会创建在默认的表空间中。
- 例如，在上述 hr.departments 表的创建示例中，表存储在 data 表空间中。

4. 权限要求（Privileges Required）

CREATE TABLE 权限：如果你想在自己的模式中创建表，需要 CREATE TABLE 权限。
CREATE ANY TABLE 权限：如果你要在其他用户的模式中创建表，则需要 CREATE ANY TABLE 权限。

5. 扩展与优化

自动段空间管理（ASSM）：在 Oracle9i 中，引入了 自动段空间管理（ASSM） 功能，目的是简化空间管理，自动调整数据存储的空间配置，不再需要手动设置 PCTFREE、PCTUSED 等参数。
表空间管理：合理配置表空间可以提高数据库性能并有效管理存储。例如，使用多个表空间可以将不同类型的表分开存储，或者为大对象（LOBs）指定专用的表空间。