Re: Perfomance
Thanks for your help.
But I think don't optimize correct because not access with index created
The query is:
create temp table regTL
(serial integer,reg char(211));
create temp table TL
(serial integer,
ruc char(13) NOT NULL,
periodo char(3) NOT NULL,
secuencia char(2) NOT NULL,
no_documento char(13) NOT NULL,
tipo_cbte char(2) NOT NULL,
fecha_e char(8) NOT NULL,
fecha_r char(8) NOT NULL,
serie_cbte char(6),
secuencia_cbte char(7),
autoriza_cbte char(10),
identificacion char(2) NOT NULL,
base_imponibleg char(12) NOT NULL,
codigo_por_iva char(1) NOT NULL,
base_imponible_0 char(12) NOT NULL,
monto_iva char(12) NOT NULL,
monto_ice char(12) NOT NULL,
monto_iva1 char(12),
codigo_ret_iva char(1),
monto_ret_iva char(12),
monto_iva2 char(12),
codigo_ret_iva2 char(1),
monto_ret_iva2 char(12),
tran_dev char(1) NOT NULL,
cant_cbte CHAR(12),
cant_nc CHAR(12),cant_nd CHAR(12));
create index idx_fr3 on regtl(serial);
create index idx_fr4 on tl(serial);
update statistics for table regtl; -- add but don't work
update statistics for table tl;
load from TL052003.csv
insert into tl;
insert into regtl
select serial,""
from tl;
update regtl
set reg = NULL
where serial>0;
update regtl
set reg[211] = "-"
where serial>0;
set explain on;update regtl
set reg[1,13] =
(select ruc
from tl
where tl.serial = regtl.serial), -- here access is sequential
-- don't access with index
reg[14,16] =
(select periodo
from tl
where tl.serial = regtl.serial),
reg[17,18] =
(select secuencia
from tl
where tl.serial = regtl.serial),
reg[19,31] =
(select no_documento
from tl
where tl.serial = regtl.serial),
reg[32,33] =
(select tipo_cbte
from tl
where tl.serial = regtl.serial),
reg[34,41] =
(select fecha_e
from tl
where tl.serial = regtl.serial),
reg[42,49] =
(select fecha_r
from tl
where tl.serial = regtl.serial),
reg[50,55] =
(select serie_cbte
from tl
where tl.serial = regtl.serial),
reg[56,62] =
(select secuencia_cbte
from tl
where tl.serial = regtl.serial),
reg[63,72] =
(select autoriza_cbte
from tl
where tl.serial = regtl.serial),
reg[73,74] =
(select identificacion
from tl
where tl.serial = regtl.serial),
reg[75,86] =
(select base_imponibleg
from tl
where tl.serial = regtl.serial),
reg[87,87] =
(select codigo_por_iva
from tl
where tl.serial = regtl.serial),
reg[88,99] =
(select base_imponible_0
from tl
where tl.serial = regtl.serial),
reg[100,111] =
(select monto_iva
from tl
where tl.serial = regtl.serial),
reg[112,123] =
(select monto_ice
from tl
where tl.serial = regtl.serial),
reg[124,135] =
(select monto_iva1
from tl
where tl.serial = regtl.serial),
reg[136,136] =
(select codigo_ret_iva
from tl
where tl.serial = regtl.serial),
reg[137,148] =
(select monto_ret_iva
from tl
where tl.serial = regtl.serial),
reg[149,160] =
(select monto_iva2
from tl
where tl.serial = regtl.serial),
reg[161,161] =
(select codigo_ret_iva2
from tl
where tl.serial = regtl.serial),
reg[162,173] =
(select monto_ret_iva2
from tl
where tl.serial = regtl.serial),
reg[174,174] =
(select tran_dev
from tl
where tl.serial = regtl.serial),
reg[175,186] =
(select cant_cbte
from tl
where tl.serial = regtl.serial),
reg[187,198] =
(select cant_nc
from tl
where tl.serial = regtl.serial),
reg[199,210] =
(select cant_nd
from tl
where tl.serial = regtl.serial)
where serial IN (select serial from tl);
unload to TL052003.ane
select reg from regtl;
Regards
Rubén
Comments below --EEM
> -----Original Message-----
> From: Ruben Dutan [mailto:rdutan@aplext.com]
> Sent: Tuesday, July 13, 2004 1:07 PM
> To: ids@iiug.org
> Subject: Re: Perfomance [3236]
>
> Thanks for your help.
> But I think don't optimize correct because not access with index created
> The query is:
> create temp table regTL
> (serial integer,> reg char(211));
>
> create temp table TL
> (serial integer,
> ruc char(13) NOT NULL,
> periodo char(3) NOT NULL,
> secuencia char(2) NOT NULL,
> no_documento char(13) NOT NULL,
> tipo_cbte char(2) NOT NULL,
> fecha_e char(8) NOT NULL,
> fecha_r char(8) NOT NULL,
> serie_cbte char(6),
> secuencia_cbte char(7),
> autoriza_cbte char(10),
> identificacion char(2) NOT NULL,
> base_imponibleg char(12) NOT NULL,
> codigo_por_iva char(1) NOT NULL,
> base_imponible_0 char(12) NOT NULL,
> monto_iva char(12) NOT NULL,
> monto_ice char(12) NOT NULL,
> monto_iva1 char(12),
> codigo_ret_iva char(1),
> monto_ret_iva char(12),
> monto_iva2 char(12),
> codigo_ret_iva2 char(1),
> monto_ret_iva2 char(12),
> tran_dev char(1) NOT NULL,
> cant_cbte CHAR(12),
> cant_nc CHAR(12),> cant_nd CHAR(12));
>
> create index idx_fr3 on regtl(serial);
> create index idx_fr4 on tl(serial);
> update statistics for table regtl; -- add but don't work
> update statistics for table tl;>
These update statistics statements will not do anything useful until data is
loaded into the temp tables.
> load from TL052003.csv
> insert into tl;>
> insert into regtl
> select serial,""
> from tl;>
Try them here, instead:
update statistics for table regtl;
update statistics for table tl;
> update regtl
> set reg = NULL
> where serial>0;
>
> update regtl
> set reg[211] = "-"
> where serial>0;
>
> set explain on;> update regtl
> set reg[1,13] =
> (select ruc
> from tl
> where tl.serial = regtl.serial), -- here access is sequential
> -- don't access with index
> reg[14,16] =
> (select periodo
> from tl
> where tl.serial = regtl.serial),
> reg[17,18] =
> (select secuencia
> from tl
> where tl.serial = regtl.serial),
> reg[19,31] =
> (select no_documento
> from tl
> where tl.serial = regtl.serial),
> reg[32,33] =
> (select tipo_cbte
> from tl
> where tl.serial = regtl.serial),
> reg[34,41] =
> (select fecha_e
> from tl
> where tl.serial = regtl.serial),
> reg[42,49] =
> (select fecha_r
> from tl
> where tl.serial = regtl.serial),
> reg[50,55] =
> (select serie_cbte
> from tl
> where tl.serial = regtl.serial),
> reg[56,62] =
> (select secuencia_cbte
> from tl
> where tl.serial = regtl.serial),
> reg[63,72] =
> (select autoriza_cbte
> from tl
> where tl.serial = regtl.serial),
> reg[73,74] =
> (select identificacion
> from tl
> where tl.serial = regtl.serial),
> reg[75,86] =
> (select base_imponibleg
> from tl
> where tl.serial = regtl.serial),
> reg[87,87] =
> (select codigo_por_iva
> from tl
> where tl.serial = regtl.serial),
> reg[88,99] =
> (select base_imponible_0
> from tl
> where tl.serial = regtl.serial),
> reg[100,111] =
> (select monto_iva
> from tl
> where tl.serial = regtl.serial),
> reg[112,123] =
> (select monto_ice
> from tl
> where tl.serial = regtl.serial),
> reg[124,135] =
> (select monto_iva1
> from tl
> where tl.serial = regtl.serial),
> reg[136,136] =
> (select codigo_ret_iva
> from tl
> where tl.serial = regtl.serial),
> reg[137,148] =
> (select monto_ret_iva
> from tl
> where tl.serial = regtl.serial),
> reg[149,160] =
> (select monto_iva2
> from tl
> where tl.serial = regtl.serial),
> reg[161,161] =
> (select codigo_ret_iva2
> from tl
> where tl.serial = regtl.serial),
> reg[162,173] =
> (select monto_ret_iva2
> from tl
> where tl.serial = regtl.serial),
> reg[174,174] =
> (select tran_dev
> from tl
> where tl.serial = regtl.serial),
> reg[175,186] =
> (select cant_cbte
> from tl
> where tl.serial = regtl.serial),
> reg[187,198] =
> (select cant_nc
> from tl
> where tl.serial = regtl.serial),
> reg[199,210] =
> (select cant_nd
> from tl
> where tl.serial = regtl.serial)
> where serial IN (select serial from tl);
>
> unload to TL052003.ane
> select reg from regtl;>
> Regards
> Rubén
>