create index index_20250116_1 on ACT_HI_PROCINST(EXT_CATEGORY);

条件过滤性不高，加个hint禁止hash连接，看下执行时间
select /+enbale_hash_join(0)/ t.id,
t.formid,
ahp.ext_title as title,
t.receive_no receiveNo,
t.serial_number serialNumber,
t.receive_unit receiveUnit,
t.security_classification securityClassification,
t.receive_time receiveTime,
t.createdDate,
t.flow_status as dealStatus,
t.createdBy,
t.createdOrg,
t.sponsor_unit sponsorUnit,
t.bln_delete,
t.file_content fileContent,
t.format_file formatFile,
ahp.ext_processstatus,
ahp.delete_reason_,
t.draft_unit_name draftUnitName,
t.flow_status,
ahp.proc_inst_id_,
t.gdzt,
t.archive_status from ACT_HI_PROCINST ahp
inner join goa_doc_receivefile t on t.id = ahp.BUSINESS_KEY_ and ahp.EXT_CATEGORY = 'goa_doc_receivefile'
order by t.createdDate desc

做个试验，看看是否有效果。

先查一下下面这个SQL

SELECT COUNT(1) AS TOT
      ,COUNT(CASE WHEN EXT_CATEGORY = 'goa_doc_receivefile' THEN 1 END) AS FIL
  FROM ACT_HI_PROCINST

看看查询出的符合 EXT_CATEGORY = 'goa_doc_receivefile' 条件记录占比大概多少。

如果占比很高，那说明索引扫描再回表的成本已经高于全表扫描，所以会走CSCN了。

如果占比低的话，试着加一个新索引，也就是现有索引调整一下字段顺序。

CREATE INDEX INDEX_TEST2 ON ACT_HI_PROCINST(EXT_CATEGORY,BUSINESS_KEY_);

然后修改一下查询的SQL，再查一下执行计划，看看是否能用上新索引，以及耗时是否有改善效果。

select t.id,
       t.formid,
       ahp.ext_title as title,
       t.receive_no receiveNo,
       t.serial_number serialNumber,
       t.receive_unit receiveUnit,
       t.security_classification securityClassification,
       t.receive_time receiveTime,
       t.createdDate,
       t.flow_status as dealStatus,
       t.createdBy,
       t.createdOrg,
       t.sponsor_unit sponsorUnit,
       t.bln_delete,
       t.file_content fileContent,
       t.format_file formatFile,
       ahp.ext_processstatus,
       ahp.delete_reason_,
       t.draft_unit_name draftUnitName,
       t.flow_status,
       ahp.proc_inst_id_,
       t.gdzt,
       t.archive_status 
  from ACT_HI_PROCINST ahp
 inner join goa_doc_receivefile t 
         on t.id = ahp.BUSINESS_KEY_ 
 where ahp.EXT_CATEGORY = 'goa_doc_receivefile'
 order by t.createdDate desc