在Oracle中使用count时，如何提高查询的性能？

i7uaboj4 于 2023-06-29 发布在 Oracle

关注(0)|答案(2)|浏览(92)

由于某些原因，当添加AND US.ASESOR = 'Olga Rodriguez '时，查询速度变慢并且不生成结果。然而，如果我执行内部查询（使用FROM的查询），它确实给我带来了我需要的东西，但是当实现COUNT（*）时，它没有，我可以做些什么来提高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;

oracle

来源：https://stackoverflow.com/questions/76542625/how-can-i-improve-the-performance-of-my-query-when-using-count-in-oracle

2条答案

按热度按时间

x7yiwoj41#

'DANIEL.C' IN ('DANIEL.C','BRENDA','MARIO')将始终为true，而true OR anything为true，因此您可以忽略查询的anything部分：
如果您整齐地格式化查询：

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;

然后你可以看到你可以删除一半以上的查询：

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     TO_DATE(C.FECINSREG,'DD/MM/YY') BETWEEN DATE '2023-01-01'
                                          AND     DATE '2023-06-23'
  AND     US.ASESOR = 'Olga Rodriguez '
)
--WHERE ASESOR = 'Olga Rodriguez '
GROUP BY MEDIO;

然后进一步：

SELECT MEDIO,
       COUNT(*) AS TOTAL
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    TO_DATE(C.FECINSREG,'DD/MM/YY') BETWEEN DATE '2023-01-01'
                                       AND     DATE '2023-06-23'
AND    US.ASESOR = 'Olga Rodriguez '
GROUP BY MEDIO;

赞(0）回复(0）举报 2023-06-29

42fyovps2#

如果只有一个列和count as total需要作为查询的输出，则可以避免所有其他查询列格式和 *。
请尝试下面的工作

Select MEDIO , count(*)over(partition by MEDIO order by MEDIO) as Total from  ALUMNOS_CONTACTADOS  C
---all code
---------- instead 
SELECT MEDIO, COUNT(*) AS TOTAL FROM (SELECT * FROM ALUMNOS_CONTACTADOS  C
----all code
)

赞(0）回复(0）举报 2023-06-29

我来回答

在Oracle中使用count时，如何提高查询的性能？

2条答案

相关问题

热门标签

最新问答