bom dia a todos.
O select abaixo está executando com um tempo de 20s, e gostaria de pedir a
ajuda para poder estar melhorando.
Segue abaixo o script e em anexo o plano de execução.
Obrigado.
select cd_terminal as "cd"
, cast(data_captura as timestamp) as "dataUltima"
, cast(data_adm as timestamp) as "dataFechamento"
, CASE tipoUltima WHEN 4 THEN 'SAIDA' ELSE 'ENTRADA' END as "fluxo"
, valorSaques as "valorSaques"
, valorRecargas as "valorRecargas"
, valorDepositos as "valorDepositos"
, totalSaques as "totalSaques"
, totalRecargas as "totalRecargas"
, totalDepositos as "totalDepositos"
, denominacoes as "denominacaoUltima"
, dados_adm as "denominacaoFechamento" from (
with r as (
select t.cd_terminal
, c.cd_tipo_transacao as tipo
, sum(t.valor) as valor
, count(*) as total
from transacao_siscap t, codigo_transacao c, (
select busca.terminal
, busca.tipo as tipoUltima
, busca.data_captura
, busca.denominacoes
, busca.data_adm
, busca.dados_adm
from (
select trn.cd_terminal as terminal, trn.*, adm.* from (
select ultima_adm.cd_terminal,
data_captura as data_adm,
dados_adm
from (
select adm.cd_terminal, nvl(max(id), 0) as id
from transacao_adm_siscap adm,
terminal atm
where adm.cd_terminal = atm.cd_terminal
and cd_proc = 310000
and atm.ativo = 1
group by adm.cd_terminal
) ultima_adm, transacao_adm_siscap adm
where ultima_adm.id = adm.id
) adm, (
select trn.cd_terminal, cod.cd_tipo_transacao as tipo
, trn.data_captura as data_captura
, e.denominacao_movimentada as denominacoes from (
select trn.cd_terminal, max(data_captura) as data_captura
from transacao_siscap trn, transacao_ext_siscap e, terminal atm
where trn.cd_terminal = atm.cd_terminal
and trn.id = e.transacao_id
and e.denominacao_movimentada is not null
and atm.ativo = 1
and cd_status in (03, 04, 05)
group by trn.cd_terminal
) ultima, transacao_siscap trn, transacao_ext_siscap e,
codigo_transacao cod
where trn.cd_terminal = ultima.cd_terminal
and cod.cd_proc = trn.cd_proc
and ultima.data_captura = trn.data_captura
and trn.id = e.transacao_id
) trn
where adm.cd_terminal = trn.cd_terminal
) busca
) busca1
where t.cd_proc = c.cd_proc
and t.cd_terminal = busca1.terminal
and t.data_captura between busca1.data_adm and busca1.data_captura
and t.cd_status IN ('03', '04', '05')
and c.cd_tipo_transacao in (7,4,2,13)
group by t.cd_terminal
, c.cd_tipo_transacao
) select t.cd_terminal AS cd_terminal
, nvl((select valor from r where tipo = 4 and r.cd_terminal =
t.cd_terminal), 0) as valorSaques
, nvl((select valor from r where tipo = 7 and r.cd_terminal =
t.cd_terminal), 0)
+nvl((select valor from r where tipo = 13 and r.cd_terminal =
t.cd_terminal), 0) as valorRecargas
, nvl((select valor from r where tipo = 2 and r.cd_terminal =
t.cd_terminal), 0) as valorDepositos
, nvl((select total from r where tipo = 4 and r.cd_terminal =
t.cd_terminal), 0) as totalSaques
, nvl((select total from r where tipo = 7 and r.cd_terminal =
t.cd_terminal), 0)
+nvl((select total from r where tipo = 13 and r.cd_terminal =
t.cd_terminal), 0) as totalRecargas
, nvl((select total from r where tipo = 2 and r.cd_terminal =
t.cd_terminal), 0) as totalDepositos
from terminal t
where t.ativo = 1
) busca2, (
select busca.terminal
, busca.tipoUltima
, busca.data_captura
, busca.denominacoes
, busca.data_adm
, busca.dados_adm
from (
select trn.cd_terminal as terminal, trn.cd_tipo_transacao as
tipoUltima, trn.*, adm.* from (
select ultima_adm.cd_terminal,
data_captura as data_adm,
dados_adm
from (
select adm.cd_terminal, nvl(max(id), 0) as id
from transacao_adm_siscap adm,
terminal atm
where adm.cd_terminal = atm.cd_terminal
and cd_proc = 310000
and atm.ativo = 1
group by adm.cd_terminal
) ultima_adm, transacao_adm_siscap adm
where ultima_adm.id = adm.id
) adm, (
select trn.cd_terminal
, c.cd_tipo_transacao
, trn.data_captura as data_captura
, e.denominacao_movimentada as denominacoes from (
select trn.cd_terminal, max(data_captura) as data_captura
from transacao_siscap trn, transacao_ext_siscap e, terminal atm
where trn.cd_terminal = atm.cd_terminal
and trn.id = e.transacao_id
and e.denominacao_movimentada is not null
and atm.ativo = 1
and cd_status in (03, 04, 05)
group by trn.cd_terminal
) ultima, transacao_siscap trn, transacao_ext_siscap e,
codigo_transacao c
where trn.cd_terminal = ultima.cd_terminal
and trn.cd_proc = c.cd_proc
and ultima.data_captura = trn.data_captura
and trn.id = e.transacao_id
) trn
where adm.cd_terminal = trn.cd_terminal
) busca
) busca3
where busca2.cd_terminal = busca3.terminal
[As partes desta mensagem que não continham texto foram removidas]
