Link to home
Start Free TrialLog in
Avatar of hydev
hydevFlag for United Kingdom of Great Britain and Northern Ireland

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_MONITOR_1 on tbl_tmp_SALES_STOCK_MONITOR(loc_num, sty_barcode);
create index tbl_tmp_SALES_STOCK_MONITOR_4 on tbl_tmp_SALES_STOCK_MONITOR(loc_num, season, sty_num, sty_qual, bf_mat_char_val, wsty_size, lsty_size);
create index tbl_rep_SALES_STOCK_MONITOR_2 on tbl_rep_SALES_STOCK_MONITOR(loc_num, sty_barcode);
create index tbl_rep_SALES_STOCK_MONITOR_3 on tbl_rep_SALES_STOCK_MONITOR(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
// 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
);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of gatorvip
gatorvip
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hydev

ASKER

Thanks SO much both solutions above work amazingly fast <5 seconds.  Thanks a lot!!!
Avatar of hydev

ASKER

Thanks a lot!
Glad to be of help :)