Re: process takes a long- BULK COLLECT
Date: Thu, 14 Aug 2008 06:15:42 -0700 (PDT)
Message-ID: <c5d9c841-a031-4cdc-be73-698a9b0552af@e39g2000hsf.googlegroups.com>
On Aug 13, 9:17 pm, EliasFigueroa <Eliasf..._at_gmail.com> wrote:
> The loading process is very slow as optimize
> are 500000 records and takes 5 hours to insert in another table.
>
> and probe with insert / APPEND * * + / <table> into select ... but
> freezes any suggestions ..
> Thank you for your assistance
>
> CREATE OR REPLACE PROCEDURE SPGT147_CARGARPTRESUMENES (
> p_fecha IN VARCHAR2,
> p_array_size IN PLS_INTEGER DEFAULT 100
> )
> AS
> TYPE ARRAY IS TABLE OF VARCHAR2 (3000)
> INDEX BY BINARY_INTEGER;
>
> v_cliente ARRAY;
> v_segmento ARRAY;
> v_servicio ARRAY;
> v_tipotrafico ARRAY;
> v_operador ARRAY;
> v_zonal ARRAY;
> v_codzonal ARRAY;
> v_horario ARRAY;
> v_denominacion ARRAY;
> v_fecha ARRAY;
> v_negocio ARRAY;
> v_duracion ARRAY;
> v_agencia ARRAY;
> v_indicador ARRAY;
> v_hora ARRAY;
> v_segundos ARRAY;
> v_minutos ARRAY;
> v_soles ARRAY;
> v_refid ARRAY;
> v_periodo ARRAY;
>
> /*----------------------------------------------------------*/
>
> --spgt147_drop_index;
>
> /*---------------------------------------------------------*/
> CURSOR c_resumen
> IS
> SELECT /*+index(TAB1 INDEX_TREE_TRAFICO)*/
> sp.des_stip_pc, --
> cliente
> sg.nom_seg_cta, -- segmento
> f_obtienetiposervicio (tab1.tipoorigen,
> tab1.tipodestino,
> tab1.callclas,
> tab1.bparty
> ), --
> servicio
> f_obtienetipotrafico (tab1.tipollamada, tab1.tipodestino,
> 1), -- trafico
> f_obteneroperador (tab1.aparty), --
> operador
> zn.descripcion, --zonal,
> tab1.codzon, --codzonal,
> f_bandahoraria (tab1.stardate, tab1.tipollamada),
> --banda
> TAB1.Denominacion,--Denominacion
> tab1.stardate,
> --Fecha
> tab1.rao, --Negocio
> tab1.duracion, --Duracion
> tab1.expiredate, --Agencia
> tab1.tipollamada, --Indirural
>
> tab1.hora, --Hora
> tab1.minutos, --minutos
> tab1.segundos, --Segundos
> tab1.soles , --soles
> tab1.refid,
> tab1.periodo
>
> FROM ( SELECT rg.cod_stip_pc AS subtipo,
> rg.cod_seg_cta AS segcta,
> rg.cod_tipopc AS tipopc,
> rg.cod_nat_cta AS codnat,
> rg.tipotelefonoorigen AS tipoorigen,
> rg.tipotelefonodestino AS tipodestino,
> rg.callclass AS callclas,
> rg.bparty AS bparty,
> rg.aparty AS aparty,
> rg.tipollamada AS tipollamada,
> rg.codigozonal AS codzon,
> rg.startdatetime AS stardate,
> rg.rao AS rao,
> rg.DURATION AS duracion,
> rg.expiredate AS expiredate,
> SUBSTR (rg.DURATION, 1, 2) AS hora,
> SUBSTR (rg.DURATION, 3, 2) AS minutos,
> SUBSTR (rg.DURATION, 5, 2) AS segundos,
> TO_CHAR (rg.callcharge, '00.99') AS soles ,
> rg.refid AS refid,
> rg.periodo AS periodo,
> St1.Valorfacial AS denominacion
> FROM reg_tmptrafico rg,
> Seriestarjeta St1
> WHERE (rg.periodo >= p_fecha || '00' AND rg.periodo <=
> p_fecha || '23')
> AND St1.Numeroinicio <= rg.Refid AND St1.Numerofin>= rg.Refid) tab1,
>
> subtipopc sp,
> segmento sg,
> zonal zn
> WHERE tab1.tipopc = sp.cod_tipopc
> AND tab1.subtipo = sp.cod_stip_pc
> AND tab1.segcta = sg.cod_seg_cta
> AND tab1.codnat = sg.cod_nat_cta
> AND tab1.codzon = zn.cod_zonal_atis;
> BEGIN
> OPEN c_resumen;
>
> LOOP
> FETCH c_resumen
> BULK COLLECT INTO v_cliente, v_segmento, v_servicio,
> v_tipotrafico,
> v_operador, v_zonal, v_codzonal,
> v_horario,v_denominacion,v_fecha, v_negocio,
> v_duracion, v_agencia, v_indicador, v_hora, v_segundos,
> v_minutos, v_soles,v_refid,v_periodo LIMIT p_array_size;
>
> FORALL i IN 1 .. v_cliente.COUNT
> INSERT INTO reportesresumen
> (cliente, segmento, servicio,
> tipotrafico, operador, zonal,
> codzonal, horario,DENOMINACION, fecha,
> negocio, DURATION, agencia,
> indicadorrural, hora, segundos,
> minutos, soles,refid,periodo
> )
> VALUES (v_cliente(i), v_segmento(i), v_servicio(i),
> v_tipotrafico(i), v_operador(i), v_zonal(i),
> v_codzonal(i),
> v_horario(i),v_denominacion(i),v_fecha(i),
> v_negocio(i), v_duracion(i), v_agencia(i),
> v_indicador(i), v_hora(i), v_segundos(i),
> v_minutos(i), v_soles(i),
> v_refid(i),v_periodo(i)
> );
> EXIT WHEN c_resumen%NOTFOUND;
> END LOOP;
> COMMIT ;
> CLOSE c_resumen;
> END SPGT147_CARGARPTRESUMENES;
- Please define "freezes". Does it hang, or does it just take too long to complete?
- Do you have indexes on the target table? A lot of them maybe? If you do, extract their DDL and drop them before you start copying, and recreate them when you're done.
- My suggestion about using INSERT AS SELECT still stands. You won't beat its performance with PL/SQL.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Thu Aug 14 2008 - 08:15:42 CDT