为何省略NULL列的INSERT语句执行速度更慢？

我维护着一个大型INSERT脚本，执行速度偏慢。虽然性能不是核心问题（可以整夜运行），但还是想优化一下。原本以为瓶颈在客户端到服务器的语句传输，把脚本体积压缩到原来的1/3应该能提速，结果反而更慢了——测试10000条记录时，去掉NULL值和对应列的精简版耗时6分钟，而全列指定（带NULL）的版本只需要3.5分钟。

为什么指定所有列并传入NULL的INSERT反而比仅指定部分列的更快？我猜是数据库要检查列的默认值（但实际上这些列都没有默认值），但没想到影响这么大。

DDL #

create sequence COMPANY_SEQ
INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE TABLE COMPANY 
(   CO_ID                   NUMBER(*,0)        NOT NULL ENABLE
, COUNTRY_CODE            CHAR(2 CHAR)       NOT NULL ENABLE
, COMPANY_NUMBER          VARCHAR2(15 CHAR)  NOT NULL ENABLE
, CO_SHORT_NAME           VARCHAR2(70 CHAR)  NOT NULL ENABLE
, CO_FULL_NAME            VARCHAR2(512 CHAR) NOT NULL ENABLE
, START_DATE              TIMESTAMP (6)      NOT NULL ENABLE
, DUMMY_FLAG              CHAR(1 CHAR)
, DATE_DELETED            TIMESTAMP (6)
, EXPIRY_DATE             TIMESTAMP (6)
, PUB_FLAG                CHAR(1 CHAR)
, DATE_CREATED            TIMESTAMP (6)      NOT NULL ENABLE
, DATE_UPDATED            TIMESTAMP (6)
, MODIFIED                TIMESTAMP (6)
, DUPLICATE_CO_ID         NUMBER(*,0)
, BUSINESS_PARTNER_NUMBER VARCHAR2(10 CHAR)
, PRINCIPAL_ACTIVITY      VARCHAR2(4 CHAR)
, ESTABLISHMENT           CHAR(1 BYTE)
, DATE_OF_ESTABLISHMENT   DATE
, PERSON_TYPE             CHAR(1 CHAR)
, CO_FULL_NAME_SEARCHABLE VARCHAR2(512 CHAR)
, SOURCE                  NUMBER(1,0)        DEFAULT NULL
, CO_TRADER_LOADED_ON     DATE
, RISK_FLAG               VARCHAR2(30 CHAR)
, IS_HARD_DELETED         CHAR(1 BYTE)
, DELETES_SENT_ON         DATE
, AMENDMENTS_SENT_ON      DATE
, DATE_OF_EST             DATE
, PERSON_PROTECTED        DATE
, ZI_CCNN                 VARCHAR2(17)
, UA_STATUS               CHAR(1)
, ZI_EST                  CHAR(1)
, ZI_CONSENT              CHAR(1)
, ZI_SIC_CODE             VARCHAR2(4)
, ZI_CCNN_START_DATE      DATE
, ZI_CCNN_END_DATE        DATE
, ZI_CREATED_DATE         TIMESTAMP
, ZI_UPDATED_DATE         TIMESTAMP
, CONSTRAINT PK_COMPANY_ID PRIMARY KEY (CO_ID)
, CONSTRAINT CCNN_UK UNIQUE (COUNTRY_CODE, COMPANY_NUMBER)
);
CREATE INDEX ECNO_CO_LOADED_ON         ON COMPANY (CO_TRADER_LOADED_ON) ;
CREATE INDEX ECNO_EXPIRED_ON           ON COMPANY (EXPIRY_DATE)  ;
CREATE INDEX ECNO_FULL_NAME_SEARCHABLE ON COMPANY (CO_FULL_NAME_SEARCHABLE) ;

完整INSERT语句 #

INSERT INTO COMPANY
(CO_ID,COUNTRY_CODE,COMPANY_NUMBER,CO_SHORT_NAME,CO_FULL_NAME,START_DATE,DUMMY_FLAG
,DATE_DELETED,EXPIRY_DATE,PUB_FLAG,DATE_CREATED,DATE_UPDATED,MODIFIED,DUPLICATE_CO_ID
,BUSINESS_PARTNER_NUMBER,PRINCIPAL_ACTIVITY,ESTABLISHMENT,DATE_OF_ESTABLISHMENT
,PERSON_TYPE,CO_FULL_NAME_SEARCHABLE,SOURCE,CO_TRADER_LOADED_ON,RISK_FLAG,IS_HARD_DELETED
,DELETES_SENT_ON,AMENDMENTS_SENT_ON,DATE_OF_EST,PERSON_PROTECTED,ZI_CCNN,UA_STATUS
,ZI_EST,ZI_CONSENT,ZI_SIC_CODE,ZI_CCNN_START_DATE,ZI_CCNN_END_DATE,ZI_CREATED_DATE
,ZI_UPDATED_DATE)
VALUES
(COMPANY_SEQ.NEXTVAL,'UK','002060453000','UK002060453000','UK002060453000'
,TO_DATE('2024-04-22 11:32:46','YYYY-MM-DD HH24:MI:SS'),'',NULL,NULL,'' 
,TO_DATE('2024-04-22 11:32:46','YYYY-MM-DD HH24:MI:SS'),NULL,NULL,NULL,'','','',NULL
,'','UK002060453000',NULL,NULL,'','',NULL,NULL,NULL,NULL,'','','','','',NULL,NULL,NULL
,NULL);

精简后INSERT语句 #

INSERT INTO COMPANY
(CO_ID,COUNTRY_CODE,COMPANY_NUMBER,CO_SHORT_NAME,CO_FULL_NAME,START_DATE
,DATE_CREATED,CO_FULL_NAME_SEARCHABLE)
VALUES
(COMPANY_SEQ.NEXTVAL,'UK','002060453000','UK002060453000','UK002060453000'
,TO_TIMESTAMP('2024-04-22 11:32:46','YYYY-MM-DD HH24:MI:SS')
,TO_TIMESTAMP('2024-04-22 11:32:46','YYYY-MM-DD HH24:MI:SS'),'UK002060453000');

执行计划 #

--------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |     1 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | COMPANY     |       |            |          |
|   2 |   SEQUENCE               | COMPANY_SEQ |       |            |          |
--------------------------------------------------------------------------------

两种语句的执行计划一致，除非操作有误。我尝试把脚本按列列表排序，将结构相似的语句分组，性能几乎恢复，但还是比全列插入慢约10%。不过脚本大小缩减了一半以上（接近1/3），这个结果或许可以接受。

核心原因解析 #

虽然执行计划表面一致，但Oracle处理两种INSERT的内部逻辑存在关键差异：

• 语句解析缓存命中率：全列INSERT的语句结构完全固定，数据库可以重复复用已解析的执行计划（软解析）；而精简版如果列列表有变化（哪怕是同一表的不同列组合），数据库需要反复执行硬解析，消耗额外CPU和时间。即便精简版列列表完全统一，全列版本的解析缓存复用效率依然更高。
• NULL填充与约束检查：即使列没有显式默认值，数据库处理未指定列时，仍需逐一确认列的默认值规则（包括系统默认NULL）、验证NOT NULL约束（哪怕非空列都已赋值）。这个检查过程在每条插入时都会执行，大量累积后开销显著。而全列INSERT直接指定所有列的值（含NULL），数据库可跳过这部分检查逻辑，直接写入数据块。
• 数据写入顺序匹配：全列INSERT的字段顺序与表的物理存储顺序完全一致，数据库可按顺序直接写入数据块，无需调整字段映射；精简版则需要将指定列映射到表的存储顺序，增加内存拷贝和顺序调整的开销，在大量插入时被放大。

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