Oracle中SUBSTR函数为何生成更长的目标列？

这个现象的核心原因是Oracle执行CREATE TABLE AS SELECT (CTAS)语句时，对函数生成的字符型列的长度推断规则：

• 当SELECT列表中的列是函数表达式（比如SUBSTR）而非直接引用源表列时，Oracle无法直接沿用源列的长度定义，而是会采用该字符函数的最大可能返回长度来定义新列的DATA_LENGTH。
• 对于SUBSTR函数，若未通过第三个参数显式指定截取的最大长度，Oracle会默认将返回值的最大长度设为当前数据库环境下允许的VARCHAR2默认上限。在Oracle 18c Express Edition（XE）中，这个默认上限为4096，因此新表t2的short_field列DATA_LENGTH被设为4096，而非源列的1024。

补充解决方式 #

如果需要让新列长度匹配实际需求，可采用两种方法：

1. 在SUBSTR中显式指定第三个参数（截取长度），示例：

   CREATE TABLE t2 AS SELECT substr(long_field, instr(long_field, 'c') + 1, 1024) short_field FROM t;
   

   此时新列的DATA_LENGTH会被设为指定的1024。
2. 执行CTAS后，通过ALTER TABLE修改列长度，示例：

   ALTER TABLE t2 MODIFY short_field VARCHAR2(1024);
   

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