一、它解决什么问题

场景很具体：对方给你一个 CSV 或者定长文本，让你灌进一张表。 自己写脚本也能做，但要处理：分隔符、引号、空值、类型转换、坏行、性能。 SQL*Loader 把这五件事都做了，而且做得比你写的脚本快。 它的输入是三样东西：数据文件、控制文件、目标表。 输出是四样：表里的数据、日志、坏行文件、丢弃行文件。 把这四样搞清，它就没有黑箱了。 调用方式两种：命令行 sqlldr userid=... control=...，或者从 SQL＊Plus 里用 $ 调用。 它是个独立的可执行程序，不是数据库的一部分——所以客户端装了 Oracle 客户端就能跑。

二、控制文件：三段话

LOAD DATA
INFILE 'emp.dat'
BADFILE 'emp.bad'
DISCARDFILE 'emp.dis'
INTO TABLE emp
FIELDS TERMINATED BY '|'
(empno, ename, deptno)

LOAD DATA 是个固定开头，告诉它「这是个加载脚本」。 INFILE 说从哪个文件读，BADFILE 和 DISCARDFILE 说坏行和丢弃行往哪写。 INTO TABLE 之后是「怎么解析」：FIELDS TERMINATED BY 说分隔符，括号里说「文件里的列， 对应表里的哪些列」。 括号里还可以写转换函数，比如 deptno "UPPER(:deptno)"。 BADFILE 和 DISCARDFILE 是两个不同用途的文件，别搞混： 前者放「解析失败」的行（列数不对、类型转不了），后者放「解析成功但被 WHEN 条件挡掉」的行。 对着 bad 文件找一个「被条件挡掉」的行，是白费功夫。

三、两种路径：常规与直接

快的来源有两处：一次提交一批（少了事务开销），以及索引事后统一建。 后者更关键——边插边维护索引，等于每插一行就要在 B 树里找一次位置； 事后统一建是一次排序批量生成，便宜得多。 所以「先删索引、灌完再建」这个土办法，和直接路径是一个思路。 区别只是直接路径连「写数据块」这一步都绕过了 SQL 引擎。 代价要说清楚：直接路径【绕过 SQL 引擎】，所以触发器不触发、 约束不逐行检查（事后 ENABLE 时才发现脏数据）、INSERT 级审计不记。 所以它适合「初始灌数」，不适合「日常增量」。

四、坏行、丢弃行、ERRORS 阈值

坏行不会让整批失败——这是它比「自己写脚本」强的地方。 好的进去、坏的写进 BAD 文件，原样保留，改好可以再加载一次。 但有一个阈值：ERRORS 参数默认是 50。 错误行超过 50 条，整个作业被判失败并回滚。 所以灌一个「脏数据比较多」的文件，要先把 ERRORS 调大。 DISCARD 是另一回事：它不报错。 控制文件里写 WHEN deptno != '10'，不满足的行不进表、也不进 BAD，而是进 DISCARD 文件。 这是「主动筛掉」而不是「出错」，所以不计入 ERRORS。 实用建议：第一次灌一个陌生文件，先 ROWS=1 只灌一行。 看日志里的「列数、类型、分隔符」对不对，再全量跑。 这一步能省掉大量「跑了两小时才发现分隔符写错」的时间。

五、什么时候别用它

三种情况用别的更好： ① 数据已经在另一个 Oracle 库里——用数据泵，别导成文本再灌； ② 要做复杂的转换——用外部表加 SQL，或者干脆先灌进临时表再 INSERT ... SELECT； ③ 要反复跑、要能回滚——用普通 INSERT，慢一点但可控。 第一种最常见。 「从老库导成 CSV，再灌进新库」这个流程，比直接用 NETWORK_LINK 或者数据泵多了一倍的工作量， 还多了一倍的出错机会。 还有一条：文本文件天然有编码问题。 UTF-8、GBK、带 BOM 不带 BOM，任意一个不对，中文就变乱码。 所以灌之前先用 file 命令或者编辑器确认编码，再在控制文件里配 CHARACTERSET。 这一步五分钟，能省掉半天。

六、动手 Demo：两万行文本、五百个坏行：两种路径各跑一遍

这一篇的 Demo 造一个「每 40 行少一列」的文本文件，两万行里有五百行是坏的。 然后用两种方式加载：逐行走 SQL，和批量直灌加事后建索引。 要盯住三样：耗时、成功行数、坏行数。 坏行数两边必须一样——它说明「坏行判定」跟路径无关，只跟解析规则有关。

# SQL*Loader 的两种路径：逐行走 SQL，还是批量直灌
import os, random, sqlite3, tempfile, time
T = tempfile.gettempdir()
dat = os.path.join(T, "t174.dat")
rnd = random.Random(11)
bad = 0
with open(dat, "w", encoding="utf-8") as f:
    for i in range(1, 20001):
        if i % 40 == 0:                    # 每 40 行少一列
            f.write("%d|E%04d\n" % (i, i))
            bad += 1
        else:
            f.write("%d|E%04d|%d\n" % (i, i, rnd.randint(10, 99)))
print("造了 20000 行，其中 %d 行少一列" % bad)

def load(direct):
    p = os.path.join(T, "t174_%s.db" % direct)
    if os.path.exists(p):
        os.remove(p)
    d = sqlite3.connect(p)
    d.execute("create table emp (empno int primary key, ename text,"
              " deptno int)")
    if not direct:
        d.execute("create index i_d on emp(deptno)")
    n, nb, batch = 0, 0, []
    t0 = time.perf_counter()
    with open(dat, encoding="utf-8") as f:
        for ln in f:
            parts = ln.rstrip("\n").split("|")
            if len(parts) != 3:
                nb += 1                    # 坏行进 BAD 文件
                continue
            row = (int(parts[0]), parts[1], int(parts[2]))
            if direct:
                batch.append(row)
                if len(batch) >= 5000:
                    d.executemany("insert into emp values (?,?,?)", batch)
                    batch = []
            else:
                d.execute("insert into emp values (?,?,?)", row)
            n += 1
    if batch:
        d.executemany("insert into emp values (?,?,?)", batch)
    d.commit()
    if direct:                             # 直接路径：灌完再建索引
        d.execute("create index i_d on emp(deptno)")
        d.commit()
    d.close()
    return n, nb, time.perf_counter() - t0

for direct in (False, True):
    n, nb, t = load(direct)
    print("%s  成功 %d 行  坏行 %d  %.4f 秒"
          % ("直接路径" if direct else "常规路径", n, nb, t))

实跑输出：

--------------------------------------------------------------------
第七十八课：SQL*Loader——控制文件、坏行、两种路径
--------------------------------------------------------------------

  **SQL*Loader 干的事只有一件：把文本文件按格式解析成行，灌进表里。**
  **它的速度来自「绕过 SQL 引擎」，代价是绕过了一堆本来会帮你把关的东西。**

  一、造一个带坏数据的文本文件
  --------------------------------------------------------------------
  **20000 行数据，其中 500 行故意少一列（用来模拟格式错误）。**
  **字段之间用竖线分隔，对应的控制文件写 `FIELDS TERMINATED BY '|'`。**

  二、控制文件长什么样
  --------------------------------------------------------------------
    LOAD DATA
    INFILE 'emp.dat' "str '|'"
    BADFILE 'emp.bad'
    DISCARDFILE 'emp.dis'
    INTO TABLE emp
    FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
    (empno, ename, deptno)
  **控制文件就三段。**
  **`LOAD DATA` 说「我要加载」，`INFILE` 说「从哪读」，`INTO TABLE` 说「怎么解析、往哪灌」。**
  **`BADFILE` 和 `DISCARDFILE` 是两个不同用途的文件：**
  **前者放「解析失败」的行（列数不对、类型不对），后者放「解析成功但被 `WHEN` 条件挡掉」的行。**
  **把这两个搞混，会对着 bad 文件找半天找不到那行。**

  三、常规路径 vs 直接路径
  --------------------------------------------------------------------

  路径                     耗时      成功行 坏行 索引
  ---------------------------------------------------------
  常规（走 SQL 引擎）      0.1541 秒 19500  500  建表时就有
  直接（批量加事后建索引） 0.1154 秒 19500  500  灌完再建
  已选择 2 行
  **快了 1.3 倍。**
  **快的来源有两处：一次提交一批（少了事务开销），以及索引事后统一建**——
  **直接路径的索引是按排序后的顺序批量生成的，比「每插一行维护一次 B 树」便宜得多。**
  **代价也要说清楚：直接路径【绕过 SQL 引擎】，所以**：
  **触发器不触发、约束不逐行检查（事后启用时才发现脏数据）、`INSERT` 级别的审计不记。**
  **所以它适合「初始灌数」，不适合「日常增量」。**

  四、坏了 500 行，怎么找回来
  --------------------------------------------------------------------
  **坏行都在 BAD 文件里，原样保存，改好可以再加载一次。**
  **这一步是 SQL*Loader 比「自己写脚本解析」强的地方**——
  **它不会因为一行坏了就整批失败，而是「好的进去、坏的留证」。**
  **但要注意一个阈值：`ERRORS` 参数默认是 50。**
  **错误行超过 50 条，整个作业会被判定失败并回滚。**
  **所以灌一个「脏数据比较多」的文件，要先把 `ERRORS` 调大，或者分片跑。**

两万行里 500 行坏，两种路径都是「成功 19500、坏 500」。 这个一致性很重要：它说明坏行的判定只看「列数对不对」，和走不走 SQL 引擎无关。 耗时上直接路径快了 1.3 倍。 这个倍数比真实 Oracle 低得多——真实环境里直接路径通常快 5 到 10 倍。 原因在 SQLite：它没有 Oracle 那套「解析 SQL、生成执行计划」的开销， 所以「绕过 SQL 引擎」省下的东西本来就少。 但有一件事两边是一样的：索引建的时机。 常规路径在建表时就建了索引，每插一行维护一次；直接路径是灌完数据再统一建，一次排序搞定。 这个差别在数据量大时会放大很多倍。 所以这条 Demo 能给的最实在的结论是： 「先灌数据、后建索引」这个顺序本身就值钱，不用等直接路径。 日常写脚本灌数，也可以照这个顺序来。 Demo 的边界：这里没有真的写 BAD 文件，只是把坏行计数了。 真实 SQL*Loader 会把坏行原样写进 BADFILE，包括行号和原始内容—— 这才是「坏的留证」的意义：改好之后能只重跑那五百行。

30 秒实验

1. 把坏行的比例从「每 40 行一次」改成「每 4 行一次」，再跑一次。 看两边耗时和坏行数怎么变——坏行多了，解析的开销占比会上升。
2. 把 batch 的阈值从 5000 改成 500，再跑一次直接路径。 看是不是变慢——批量太小，事务开销又回来了。
3. 把 create index 提到灌数据之前（常规路径的写法），但其他用直接路径的写法。 看耗时变成多少——这就单独量出了「索引时机」的贡献。

常见误区

1. 「直接路径什么都能干，日常也用直接路径」。 它绕过 SQL 引擎，触发器、约束、审计都不走。 适合初始灌数，不适合日常增量。
2. 「坏行会让整批失败」。 默认不会，ERRORS 阈值是 50。超过 50 才判失败并回滚。
3. 「BADFILE 和 DISCARDFILE 是一回事」。 前者是解析失败， 后者是被 WHEN 条件主动筛掉的，不报错。
4. 「从老库导 CSV 再灌进新库最稳」。 多一倍工作量、多一倍出错机会。 同构库之间直接用数据泵或 NETWORK_LINK。

第 17 章到这里结束。下一篇进第 18 章：闪回技术。 前面两章讲的都是「出事了怎么救」—— 第 18 章讲的是「能不能不出事」，或者说「不用恢复也能把数据看回来」。

• ☐ （本篇完，下一篇 18.1 闪回技术概述）

相关笔记

• IMPDP 导入数据
• EXPDP 和 IMPDP 概述
• 使用 RMAN 工具部分恢复
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引