Improve select statement performance

Question

I'm with an Select performance issue, how can I make this SQL more efficient?

SELECT
 O.DT_OCORRENCIA,
 S.NU_SMP,
 CLI.NM_APELIDO CLI,
 TRANS.NM_ENTIDADE TRANS,
 TPO.DS_TIPO_OCORRENCIA,
 O.DS_OCORRENCIA,
 REPLACE(FNC_RETORNA_MOTORISTAS(S.CD_SMP), '<br>', ';') AS NOME_MOTORISTA,
 OG.DS_NOME AS DS_NOME_ORIGEM,
 DG.DS_NOME AS DS_NOME_DESTINO,
 U.NM_LOGIN AS DS_USUARIO_OCORRENCIA,
 CASE
   WHEN O.CD_USUARIO_INFORMOU = 717 THEN
    'NAO'
   ELSE
    'SIM'
 END FL_MANUAL,
 CASE
   WHEN (O.FL_ENVIOU_EMAIL_CLIENTE = 'S' OR O.FL_ENVIOU_EMAIL_TRANSP = 'S') THEN
    'SIM'
   ELSE
    'NÃO'
 END ENVIOU_EMAIL,
 DECODE(O.NU_LOCAL_OCORRENCIA,
        '1',
        'Origem',
        '2',
        'Em transito',
        '3',
        'Alvos',
        '4',
        'Indiferente') DS_LOCAL_OCORRENCIA,
 TOPER.DS_TIPO_OPERACAO
  FROM TB_SMP S
 INNER JOIN TB_OCORRENCIA O
    ON O.CD_SMP = S.CD_SMP
 INNER JOIN TB_TIPO_OCORRENCIA TPO
    ON O.CD_TIPO_OCORRENCIA = TPO.CD_TIPO_OCORRENCIA
  LEFT JOIN TB_TIPO_OPERACAO TOPER
    ON TOPER.CD_TIPO_OPERACAO = S.CD_TIPO_OPERACAO
  LEFT JOIN TB_USUARIO U
    ON U.CD_USUARIO = O.CD_USUARIO_INFORMOU
  LEFT JOIN TB_ENTIDADE CLI
    ON CLI.CD_ENTIDADE = S.CD_ENTIDADE_CLIENTE
  LEFT JOIN TB_ENTIDADE TRANS
    ON TRANS.CD_ENTIDADE = S.CD_ENTIDADE_TRANSPORTADOR
  LEFT JOIN TB_PONTO_GEOREFERENCIADO OG
    ON OG.CD_PONTO_GEOREFERENCIADO = S.CD_PONTO_GEO_ORIGEM
  LEFT JOIN TB_PONTO_GEOREFERENCIADO DG
    ON (S.CD_PONTO_GEO_DESTINO IS NULL AND
       DG.CD_PONTO_GEOREFERENCIADO =
       FNC_GET_ULTIMA_ENTREGA_PONTO(S.CD_SMP))
    OR (S.CD_PONTO_GEO_DESTINO IS NOT NULL AND
       DG.CD_PONTO_GEOREFERENCIADO = S.CD_PONTO_GEO_DESTINO)

 WHERE EXISTS (SELECT 1
          FROM TB_TIPO_OCORRENCIA T
         WHERE T.CD_TIPO_OCORRENCIA = O.CD_TIPO_OCORRENCIA)

   AND S.DT_CADASTRO BETWEEN to_date('21/04/2014', 'dd/MM/yyyy') AND to_date('09/06/2014', 'dd/MM/yyyy')
   AND O.DT_OCORRENCIA BETWEEN to_date('10/05/2014','dd/MM/yyyy') AND to_date('20/05/2014','dd/MM/yyyy')
   AND S.cd_tipo_operacao in
       (-1, 27, 11, 13, 37, 6, 7, 21, 12, 36, 28, 4, 5)

-- order by using linq?
 ORDER BY CLI.NM_APELIDO,
          TRANS.NM_APELIDO,
          O.DT_OCORRENCIA,
          DS_TIPO_OCORRENCIA

Here goes my execution plan: explain plan

PS.: I don't know nothing about sql tunnig :(

You can remove `WHERE EXISTS (SELECT 1 FROM TB_TIPO_OCORRENCIA T WHERE T.CD_TIPO_OCORRENCIA = O.CD_TIPO_OCORRENCIA)` as you already INNER JOIN `TB_OCORRENCIA` and `TB_TIPO_OCORRENCIA` earlier. — Joseph B, May 30 '14 at 14:13
Doesn't Oracle store time as part of the `DATE` type? Then you want to avoid [the inclusive upper-bound found in `BETWEEN`](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) (yes, this applies to _all_ systems with a continuous-range type). I'm curious whether you could switch `DS_TIPO_OCORRENCIA` in the `ORDER BY` to `O.DS_OCORRENCIA` (same-table usually means better likelihood of index use). — Clockwork-Muse, May 30 '14 at 14:49

Joseph B · Answer 1 · 2014-05-30T14:22:43.767

You can move some of the WHERE conditions to the JOIN conditions as you are doing an INNER JOIN on these tables, as below. Then, you would be operating on a smaller data set when you come to the LEFT JOIN conditions.

SELECT
 O.DT_OCORRENCIA,
 S.NU_SMP,
 CLI.NM_APELIDO CLI,
 TRANS.NM_ENTIDADE TRANS,
 TPO.DS_TIPO_OCORRENCIA,
 O.DS_OCORRENCIA,
 REPLACE(FNC_RETORNA_MOTORISTAS(S.CD_SMP), '<br>', ';') AS NOME_MOTORISTA,
 OG.DS_NOME AS DS_NOME_ORIGEM,
 DG.DS_NOME AS DS_NOME_DESTINO,
 U.NM_LOGIN AS DS_USUARIO_OCORRENCIA,
 CASE
   WHEN O.CD_USUARIO_INFORMOU = 717 THEN
    'NAO'
   ELSE
    'SIM'
 END FL_MANUAL,
 CASE
   WHEN (O.FL_ENVIOU_EMAIL_CLIENTE = 'S' OR O.FL_ENVIOU_EMAIL_TRANSP = 'S') THEN
    'SIM'
   ELSE
    'NÃO'
 END ENVIOU_EMAIL,
 DECODE(O.NU_LOCAL_OCORRENCIA,
        '1',
        'Origem',
        '2',
        'Em transito',
        '3',
        'Alvos',
        '4',
        'Indiferente') DS_LOCAL_OCORRENCIA,
 TOPER.DS_TIPO_OPERACAO
  FROM TB_SMP S
 INNER JOIN TB_OCORRENCIA O
    ON O.CD_SMP = S.CD_SMP
   AND S.DT_CADASTRO BETWEEN to_date('21/04/2014', 'dd/MM/yyyy') AND to_date('09/06/2014', 'dd/MM/yyyy')
   AND O.DT_OCORRENCIA BETWEEN to_date('10/05/2014','dd/MM/yyyy') AND to_date('20/05/2014','dd/MM/yyyy')
   AND S.cd_tipo_operacao in
       (-1, 27, 11, 13, 37, 6, 7, 21, 12, 36, 28, 4, 5)
 INNER JOIN TB_TIPO_OCORRENCIA TPO
    ON O.CD_TIPO_OCORRENCIA = TPO.CD_TIPO_OCORRENCIA
  LEFT JOIN TB_TIPO_OPERACAO TOPER
    ON TOPER.CD_TIPO_OPERACAO = S.CD_TIPO_OPERACAO
  LEFT JOIN TB_USUARIO U
    ON U.CD_USUARIO = O.CD_USUARIO_INFORMOU
  LEFT JOIN TB_ENTIDADE CLI
    ON CLI.CD_ENTIDADE = S.CD_ENTIDADE_CLIENTE
  LEFT JOIN TB_ENTIDADE TRANS
    ON TRANS.CD_ENTIDADE = S.CD_ENTIDADE_TRANSPORTADOR
  LEFT JOIN TB_PONTO_GEOREFERENCIADO OG
    ON OG.CD_PONTO_GEOREFERENCIADO = S.CD_PONTO_GEO_ORIGEM
  LEFT JOIN TB_PONTO_GEOREFERENCIADO DG
    ON (S.CD_PONTO_GEO_DESTINO IS NULL AND
       DG.CD_PONTO_GEOREFERENCIADO =
       FNC_GET_ULTIMA_ENTREGA_PONTO(S.CD_SMP))
    OR (S.CD_PONTO_GEO_DESTINO IS NOT NULL AND
       DG.CD_PONTO_GEOREFERENCIADO = S.CD_PONTO_GEO_DESTINO)
-- order by using linq?
 ORDER BY CLI.NM_APELIDO,
          TRANS.NM_APELIDO,
          O.DT_OCORRENCIA,
          DS_TIPO_OCORRENCIA;

Adding indexes on the columns that are used for joining / filtering would help as well, especially on

TB_SMP.DT_CADASTRO
TB_SMP.cd_tipo_operacao
TB_OCORRENCIA.DT_OCORRENCIA
TB_ENTIDADE.CD_ENTIDADE

... I generally recommend only moving those conditions to a `JOIN` that directly reference that table. When the table is in the `FROM` clause, leave conditions that reference only that table in the `WHERE` clause. For the `BETWEEN` condition, the RDBMS is likely doing this automatically, so won't (likely) gain any benefits in this case. — Clockwork-Muse, May 30 '14 at 14:34
For inner joins, it should make a difference whether the conditions are in the `on` clause or the `where` clause. The Oracle optimizer does a pretty good job. — Gordon Linoff, May 30 '14 at 20:31

Improve select statement performance

1 Answers1