一、数据表的逻辑结构：CTAS 一步搞定

CREATE TABLE emp_copy AS SELECT * FROM emp; 这一句做完两件事：建结构、灌数据。 加 WHERE 1 = 0 就只建结构——条件恒假，一行都查不出来，但列定义照样复制。 CTAS 的最大坑：不复制约束。 主键、外键、唯一、CHECK 全都不跟过来。 所以用 CTAS 做的「备份表」，是没有约束保护的，数据可以被随便改坏。 要连约束一起复制，只能用 DBMS_METADATA.GET_DDL 或者 expdp。 CTAS 还有个好处：可以顺便改存储属性。 CREATE TABLE t NOLOGGING TABLESPACE dw AS SELECT ...—— 建表、灌数据、指定表空间、不记日志，一句完成。 这是数据仓库里最常见的写法。

二、创建数据表：列定义与命名

列定义三件事：名字、类型、约束。 类型选择有几个经验： 金额用 NUMBER(p,2) 不用 FLOAT（浮点会丢精度）；日期用 DATE 或 TIMESTAMP； 字符串用 VARCHAR2 不用 VARCHAR（后者是保留字，行为可能变）。 VARCHAR 和 VARCHAR2 的区别值得单独说： VARCHAR 是 SQL 标准的写法，Oracle 保留它是为了兼容，但【将来可能改行为】。 VARCHAR2 是 Oracle 自己的，行为承诺不变。 所以 Oracle 里一律写 VARCHAR2——这一条不是风格问题，是「别给自己埋雷」。 约束命名建议显式写：CONSTRAINT pk_emp PRIMARY KEY。 不写的话 Oracle 自动生成 SYS_C0012345 这种名字， 以后想 DISABLE 或者 DROP 它，得先去字典里查名字。 规范的做法是「约束名带表名和类型」，比如 pk_emp、fk_emp_dept。

三、PCTFREE 与 PCTUSED：块内空间的两道闸

PCTFREE 是「留多少给未来更新」。 PCTUSED 是「用到多少以下才允许再插新行」（只对 MANUAL 段空间管理有效）。 举个具体的：PCTFREE 20 表示 8K 块最多用到 80%，剩下 20% 留给行变长。 一行从 100 字节长到 300 字节，新内容要有地方放—— 没地方就「行迁移」，一行被劈到两个块，读它要跳两次 I/O。 PCTFREE 给多少，看「这张表更新多不多」： 只读表给 5 甚至 0（反正不会变长）；经常更新的给 20 到 30；默认 10 是个折中值。 PCTUSED 在现代库上基本不用了——因为它只对 MANUAL 段空间管理有效， 而 ASSM（自动段空间管理）从 9i 起就是默认。 教材还讲它是为了完整性，实际建表时写不写都一样（写了也被忽略）。

四、LOGGING 与 NOLOGGING：快与可恢复的取舍

NOLOGGING 的意思不是「这张表不记日志」，而是「某些操作不记日志」： 直接路径插入、CTAS、CREATE INDEX、ALTER TABLE ... MOVE。 普通 INSERT／UPDATE／DELETE 照样记——这一点最容易误解。 好处是快（少一次写日志），坏处是「崩溃后这部分数据恢复不了」。 所以它只适合「能重建的数据」：从源表 CTAS 出来的汇总表、可以通过 ETL 重跑的事实表。 用了 NOLOGGING 之后，备份策略必须跟着改： 恢复完之后要重建那些表， 不然它们的数据文件里会有「不需要恢复的块」（坏块），一查就报 ORA-01578。 这也是为什么生产库上开 NOLOGGING 要走评审。

五、压缩：省空间，费 CPU

压得动压不动，取决于「数据重复度高不高」。 同一列一堆相同的值，压得狠；全是随机字符串，压不动还费 CPU。 所以压缩前该做的是「抽样看重复度」，不是「先压了再说」。 代价在两处： ① 写入时要压（CPU 开销）；② 读取时要解压（也是 CPU）。 所以「压缩能省 I/O」是真的，但「压缩能提高整体性能」要看场景——CPU 紧张的库上压了反而慢。

六、动手 Demo：算 PCTFREE 三档能吃多少行，再看 CTAS 丢了什么

沙箱里没有 Oracle，CREATE TABLE ... PCTFREE 执行不了。 但「PCTFREE 影响每块能装几行」这件事，是一道除法。 我算了 10／20／40 三档各自每块能放几行，再算压缩的理论上限（并说明为什么要打七折）， 最后把 CTAS 和「手工建表 + 加约束」的差别并排摆出来。

# PCTFREE：给未来更新预留多少块内空间
for pf in (10, 20, 40):
    usable = int(8092 * (100 - pf) / 100)
    print('PCTFREE %2d%% -> 每块可用 %d 字节，行长 100 时 %d 行'
          % (pf, usable, usable // 100))

# 压缩能压到多少：看重复度，不是看表大小
total, uniq = 1000, 40
print('一列 %d 个值、只有 %d 个不同 -> 理论上限 %.1f%%'
      % (total, uniq, uniq * 100.0 / total))
print('实际还要扣掉字典开销，通常打个七折')

# CTAS 与 CREATE 的差别：约束会不会跟过来
for how in ('CREATE TABLE ... AS SELECT', 'CREATE TABLE (...) + 手工建约束'):
    keep = '不复制' if 'SELECT' in how else '自己写'
    print('%-38s 约束: %s' % (how, keep))

实跑输出：

--------------------------------------------------------------------
第四十课：创建数据表——选项比你想的多
--------------------------------------------------------------------

  **一句 `CREATE TABLE` 除了列定义，还有一堆「物理属性」可以调：**
  **放哪个表空间、`PCTFREE` 留多少、要不要记日志、要不要压缩、是不是 IOT。**
  **这些属性不影响 SQL 怎么写，但影响「快不快、省不省」。**

  最小的一条，和加了属性的那条
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | CREATE TABLE emp_copy AS SELECT * FROM emp WHERE 1 = 0;
    CTAS：只建结构不搬数据，最常用的复制表手段。


  [Oracle 原码 · 本沙箱不执行]
  | CREATE TABLE emp2 (
  |   empno NUMBER(4) CONSTRAINT pk_emp2 PRIMARY KEY,
  |   ename VARCHAR2(10),
  |   sal   NUMBER(7,2) DEFAULT 0
  | ) TABLESPACE users
  |   PCTFREE 20 PCTUSED 40
  |   LOGGING
  |   COMPRESS FOR OLTP;
    物理属性写在右括号后面。

  **`CREATE TABLE ... AS SELECT`（简称 CTAS）是最常用的建表方式。**
  **它一步做完两件事：建结构 + 灌数据。**  **加 `WHERE 1 = 0` 就只建结构**——
  **因为条件恒假，一行都查不出来，但列定义照样复制。**
  **CTAS 有一个代价：它不复制约束。**
  **主键、外键、`NOT NULL`（除 `NOT NULL` 之外）都不会跟过来**——
  **所以「用 CTAS 做备份表」之后，那张表是没有约束的，数据可能不干净。**
  **要连约束一起复制，得用 `DBMS_METADATA.GET_DDL` 或者 `expdp`。**

  PCTFREE 和 PCTUSED：两个百分比管块里的空间
  --------------------------------------------------------------------
  **`PCTFREE` 是「留多少给未来更新」。**  **`PCTUSED` 是「用到多少以下才允许再插新行」**
  （只对 `MANUAL` 段空间管理有效）。
  **举个具体的：`PCTFREE 20` 表示一个 8K 块最多用到 80%，剩下 20% 留给行变长。**
  **如果一行从 100 字节长到 300 字节，新内容要有地方放**——
  **没地方就「行迁移」，一行被劈到两个块，读它要跳两次。**
  **`PCTFREE 10`：每块可用 7282 字节，行长 100 的话每块 72 行。**
  **`PCTFREE 20`：每块可用 6473 字节，行长 100 的话每块 64 行。**
  **`PCTFREE 40`：每块可用 4855 字节，行长 100 的话每块 48 行。**
  **所以 `PCTFREE` 越大，能装的初始行越少，但行迁移越少。**
  **经常更新的表给 20 到 30，只读的表给 5 甚至 0。**  **默认 10 是个折中值，不是最优值。**

  LOGGING / NOLOGGING：要不要记重做
  --------------------------------------------------------------------
  **`NOLOGGING` 的意思是「这个操作不写重做日志」。**
  **好处是快很多（少一次写），坏处是「崩溃后这部分数据恢复不了」。**
  **所以它只适合「能重建的数据」**——
  **比如从源表 CTAS 出来的汇总表、可以通过 ETL 重跑的事实表。**
  **`NOLOGGING` 不是「表永久不记日志」。**
  **它只影响特定的操作：直接路径插入、`CREATE TABLE AS SELECT`、`CREATE INDEX`、`ALTER TABLE ... MOVE`。**  **普通的 `INSERT`／`UPDATE`／`DELETE` 照样记日志**，  **这一点最容易误解。**
  **用了 `NOLOGGING` 之后，备份策略要跟着改：**  **恢复完必须重建那些表**，
  不然它们的数据是「不需要恢复」的，会留下坏块。

  压缩：省空间，费 CPU
  --------------------------------------------------------------------
  **Oracle 的表压缩有三种，各有各的适用场景：**

  SQL> select kind as 方式, how as 怎么压, fits as 适合 from comp
  方式                    怎么压               适合
  -------------------------------------------------------------
  `COMPRESS BASIC`        块内去重，插入时压   数据仓库、只读表
  `COMPRESS FOR OLTP`     行内压，支持正常 DML OLTP 表也能用
  `COLUMN STORE COMPRESS` 列式压，压得最狠     分析型、宽表
  已选择 3 行
  **压不压得动，取决于数据重复度高不高。**
  **同一列里一堆相同的值，压得就狠；全是随机字符串，压不动还费 CPU。**
  **所以压缩前该做的事是「抽样看重复度」，不是「先压了再说」。**
  **举个例子：一列 1000 个值，只有 40 个不同。**
  **块内去重之后，理论上能压到 4.0%**——  **但这是理论值，实际还有字典开销，通常打个七折。**
  **所以「压缩率 40 倍」这种宣传数字，前提是「重复度极高」。**
  **真实的业务表，压到 2 到 4 倍算正常。**

  索引组织表：加两张子句就变一种表
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | CREATE TABLE cfg (
  |   key   VARCHAR2(30) PRIMARY KEY,
  |   value VARCHAR2(200)
  | ) ORGANIZATION INDEX
  |   PCTTHRESHOLD 20
  |   OVERFLOW TABLESPACE users_ovf;
    ORGANIZATION INDEX 一写，这表就按主键排了。

  **`ORGANIZATION INDEX` 一写，这张表就从堆表变成 IOT。**  **它必须有主键**（不然没法排）。
  **`PCTTHRESHOLD` 控制「一行多长就溢出去」**——
  **IOT 的 B 树节点装不下大行，超过阈值的部分放到 `OVERFLOW` 段里。**
  **IOT 适合「短行 + 按主键查」的表**：码表、配置表、字典表。
  **不适合「长行 + 按别的列查」的表**——  **长行会溢出，别的列没索引照样全扫。**

三行分别是 72／64／48 行。 PCTFREE 从 10 涨到 40，每块能装的行少了三分之一。 这就是「留空间给更新」的代价——表会变大，但行迁移会变少。 怎么判断该给多少？看「更新频率」和「行变长幅度」。 如果一行只是偶尔改几个数字，10 够了；如果经常往 VARCHAR2 里追加内容，给 20 到 30。 压缩那段：1000 个值只有 40 个不同，理论上限 4%。 注意这是「理论」——它假设「块内完全去重」，实际还有字典开销、行头开销。 所以真实压缩率要打折，我写的是「七折」，保守一点说「两到四倍压缩」比较靠谱。 最后两行是 CTAS 和手工建表的对比。 CTAS 那行的「约束: 不复制」就是它最大的坑—— 很多人拿 CTAS 做备份表，之后发现「怎么插都能插进去」，就是因为约束没跟过来。 顺带一句：CTAS 也不复制索引，所以拿它做备份表，查询会比原表慢很多。 真要备份，expdp 或者 RMAN 都比 CTAS 靠谱。

30 秒实验

1. 把 PCTFREE 的取值改成 (0, 50, 90)，重跑。PCTFREE 90 时每块只剩 8 行。 这就是「几乎不装数据、全留给更新」的极端情况—— 只有「一行要长到原来的十倍」的表才需要这样。
2. 把 uniq 从 40 改成 900，重跑。理论上限从 4% 涨到 90%。 压缩基本失效——重复度低的数据压不动。
3. 把 total 从 1000 改成 10，重跑。理论上限变成 400%。 这个数一看就不对：压缩率不可能超过 100%。 它暴露了我的模型缺陷：uniq 不该大于 total。

常见误区

1. 以为 NOLOGGING 是「这张表不记日志」。 只对特定操作生效，普通 DML 照样记。
2. 用 CTAS 做备份表，以为约束也复制了。 不复制。 那张表没有主键、没有 NOT NULL，数据可以被随便改坏。
3. 写 VARCHAR 而不是 VARCHAR2。 VARCHAR 是保留字，Oracle 将来可能改它的行为， 一律用 VARCHAR2。
4. 约束不命名，全靠 SYS_C...。 以后 DROP／DISABLE 得先查字典， 而且报错信息里全是这种名字，很难看懂。

下一篇讲维护表：加字段、改字段、删字段、换表空间、清数据。 每个动作都有「快不快」的问题——有的几毫秒，有的要锁表几小时。

• ☐ （本篇完，下一篇 9.3 维护数据表）

相关笔记

• 维护数据表
• 数据表概述
• 创建表空间
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引