Oracle中为何substr(clob,1)能大幅提升查询性能？

背景信息 #

表结构 #

创建的表结构如下：

create table messages (
    id number(38,0) generated by default as identity not null,
    create_timestamp timestamp(6) default current_timestamp,
    message clob
);

该表存储约500万行数据，仅主键id存在索引。

对比查询 #

以下两个查询返回完全相同的结果集：

查询1

select m.id,
       m.create_timestamp,
       m.message
from messages m;

查询2

select m.id,
       m.create_timestamp,
       substr(m.message,1)
from messages m;

性能表现 #

当仅获取1000行数据时，两者性能差距悬殊：

• 查询1：执行耗时2503ms，抓取耗时37988ms
• 查询2：执行耗时255ms，抓取耗时7ms

原本预期带有substr额外处理逻辑的查询2会更慢，实际却相反，这是为什么？

核心原因 #

这本质是CLOB大对象的存储与读取机制导致的：

1. CLOB的默认读取逻辑：CLOB属于大对象类型，在Oracle这类数据库中，表的行数据里不会直接存储CLOB的完整内容，只会存一个指向实际数据存储位置的指针（LOB定位器）。当直接查询m.message时，数据库需要先读取这个指针，再额外发起磁盘IO去读取指针指向的CLOB实际内容——这部分额外IO就是查询1抓取耗时暴增的关键，哪怕只取1000行，每行的额外IO累积起来也会占用大量时间。
2. substr触发的优化：当使用substr(m.message,1)时，数据库会识别到这个操作需要读取CLOB的全部内容，此时会触发一个优化逻辑：将CLOB内容转换为普通VARCHAR类型（只要内容长度不超过VARCHAR的上限）。这个转换过程中，数据库会直接在行数据读取阶段同步获取CLOB内容，跳过了单独读取大对象存储区域的复杂流程，相当于把CLOB当作普通字符串处理，避免了额外的磁盘IO开销。
3. 数据抓取的本质差异：查询1的“抓取耗时”大部分花在逐个读取CLOB的实际内容上，而查询2的抓取过程只需要读取行数据本身，没有额外的IO操作，所以耗时可以忽略。

内容的提问来源于stack exchange，提问作者Jamie