Oracle中含COUNT的查询性能优化及添加条件无结果问题排查

AI 小北

2026-7-18

Oracle COUNT(*)查询添加条件后性能优化方案

问题描述

在Oracle数据库执行包含COUNT(*)的SQL查询时，添加AND US.ASESOR = 'Olga Rodriguez '条件后，查询运行极慢甚至无法返回结果，但单独执行子查询可正常获取数据，现针对该SQL提供性能优化方案。

原SQL代码

SELECT MEDIO, COUNT(*) AS TOTAL FROM (SELECT * FROM ALUMNOS_CONTACTADOS  C
INNER JOIN TBLCATALU TB ON C.IDECATALU = TB.IDECATALU
INNER JOIN (SELECT AC.NOMASE||' '||AC.PATASE||' '||AC.MATASE AS ASESOR, AC.USUAPE, USUARIO_SISTEMA_CLAVE AS USERAPEX 
            FROM TBLASECRM AC
            INNER JOIN USUARIOS_SISTEMA US ON AC.USUAPE = US.USUARIO_SISTEMA_ID ) US ON C.USUINSREG = US.USERAPEX
LEFT JOIN (SELECT AC2.NOMASE||' '||AC2.PATASE||' '||AC2.MATASE AS ASESOR2, AC2.USUAPE AS USUAPE_MOD
            FROM TBLASECRM AC2
            INNER JOIN USUARIOS_SISTEMA US ON AC2.USUAPE = US.USUARIO_SISTEMA_ID) US2 ON C.USUMODREG = US2.USUAPE_MOD
WHERE C.STATUS = 'A'
    AND (--(:P483_FECHA_FIN IS NULL AND :P483_FECHA_INICIO IS NULL)
         --OR 
         (TO_DATE(C.FECINSREG,'DD/MM/YY') BETWEEN TO_DATE('01/01/23','DD/MM/YY')   AND  TO_DATE('23/06/23','DD/MM/YY')) )
   AND('DANIEL.C' IN ('DANIEL.C','BRENDA','MARIO') OR( (C.USUMODREG IN (SELECT AC.USUAPE FROM TBLASECRM AC
                                INNER JOIN TBLASECRM AC2 ON AC.SUPASE_ID = AC2.PKIDASE AND ac2.DESPUE = 'Supervisor'
                                INNER JOIN USUARIOS_SISTEMA US ON AC2.USUAPE = US.USUARIO_SISTEMA_ID
                                WHERE US.USUARIO_SISTEMA_CLAVE= 'DANIEL.C'
                       union 
                                select USUARIO_SISTEMA_ID from USUARIOS_SISTEMA where USUARIO_SISTEMA_CLAVE= 'DANIEL.C') 
        OR C.USUINSREG IN ( SELECT  USUARIO_SISTEMA_CLAVE FROM USUARIOS_SISTEMA WHERE USUARIO_SISTEMA_ID IN (
                            SELECT AC.USUAPE FROM TBLASECRM AC
                                INNER JOIN TBLASECRM AC2 ON AC.SUPASE_ID = AC2.PKIDASE AND ac2.DESPUE = 'Supervisor'
                                INNER JOIN USUARIOS_SISTEMA US ON AC2.USUAPE = US.USUARIO_SISTEMA_ID
                                WHERE US.USUARIO_SISTEMA_CLAVE= 'DANIEL.C')
                         union 
                                select USUARIO_SISTEMA_CLAVE from USUARIOS_SISTEMA where USUARIO_SISTEMA_CLAVE= 'DANIEL.C')   )
    OR
        ( C.USUMODREG = (SELECT US.USUARIO_SISTEMA_ID FROM TBLASECRM AC
                        INNER JOIN USUARIOS_SISTEMA US ON AC.USUAPE = US.USUARIO_SISTEMA_ID
                           WHERE AC.DESPUE = 'Asesor Académico'
                           AND US.USUARIO_SISTEMA_CLAVE= 'DANIEL.C') 
        OR C.USUINSREG = (SELECT US.USUARIO_SISTEMA_CLAVE FROM TBLASECRM AC
                        INNER JOIN USUARIOS_SISTEMA US ON AC.USUAPE = US.USUARIO_SISTEMA_ID
                           WHERE AC.DESPUE = 'Asesor Académico'
                           AND US.USUARIO_SISTEMA_CLAVE= 'DANIEL.C') )
      )) AND US.ASESOR = 'Olga Rodriguez '
        
                                     ) --WHERE ASESOR = 'Olga Rodriguez '
      GROUP BY MEDIO;

优化方案

1. 提前过滤数据，缩小关联范围

将US.ASESOR = 'Olga Rodriguez '条件直接加入US子查询，避免先关联全量数据再过滤，大幅减少后续关联的数据量：

-- 修改后的US子查询
SELECT AC.NOMASE||' '||AC.PATASE||' '||AC.MATASE AS ASESOR, AC.USUAPE, US.USUARIO_SISTEMA_CLAVE AS USERAPEX 
FROM TBLASECRM AC
INNER JOIN USUARIOS_SISTEMA US ON AC.USUAPE = US.USUARIO_SISTEMA_ID
WHERE AC.NOMASE||' '||AC.PATASE||' '||AC.MATASE = 'Olga Rodriguez '

同时检查C.FECINSREG字段类型：如果是日期类型，直接用日期比较（避免TO_DATE函数导致索引失效）；如果是字符串存储日期，优先修改字段类型为日期型，临时方案可创建基于TO_DATE(C.FECINSREG,'DD/MM/YY')的函数索引。

2. 复用重复子查询，避免重复计算

原SQL存在多处重复子查询逻辑，用CTE（公共表表达式）提前计算结果并复用，减少重复执行开销：

WITH USER_CTE AS (
    -- 获取DANIEL.C对应的主管下属用户ID
    SELECT AC.USUAPE 
    FROM TBLASECRM AC
    INNER JOIN TBLASECRM AC2 ON AC.SUPASE_ID = AC2.PKIDASE AND AC2.DESPUE = 'Supervisor'
    INNER JOIN USUARIOS_SISTEMA US ON AC2.USUAPE = US.USUARIO_SISTEMA_ID
    WHERE US.USUARIO_SISTEMA_CLAVE= 'DANIEL.C'
    UNION 
    SELECT USUARIO_SISTEMA_ID 
    FROM USUARIOS_SISTEMA 
    WHERE USUARIO_SISTEMA_CLAVE= 'DANIEL.C'
),
USER_CLAVE_CTE AS (
    -- 转换用户ID为CLAVE
    SELECT USUARIO_SISTEMA_CLAVE 
    FROM USUARIOS_SISTEMA 
    WHERE USUARIO_SISTEMA_ID IN (SELECT * FROM USER_CTE)
    UNION 
    SELECT USUARIO_SISTEMA_CLAVE 
    FROM USUARIOS_SISTEMA 
    WHERE USUARIO_SISTEMA_CLAVE= 'DANIEL.C'
),
ASESOR_DANIEL_CTE AS (
    -- 获取DANIEL.C作为学术顾问的对应ID和CLAVE
    SELECT US.USUARIO_SISTEMA_ID, US.USUARIO_SISTEMA_CLAVE
    FROM TBLASECRM AC
    INNER JOIN USUARIOS_SISTEMA US ON AC.USUAPE = US.USUARIO_SISTEMA_ID
    WHERE AC.DESPUE = 'Asesor Académico'
      AND US.USUARIO_SISTEMA_CLAVE= 'DANIEL.C'
)
SELECT MEDIO, COUNT(*) AS TOTAL 
FROM (
    SELECT TB.MEDIO -- 仅查询需要的字段，避免SELECT *
    FROM ALUMNOS_CONTACTADOS  C
    INNER JOIN TBLCATALU TB ON C.IDECATALU = TB.IDECATALU
    INNER JOIN (
        SELECT AC.NOMASE||' '||AC.PATASE||' '||AC.MATASE AS ASESOR, AC.USUAPE, US.USUARIO_SISTEMA_CLAVE AS USERAPEX 
        FROM TBLASECRM AC
        INNER JOIN USUARIOS_SISTEMA US ON AC.USUAPE = US.USUARIO_SISTEMA_ID
        WHERE AC.NOMASE||' '||AC.PATASE||' '||AC.MATASE = 'Olga Rodriguez '
    ) US ON C.USUINSREG = US.USERAPEX
    -- US2为左连接且未被最终查询使用，直接移除减少关联开销
    WHERE C.STATUS = 'A'
      AND TO_DATE(C.FECINSREG,'DD/MM/YY') BETWEEN TO_DATE('01/01/23','DD/MM/YY') AND TO_DATE('23/06/23','DD/MM/YY')
      AND (
          'DANIEL.C' IN ('DANIEL.C','BRENDA','MARIO') 
          OR (
              C.USUMODREG IN (SELECT * FROM USER_CTE) 
              OR C.USUINSREG IN (SELECT * FROM USER_CLAVE_CTE)
          )
          OR (
              C.USUMODREG = (SELECT USUARIO_SISTEMA_ID FROM ASESOR_DANIEL_CTE) 
              OR C.USUINSREG = (SELECT USUARIO_SISTEMA_CLAVE FROM ASESOR_DANIEL_CTE)
          )
      )
)
GROUP BY MEDIO;

3. 优化索引策略

为ALUMNOS_CONTACTADOS创建覆盖过滤和关联条件的联合索引：

CREATE INDEX IDX_ALUMNOS_CONTACTADOS_FILTER ON ALUMNOS_CONTACTADOS(STATUS, FECINSREG, USUINSREG, USUMODREG);

若必须用字符串拼接过滤顾问名称，为TBLASECRM创建函数索引：

CREATE INDEX IDX_TBLASECRM_ASESOR ON TBLASECRM(NOMASE||' '||PATASE||' '||MATASE);

检查所有关联字段（如C.IDECATALU、AC.USUAPE、US.USUARIO_SISTEMA_ID）是否已有主键/唯一索引，确保关联时快速定位数据。

4. 其他细节优化

避免SELECT *，仅查询最终需要的字段，减少数据传输和内存占用。
确认US.ASESOR = 'Olga Rodriguez '末尾的空格是否为数据实际存在，若为多余空格，可改用TRIM(US.ASESOR) = 'Olga Rodriguez'（但会导致索引失效，优先确认数据准确性）。
执行EXPLAIN PLAN FOR对比原查询与优化后查询的执行计划，定位剩余瓶颈。

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