hydev
asked on
Oracle UPDATE command taking long, long time to execute.
Hi,
I have a stored procedure in Oracle that is baffling me. I have one table, let say called MASTER and a second table called SOURCE. As there is a lot of data being processed I run a complex query on the database and put the results in SOURCE. I then run an UPDATE command to populate the MASTER table with the SOURCE's data. Sounds simple but that update is taking a very long time to execute and I don't know why. If I SELECT the MASTER table there is about 28,000 rows and in the SOURCE table there is about 1500 rows. I have timed the first INSERT command and it's not very long 2 minutes max. However the UPDATE is still going after 15 minutes!
I have indexes on both tables defined as:
create index tbl_tmp_SALES_STOCK_MONITO R_1 on tbl_tmp_SALES_STOCK_MONITO R(loc_num, sty_barcode);
create index tbl_tmp_SALES_STOCK_MONITO R_4 on tbl_tmp_SALES_STOCK_MONITO R(loc_num, season, sty_num, sty_qual, bf_mat_char_val, wsty_size, lsty_size);
create index tbl_rep_SALES_STOCK_MONITO R_2 on tbl_rep_SALES_STOCK_MONITO R(loc_num, sty_barcode);
create index tbl_rep_SALES_STOCK_MONITO R_3 on tbl_rep_SALES_STOCK_MONITO R(loc_num, season, sty_num, sty_qual, bf_mat_char_val, wsty_size, lsty_size);
Can anyone help as I am very baffled. Any help greatly appreciated.
Mike
I have a stored procedure in Oracle that is baffling me. I have one table, let say called MASTER and a second table called SOURCE. As there is a lot of data being processed I run a complex query on the database and put the results in SOURCE. I then run an UPDATE command to populate the MASTER table with the SOURCE's data. Sounds simple but that update is taking a very long time to execute and I don't know why. If I SELECT the MASTER table there is about 28,000 rows and in the SOURCE table there is about 1500 rows. I have timed the first INSERT command and it's not very long 2 minutes max. However the UPDATE is still going after 15 minutes!
I have indexes on both tables defined as:
create index tbl_tmp_SALES_STOCK_MONITO
create index tbl_tmp_SALES_STOCK_MONITO
create index tbl_rep_SALES_STOCK_MONITO
create index tbl_rep_SALES_STOCK_MONITO
Can anyone help as I am very baffled. Any help greatly appreciated.
Mike
// POPULATE THE SOURCE TABLE
insert into tbl_tmp_SALES_STOCK_MONITOR(loc_num, season, sty_num, sty_qual, bf_mat_char_val, wsty_size,
lsty_size, sty_barcode, qty, sales_net, sales_value)
select a.shop_num, b.season, b.sty_num, b.sty_qual, b.bf_mat_char_val, wsty_size, lsty_size, sty_barcode,
nvl(sum(decode(a.pos_type, 'C', -1, 1) * b.sty_qty), 0),
nvl(sum(decode(a.pos_type, 'C', -1, 1) * (b.pre_vat_lamount)), 0),
nvl(sum(decode(a.pos_type, 'C', -1, 1) * (b.pre_vat_lamount + b.vat_lamount)), 0)
from pro.pos_grp_defs a, pro.pos_grp_items b, pro.loc_defs c
where a.shop_num = b.shop_num
and a.pos_num = b.pos_num
and c.loc_num = a.shop_num
and c.ploc_type in ('RSC','RSO')
and a.rollup_run = 'Y'
and a.ftrans_run != 'V'
and a.pos_type in ('S','C')
and a.in_date between var_in_end_date-7 and var_in_end_date
and c.loc_sort like var_in_loc_sort
group by a.shop_num, b.season, b.sty_num, b.sty_qual, bf_mat_char_val, wsty_size, lsty_size, sty_barcode;
// NOW UPDATE THE MASTER TABLE
update tbl_Rep_SALES_STOCK_MONITOR x
set (sales_LW_qty, sales_LW_net, sales_LW_value) = (
select qty, sales_net, sales_value
from tbl_tmp_SALES_STOCK_MONITOR a
where a.sty_barcode = x.sty_barcode
and a.loc_num = x.loc_num
);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot!
Glad to be of help :)
ASKER