ORACLE SQL使用cast、trunc及时区转换操作时如何提升查询性能

1. 索引优化（解决i.TABLA = p.TABLA关联慢问题） #

移除该关联条件后性能提升，本质是现有索引无法覆盖关联字段，导致关联时产生大量回表或全表扫描，可通过以下索引解决：

• 给IGRI.PROCEDIMIENTO表建立联合覆盖索引：IDX_PROC_ID_TAB_SEC (ID_PROCEDIMIENTO, TABLA, SECUENCIA, F_CREACION, USUARIO, GRUPO, ACCION, TEXTO, C1, C2, C3, ORIGEN_ACCION)，前三个字段匹配过滤+关联条件，后面追加查询需要的字段避免回表。
• 给IGRI.PROCEDIMIENTO_2表建立和上面结构完全相同的联合索引。
• 给IGRI.BOL_INC_DE_RED表建立联合覆盖索引：IDX_BOL_TAB_SEC (TABLA, SECUENCIA, TIPO_ELEMENTO, F_CREACION, FECHA_PRESENTACION, ESTADO, SEVERIDAD, NDAI)，同样覆盖关联条件和查询需要的字段。
• 给IGRI.EQUIPO、IGRI.SISTEMA表的SECUENCIA字段建立主键/唯一索引（如果还没有的话）。

2. 时区&计算逻辑优化（解决转换开销过高问题） #

时区转换优化 #

原有三层嵌套的时区转换逻辑开销极大，可做以下优化：

• 替换轻量化转换函数：把cast(from_tz(cast(xxx as timestamp),'GMT') at time zone 'CET' as date)直接替换为Oracle内置轻量函数NEW_TIME(xxx, 'GMT', 'CET')，性能可提升数倍，且结果完全一致。
• 预计算存储：如果该时区转换是高频查询逻辑，可以在表上建立生成列（虚拟列）并建索引，比如：

-- 给PROCEDIMIENTO表加预转换字段
ALTER TABLE IGRI.PROCEDIMIENTO ADD (F_CREACION_CET DATE GENERATED ALWAYS AS (NEW_TIME(F_CREACION, 'GMT', 'CET')) VIRTUAL);
CREATE INDEX IDX_PROC_FCREATE_CET ON IGRI.PROCEDIMIENTO(F_CREACION_CET);

BOL_INC_DE_RED表的F_CREACION、FECHA_PRESENTACION字段也按同样方式添加虚拟列，查询时直接引用虚拟列即可，完全消除实时转换开销。

时间差计算优化 #

原有TRUNC((p.F_CREACION-i.F_CREACION)*86400)计算逻辑可做优化：
确认两个字段都是GMT时区的前提下，无需转换时区后再计算差值，保持原有计算逻辑即可，配合前面的覆盖索引，不需要回表取字段就可以直接计算，开销会大幅降低。

3. SQL结构优化 #

• 替换标量子查询为左连接：原有CASE里的逐行标量子查询，在数据量大时开销极高，改成左批量关联即可，示例逻辑如下：

SELECT i.*, 
  COALESCE(E.inv_str, S.inv_str, 'ââââââââââ') AS INV
FROM IGRI.BOL_INC_DE_RED i
LEFT JOIN (SELECT SECUENCIA, MODO||'â'||COD_CLAVE||'â'||CLASE||'â'||TECNOLOGIA||'â'||TIPO_ELEMENTO_GENERICO||'â'||MODELO||'â'|| CATEGORIA_EDIFICIO ||'â'|| CODIGO_UBICACION ||'â'|| DESC_CLAVE ||'â'|| DESC_PRIMARIA ||'â'|| DESC_SECUNDARIA AS inv_str FROM IGRI.EQUIPO) E ON i.TIPO_ELEMENTO='Equipo' AND E.SECUENCIA=i.SECUENCIA
LEFT JOIN (SELECT SECUENCIA, MODO||'â'||COD_SISTEMA||'â'||CLASE||'â'||JERARQUIA||'â'||TIPO_ELEMENTO_GENERICO||'â'||'FREE6'||'â'||'FREE7'||'â'||'FREE8'||'â'||'FREE9'||'â'||'FREE10'||'â'||'FREE11' AS inv_str FROM IGRI.SISTEMA) S ON i.TIPO_ELEMENTO='Sistemas de Transporte' AND S.SECUENCIA=i.SECUENCIA

• 合并重复逻辑：原有UNION ALL的两个分支除了PROCEDIMIENTO表不同，其余逻辑完全一致，可以先将两个PROCEDIMIENTO表UNION ALL后再统一关联BOL_INC_DE_RED表，减少重复代码和解析开销。
• 简化分页逻辑：如果是Oracle 12c及以上版本，把三层嵌套的排序分页改成ORDER BY ID_PROCEDIMIENTO FETCH FIRST 3000 ROWS ONLY语法，优化器可以更高效的执行分页逻辑，同时把ROWNUM <= '3000'里的字符串改成数字ROWNUM <= 3000，避免隐式类型转换。
• 移除无用字段：SELECT列表里大量无意义的空字符串''如果不需要可以直接删除，减少数据传输和内存开销。

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