Optimizing Query (Faster insert)

From: EliasFigueroa <Eliasfimo_at_gmail.com>
Date: Wed, 6 Aug 2008 08:06:59 -0700 (PDT)
Message-ID: <09328cfb-c994-4b0a-ad92-391efa65081c@25g2000hsx.googlegroups.com>

Tengo Problemas para insertar datos...

1.-CREATE OR REPLACE PROCEDURE SPGT147_CARGA_REGTMPRESUMEN    ( p_fecha IN VARCHAR2)

• param2 IN OUT datatype) IS

  --VARIABLES PARA EL CURSOR
TYPE codigoregistroactivity IS TABLE OF Reg_tmptrafico.Codigoregistroactivity%type;

TYPE periodo                  IS TABLE OF Reg_tmptrafico.Periodo


%type;

TYPE aparty                   IS TABLE OF Reg_tmptrafico.Aparty%type;
TYPE bparty                   IS TABLE OF Reg_tmptrafico.Bparty%type;
TYPE tipotelefonoorigen       IS TABLE OF
Reg_tmptrafico.Tipotelefonoorigen%type;
TYPE tipotelefonodestino      IS TABLE OF
Reg_tmptrafico.Tipotelefonodestino%type;
TYPE callclass                IS TABLE OF Reg_tmptrafico.Callclass


%type;

TYPE startdatetime            IS TABLE OF Reg_tmptrafico.Startdatetime


%type;

TYPE rao                      IS TABLE OF Reg_tmptrafico.Rao%type;
TYPE tipollamada              IS TABLE OF Reg_tmptrafico.Tipollamada


%type;

TYPE duration                 IS TABLE OF Reg_tmptrafico.Duration


%type;

TYPE callcharge               IS TABLE OF Reg_tmptrafico.Callcharge


%type;

TYPE refid                    IS TABLE OF Reg_tmptrafico.Refid%type;
TYPE cod_tipopc               IS TABLE OF Reg_tmptrafico.Cod_Tipopc


%type;

TYPE cod_stip_pc              IS TABLE OF Reg_tmptrafico.Cod_Stip_Pc


%type;

TYPE cod_nat_cta              IS TABLE OF Reg_tmptrafico.Cod_Nat_Cta


%type;

TYPE cod_seg_cta              IS TABLE OF Reg_tmptrafico.Cod_Seg_Cta


%type;

TYPE codigodepartamento       IS TABLE OF
Reg_tmptrafico.Codigodepartamento%type;
TYPE numeroinscripcion        IS TABLE OF
Reg_tmptrafico.Numeroinscripcion%type;
TYPE codigozonal              IS TABLE OF Reg_tmptrafico.Codigozonal


%type;

TYPE expiredate               IS TABLE OF Reg_tmptrafico.expiredate


%type;

TYPE tipotelefono             IS TABLE OF Reg_tmptrafico.Tipotelefono


%type;

TYPE valorfacial              IS TABLE OF Reg_tmptrafico.Valorfacial

%type;

/*---------------------------------------------------------------*/
v_codigo codigoregistroactivity;
v_periodo periodo;
v_aparty aparty;
v_bparty bparty;
v_tipotelefoori tipotelefonoorigen;
v_tipotelefodes tipotelefonodestino;
v_callclas callclass;
v_startdate startdatetime;
v_rao rao;
v_tipollamada tipollamada;
v_duration duration;
v_callcharge callcharge;
v_refid refid;
v_codtipc cod_tipopc;
v_codsubtippc  cod_stip_pc;
v_codnatcta cod_nat_cta;
v_codsegcta cod_seg_cta;
v_coddept codigodepartamento;
v_nroinsc numeroinscripcion;
v_codzonal codigozonal;
v_expire expiredate;

v_tipotelefono tipotelefono;
v_valorfacial valorfacial;
BEGIN SELECT /*+index(rg) index(st1) +*/

       rg.codigoregistroactivity,
       rg.periodo      AS periodo,
       rg.aparty       AS telefono,
       rg.bparty       AS telefonob,
       rg.Tipotelefonoorigen AS torigen,
       rg.Tipotelefonodestino AS tdestino,
       rg.Callclass AS Callclass,
       rg.startdatetime AS fecha,
       rg.rao,
       rg.tipollamada AS tipollamada,
       rg.duration     AS duracion,
       rg.callcharge   AS soles,
       rg.refid        AS refid,
       rg.cod_tipopc   AS tipopc,
       rg.cod_stip_pc  AS subtipo,
       rg.cod_nat_cta  AS codnat,
       rg.cod_seg_cta  AS segcta,
       rg.codigodepartamento AS coddept,
       rg.numeroinscripcion AS numins,
       rg.codigozonal  AS codzon,
       rg.expiredate AS expiredate,
       rg.tipotelefono as tipotelefono,
       DENOMINACIONSELNUMTARJETA_W(rg.refid) AS denominacion

BULK COLLECT INTO v_codigo ,
                  v_periodo,
                  v_aparty,
                  v_bparty,
                  v_tipotelefoori,
                  v_tipotelefodes,
                  v_callclas,
                  v_startdate,
                  v_rao,
                  v_tipollamada,
                  v_duration,
                  v_callcharge,
                  v_refid,
                  v_codtipc,
                  v_codsubtippc,
                  v_codnatcta,
                  v_codsegcta,
                  v_coddept ,
                  v_nroinsc ,
                  v_codzonal,
                  v_expire ,
                  v_tipotelefono,
                  v_valorfacial

 FROM reg_tmptrafico rg
 WHERE ( rg.periodo = p_fecha || '00' OR rg.periodo = p_fecha || '01'

      OR rg.periodo = p_fecha || '02' OR rg.periodo = p_fecha || '03'
      OR rg.periodo = p_fecha || '04' OR rg.periodo = p_fecha || '05'
      OR rg.periodo = p_fecha || '06' OR rg.periodo = p_fecha || '07'
      OR rg.periodo = p_fecha || '08' OR rg.periodo = p_fecha || '09'
      OR rg.periodo = p_fecha || '10' OR rg.periodo = p_fecha || '11'
      OR rg.periodo = p_fecha || '12' OR rg.periodo = p_fecha || '13'
      OR rg.periodo = p_fecha || '14' OR rg.periodo = p_fecha || '15'
      OR rg.periodo = p_fecha || '16' OR rg.periodo = p_fecha || '17'
      OR rg.periodo = p_fecha || '18' OR rg.periodo = p_fecha || '19'
      OR rg.periodo = p_fecha || '20' OR rg.periodo = p_fecha || '21'
      OR rg.periodo = p_fecha || '22' OR rg.periodo = p_fecha ||

'23');

 FORALL i IN v_codigo.FIRST..v_codigo.LAST   INSERT /*+ APPEND*/ INTO REG_TMPRESUMEN1

                    (codigoregistroactivity,
                    periodo,
                    aparty,
                    bparty,
                    tipotelefonoorigen,
                    tipotelefonodestino,
                    callclass,
                    startdatetime,
                    rao,
                    tipollamada,
                    duration,
                    callcharge,
                    refid,
                    cod_tipopc,
                    cod_stip_pc,
                    cod_nat_cta,
                    cod_seg_cta,
                    codigodepartamento,
                    numeroinscripcion,
                    codigozonal,
                    expiredate,
                    tipotelefono,
                    valorfacial)
         VALUES( v_codigo(i) ,
                  v_periodo(i),
                  v_aparty(i),
                  v_bparty(i),
                  v_tipotelefoori(i),
                  v_tipotelefodes(i),
                  v_callclas(i),
                  v_startdate(i),
                  v_rao(i),
                  v_tipollamada(i),
                  v_duration(i),
                  v_callcharge(i),
                  v_refid(i),
                  v_codtipc(i),
                  v_codsubtippc(i),
                  v_codnatcta(i),
                  v_codsegcta(i),
                  v_coddept(i) ,
                  v_nroinsc(i) ,
                  v_codzonal(i),
                  v_expire(i),
                  v_tipotelefono(i),
                  v_valorfacial(i)  );

COMMIT; END SPGT147_CARGA_REGTMPRESUMEN; 2.-CREATE OR REPLACE FUNCTION DENOMINACIONSELNUMTARJETA_W

     ( p_NumeroTarjeta IN Tarjetas.Numerotarjeta%Type
      -- p_FechaExpiracion IN Tarjetas.Fechaexpiracionsinuso%TYPE
     ) --RETURN SeriesTarjeta.Codigoserietarjeta%TYPE

RETURN NUMBER AS
  v_CodigoDenominacion SeriesTarjeta.Codigodenominaciontarjeta%TYPE; BEGIN     SELECT /*+index(st)+*/st.codigodenominaciontarjeta     INTO v_CodigoDenominacion
    FROM seriestarjeta st
    WHERE st.codigoserietarjeta = (SELECT/*+index(st)+*/ MAX(st.codigoserietarjeta)

                                   FROM SeriesTarjeta st
                                   WHERE st.numeroinicio <=
p_NumeroTarjeta AND
                                         st.numerofin >=

p_NumeroTarjeta );

    RETURN(v_CodigoDenominacion);
EXCEPTION
   WHEN no_data_found THEN

      v_CodigoDenominacion := NULL;
      RETURN(v_CodigoDenominacion);

END; demora mas de 1 hora

reg_tmptrafico = 400,000
seriestarjeta =28817 Received on Wed Aug 06 2008 - 10:06:59 CDT