SQL & PL/SQL

Hi,

The temporal segment size is Tb.

The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the "WITH" clause. This ensures that the tables are only created one time.

So, why query 2) gives ORA-01652 error and takes so long to run?

1)

Conectado a:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> create table CF018_1_lineas as
        select distinct p.nu_comercial,
                p.nu_comercial_aso,
                p.nu_secue_psco_aso,
                p.co_client_com,
                p.fx_ini_vigencia,
                p.fx_fin_vigencia
        from te_qypsncmd p
        left outer join (
                select num_administrativo, es_telco
                from aux_telco_admin
        ) t
        on trim(p.nu_comercial) = t.num_administrativo
        where p.co_tp_nu_comer_aso = '18'
        and p.co_tp_nu_comer = '02'
        and p.co_uni_servic_aso = '00000229'
        and p.co_tipo_rela in ('SP', 'HE')
        and t.es_telco = 0 OR t.es_telco is null;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18

Tabla creada.

Transcurrido: 00:01:44.28
SQL>
SQL>
SQL> create table CF018_1_paquetes as
  select distinct trim(psnc.nu_comercial)||'|'||r.co_equipo tag, psnc.nu_comercial, psnc.nu_comercial_aso, psco.co_prse,
  2    3                psco.nu_contratabl, r.descripcion, r.co_equipo, psco.co_reg_comerc, r.des_reg_co, psco.co_cli_tarifa,
                psnc.co_client_com, psco.nu_secue_psco_padr, psnc.nu_secue_psco_aso, psnc.fx_ini_vigencia fx_ini_vig_psnc,
                psnc.fx_fin_vigencia fx_fin_vig_psnc, psco.fx_ini_vigencia fx_ini_vig_psco,
                psco.fx_fin_vigencia fx_fin_vig_psco
  from CF018_1_lineas psnc, te_qypscomd psco, vi_ref_tv_paquetes_equ r
  where psnc.fx_fin_vigencia = (
                select max(l.fx_fin_vigencia)
                from CF018_1_lineas l
                where  4   l.nu_comercial = psnc.nu_comercial
  )
  and psnc.nu_secue_psco_aso = psco.nu_secue_psco_padr
  and psco.co_prse = r.co_prse
  and psco.nu_contratabl = r.nu_contratabl
  and psco.co_reg_comerc = r.co_reg_co
  and psco.fx_ini_vigencia <= to_date('01  5  /' || to_char(sysdate, 'MM/YYYY'), 'DD/MM/YYYY');
  6    7    8    9   10   11   12   13   14   15   16   17

Tabla creada.

Transcurrido: 00:01:07.16
SQL> SQL>
SQL>
SQL> select count(*) from CF018_1_paquetes;

  COUNT(*)
----------
   5040591

Transcurrido: 00:00:00.71

2)

Conectado a:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> with lineas as (
  select /*+ materialize */ distinct p.nu_comercial,
        p.nu_comercial_aso,
        p.nu_secue_psco_aso,
        p.co_client_com,
        p.fx_ini_vigencia,
        p.fx_fin_vigencia
  from te_qypsncmd p
  left outer join (
        select num_administrativo, es_telco
        from aux_telco_admin
  ) t
  on trim(p.nu_comercial) = t.num_administrativo
  where p.co_tp_nu_comer_aso = '18'
  and p.co_tp_nu_comer = '02'
  and p.co_uni_servic_aso = '00000229'
  and p.co_tipo_rela in ('SP', 'HE')
  and t.es_telco = 0 OR t.es_telco is null
),
paquetes as (
  select /*+ materialize */ distinct trim(psnc.nu_comercial)||'|'||r.co_equipo tag, psnc.nu_comercial, psnc.nu_comercial_aso, psco.co_prse,
        psco.nu_contratabl, r.descripcion, r.co_equipo, psco.co_reg_comerc, r.des_reg_co, psco.co_cli_tarifa,
        psnc.co_client_com, psco.nu_secue_psco_padr, psnc.nu_secue_psco_aso, psnc.fx_ini_vigencia fx_ini_vig_psnc,
        psnc.fx_fin_vigencia fx_fin_vig_psnc, psco.fx_ini_vigencia fx_ini_vig_psco,
        psco.fx_fin_vigencia fx_fin_vig_psco
  from lineas psnc, te_qypscomd psco, vi_ref_tv_paquetes_equ r
  where psnc.fx_fin_vigencia = (
        select max(l.fx_fin_vigencia)
        from lineas l
        where l.nu_comercial = psnc.nu_comercial
  )
  and psnc.nu_secue_psco_aso = psco.nu_secue_psco_padr
  and psco.co_prse = r.co_prse
  and psco.nu_contratabl = r.nu_contratabl
  and psco.co_reg_comerc = r.co_reg_co
  and psco.fx_ini_vigencia <= to_date('01/' || to_char(sysdate, 'MM/YYYY'), 'DD/MM/YYYY')
)
select count(*) from paquetes;
SQL> 
* 
ERROR en linea 1: 
ORA-01652: no se ha podido ampliar el segmento temporal con 128 en el 
tablespace TEMP_PAING0

Transcurrido: 04:59:53.09

Thanks in advance.

Regards, Jose Luis.