Otimizar SQL que leva uns 50 minutos para executar

[neotheone 0]

Fala aí galera do imaster beleza?!

 

Estou executando uma consulta em uma tabela com aproximadamente (411.000.000) registros, é isso mesmo,

411 milhões de registros. Essa consulta leva aproximadamente entre 40 a 50 minutos para executar, gostaria

de saber por obséquio se alguem tem alguma solução para otimizar esse raio dessa consulta.

 

 

Query

SELECT 
                entity,
                sum(duration) AS duracao
          FROM
                mne_e_cif
          WHERE
                substr(entity,3,1) in ('8','7') AND 
                TO_DATE (starttime, 'DD/MM/YYY') = TO_DATE (SYSDATE -1, 'DD/MM/YYY')
          GROUP BY entity

-------------------------------------\\---------------------------------------------------

Estrutura da tabela

ENTITY                         NOT NULL VARCHAR2(31)                                                                                                                                                                                  
STARTTIME                      NOT NULL DATE                                                                                                                                                                                          
ENDTIME                                 DATE                                                                                                                                                                                          
ACCOUNT_TYPE                   NOT NULL NUMBER(3)                                                                                                                                                                                     
DURATION                       NOT NULL NUMBER(15)                                                                                                                                                                                    
A_NUMBER                                VARCHAR2(31)                                                                                                                                                                                  
B_NUMBER                                VARCHAR2(31)                                                                                                                                                                                  
DESTINATION                    NOT NULL VARCHAR2(31)                                                                                                                                                                                  
CDR_PAYMENT_VALUE                       VARCHAR2(1)                                                                                                                                                                                   
TRAFFIC_TYPE                            VARCHAR2(1)                                                                                                                                                                                   
PRS                                     VARCHAR2(2)                                                                                                                                                                                   
FREEPHONE                               VARCHAR2(1)                                                                                                                                                                                   
ORIGINATING_DEVICE                      VARCHAR2(2)                                                                                                                                                                                   
TERMINATING_DEVICE                      VARCHAR2(2)                                                                                                                                                                                   
DESTINATION_IDENTIFIER                  VARCHAR2(31)                                                                                                                                                                                  
CALL_VALUE                              FLOAT(15)                                                                                                                                                                                     
OPERATOR_CONNECTED                      VARCHAR2(1)                                                                                                                                                                                   
REVERSE_CHARGE                          VARCHAR2(1)                                                                                                                                                                                   
CHARGE_ADVICE                           VARCHAR2(1)                                                                                                                                                                                   
INCOMPLETE_CALL                         VARCHAR2(1)                                                                                                                                                                                   
CALL_DIVERT                             VARCHAR2(1)                                                                                                                                                                                   
CALL_TRANSFER                           VARCHAR2(1)                                                                                                                                                                                   
MULTI_PARTY                             VARCHAR2(1)                                                                                                                                                                                   
CONF_CALL                               VARCHAR2(1)                                                                                                                                                                                   
EMERGENCY_CALL                          VARCHAR2(1)                                                                                                                                                                                   
DIRECTORY_CALL                          VARCHAR2(1)                                                                                                                                                                                   
PARTIAL_CDR                             NUMBER(1)                                                                                                                                                                                     
ROAMER                                  NUMBER(1)                                                                                                                                                                                     
IMSI                                    VARCHAR2(15)                                                                                                                                                                                  
EQUIPMENTID                             VARCHAR2(15)                                                                                                                                                                                  
CELLID                                  VARCHAR2(31)                                                                                                                                                                                  
TERMINATING_CELL_ID                     VARCHAR2(31)                                                                                                                                                                                  
ORIGINATING_SWITCH                      VARCHAR2(15)                                                                                                                                                                                  
CDQ_CUSTOM_FIELD_1                      VARCHAR2(31)                                                                                                                                                                                  
CDQ_CUSTOM_FIELD_3                      VARCHAR2(31)                                                                                                                                                                                  
CDQ_CUSTOM_FIELD_6                      VARCHAR2(31)                                                                                                                                                                                  
RATING                                  NUMBER(10,3)                                                                                                                                                                                  
LATITUDE                                FLOAT(10)                                                                                                                                                                                     
LONGITUDE                               FLOAT(10)                                                                                                                                                                                     
CALL_TYPE                               NUMBER(5)                                                                                                                                                                                     
STORED                         NOT NULL DATE                                                                                                                                                                                          
PARTNUM                        NOT NULL NUMBER(10)                                                                                                                                                                                    
ARCHIVED                                CHAR(1) 

[Motta 645]

1) TO_DATE (starttime, 'DD/MM/YYY') = TO_DATE (SYSDATE -1, 'DD/MM/YYY')

desnecessário, faça

 

trunc(starttime) = trunc(SYSDATE - 1)

 

2) substr(entity,3,1) in ('8','7') AND trunc(starttime) = trunc(SYSDATE - 1)

isto provavelmente faz um FULL TABLE SCAN pois não existem indices para isto.

 

3) Você executou a EXPLAIN PLAN desta query ?

 

4) Deve existir algum indice por entity, existe alguma forma de pesquisar por ela sem usar substr? O uso do substr talvez sugira que esta coluna na realidade reflete mais um dado necessitando uma normalização.

 

Em último caso, caso seja realmente necessário otimizar, pode-se criar um indice baseado em funcão por

substr(entity,3,1)

trunc(starttime)

mas criar indices pode acarretar na dininuição do tempo de insert/update.

[neotheone 0]

1) TO_DATE (starttime, 'DD/MM/YYY') = TO_DATE (SYSDATE -1, 'DD/MM/YYY')

desnecessário, faça

 

trunc(starttime) = trunc(SYSDATE - 1)

 

2) substr(entity,3,1) in ('8','7') AND trunc(starttime) = trunc(SYSDATE - 1)

isto provavelmente faz um FULL TABLE SCAN pois não existem indices para isto.

 

3) Você executou a EXPLAIN PLAN desta query ?

 

4) Deve existir algum indice por entity, existe alguma forma de pesquisar por ela sem usar substr? O uso do substr talvez sugira que esta coluna na realidade reflete mais um dado necessitando uma normalização.

 

Em último caso, caso seja realmente necessário otimizar, pode-se criar um indice baseado em funcão por

substr(entity,3,1)

trunc(starttime)

mas criar indices pode acarretar na dininuição do tempo de insert/update.

 

 

Fala aí motta beleza?

 

Bem, eu aprendi que pra fazer operações com data, devemos utilizar o TO_DATE, senão não funciona, então gostaria de saber o que a função trunc faz exatamente.

 

Eu utilizo o substr(entity, 3, 1) para pegar o primeiro número após o (DD)87251212 do telefone, na verdade entity é um telefone.

[Motta 645]

Trunc em uma data tira a informação da hora (se houver) jogando a data para 00:00:00

 

Uma solução para não usar funcões com a data no caso seria

 

starttime between to_date(to_char((sysdate-1),'yyyymmdd')||'000000','yyyymmddhhh24mmss') and 
                  to_date(to_char((sysdate-1),'yyyymmdd')||'235959','yyyymmddhhh24mmss') 

É feita uma conversão para montar o intervalo de data pedida.

Se houver um índice por starttime pode resolver o problema pois será feita uma poderação entre o tamanha da base e a quantidade de registros que serão lidos pelo índice.

 

A questão seria ponderar se vale a pena ou criar criar um ínice apenas para esta pesquisa, fora isto não há muito o que fazer.

[neotheone 0]

Trunc em uma data tira a informação da hora (se houver) jogando a data para 00:00:00

 

Uma solução para não usar funcões com a data no caso seria

 

starttime between to_date(to_char((sysdate-1),'yyyymmdd')||'000000','yyyymmddhhh24mmss') and 
                  to_date(to_char((sysdate-1),'yyyymmdd')||'235959','yyyymmddhhh24mmss') 

É feita uma conversão para montar o intervalo de data pedida.

Se houver um índice por starttime pode resolver o problema pois será feita uma poderação entre o tamanha da base e a quantidade de registros que serão lidos pelo índice.

 

A questão seria ponderar se vale a pena ou criar criar um ínice apenas para esta pesquisa, fora isto não há muito o que fazer.

 

Na minha tabela starttime está declarado como Date, é necessario colocar TO_DATE sempre que eu for trabalhar com datas?

 

Se eu fosse comparar: starttime = '19/07/10' não funcionaria?

[Motta 645]

O Oracle trata data de maneira realmente meio chata.

 

Mas ele faz a conversão char <> date de forma automática para o formato de string padrão.

 

Vide

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams141.htm#REFRN10119

 

Para tornar a sua pesquisa mais rápida você precisa utilizar um índice da(s) tabela(s) lida(s), se o local onde trabalha tem uma DBA peça ajuda a ele

para ver que índices poderiam ser usados e/ou criados.

 

Mas lembre que o uso de uma função faz com que o índice não possa ser utilizado.