Creation of slow materialized view

0

Good evening.

I'm creating a materialized view based on a JOIN with 3 tables, but it's taking too long ( 200+ minutes on the machine ).

I use the appropriate indexed keys in the WHERE of each table and have already given REBUID in the indexes of the largest table, doing search with filter for the partition that I want to go faster (the table is partitioned), but even so still delay. Would you have any more suggestions for trying to get a "turbo" on this create?

The largest table is about $ 20 million.

CREATE MATERIALIZED VIEW INVE_ANALIT
REFRESH FORCE ON DEMAND
AS
SELECT
 A.CPF
 B.SEGMENTO
 ....MUITOS_CAMPOS_!_
FROM
  TABELA_01  A,
  TABELA_02  B,
  TABELA_03  C
WHERE
 a.nr_cpf_cnpj = b.cli_cpf(+)
 and a.nr_cpf_cnpj = c.cpf(+)
 and to_char(add_months(a.dt_producao, -1),'RRRRMM') =  c.anomes(+)
 and to_char(add_months(a.dt_producao, -1),'RRRRMM') =  b.anomes(+)
 and (a.dt_producao <> '12/08/2017' and a.nr_vlr <> 9000999.9)
 and a.ds_grupo_produto = 'INVESTIMENTOS'
 and a.ds_produto not in ('CDC','CONTA MAX')
 and a.nr_vlr > 0
 and a.ds_segm_prim  not in ('EMPRESAS 1', 'EMPRESAS 1 REMOTO',
   'EMPRESAS', 'EMPRESAS 3',
   'EMPRESAS 2', 'PRIVATE PJ', 'GOV', 'GOVERNOS', 'CORPORATE',
   'UNIVERSIDADES', 'UNIV')
 and a.dt_producao > trunc(last_day(add_months(sysdate , -14)));
    
asked by anonymous 29.06.2018 / 23:49

0 answers