process takes a long- BULK COLLECT

From: EliasFigueroa <Eliasfimo_at_gmail.com>
Date: Wed, 13 Aug 2008 10:17:49 -0700 (PDT)
Message-ID: <eb2e4a12-22d1-44b1-b199-c8e988451344@s50g2000hsb.googlegroups.com>

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; Received on Wed Aug 13 2008 - 12:17:49 CDT