Re: Optimizing Query (Faster insert)

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Thu, 7 Aug 2008 23:56:54 -0700 (PDT)
Message-ID: <f6ca635b-506e-432d-8612-217d09107988@t54g2000hsg.googlegroups.com>


On 7 ago, 11:37, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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. :)

That's good enough! ;-)

Or 'ochim jorosho' if you prefer... (no automatic translation BTW) ;-)

poka.

Carlos. Received on Fri Aug 08 2008 - 01:56:54 CDT

Original text of this message