Re: Optimizing Query (Faster insert)
Date: Thu, 7 Aug 2008 02:37:13 -0700 (PDT)
Message-ID: <5557304c-1046-4d42-99e1-18112c0d8ef0@m73g2000hsh.googlegroups.com>
On Aug 6, 7:06 pm, EliasFigueroa <Eliasf..._at_gmail.com> wrote:
> 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
¿Por qué no simplemente INSERT /*+ APPEND */ INTO ... SELECT ...? Esto parece ser directo y ejecutará definitivamente lo más eficientemente posible. Ninguna necesidad de PL/SQL aquí…
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
p.s. Sorry if my Spanish is not syntactically or semantically correct - I don't speak it and just used a translation service on this:
Why not just INSERT /*+ APPEND */ ... SELECT ...? This seems to be straightforward and will definitely execute the most efficiently. No need for PL/SQL here...
Hope the translation is comprehensible. :) Received on Thu Aug 07 2008 - 04:37:13 CDT