Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Bulk Update

Posted on 2009-07-14
7
Medium Priority
?
462 Views
Last Modified: 2013-12-19
Hi

We have a table with following structure.
The table don't have any primary key or unique key.
We want to use Bulk Collect to improve performence.
How can we rewrite the procedure using Bulk collect?

Thanks
Table structure FD_LOG_DATA
==========================
 
ID_BC	                NUMBER
STR_CODE	        NUMBER
ASS_ORDER	        NUMBER
EFFECTIVE_DATE	DATE
EXPIRATION_DATE	DATE
 
PROC
=====
 
create or replace procedure fix_y2k is
  v_max_dt date;
begin
  select max(expiration_date)
    into v_max_dt
    from FD_LOG_DATA;
  for rec in (select expiration_date from FD_LOG_DATA)
    loop
    if add_months(rec.expiration_date,1200) <= v_max_date then
      update FD_LOG_DATA 
         set expiration_date = add_months(rec.expiration_date,1200);
    end if;
  end loop;
end;
/

Open in new window

0
Comment
Question by:basirana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 24850268
.hmmm. what about a plain update:
create or replace procedure fix_y2k is
  v_max_dt date;
begin
  select max(expiration_date)
    into v_max_dt
    from FD_LOG_DATA; 
  UPDATE  FD_LOG_DATA 
    SET expiration_date = add_months(rec.expiration_date,1200)
  WHERE rec.expiration_date<= add_months(v_max_date,-1200)
  ;
  commit;
end;
/

Open in new window

0
 

Accepted Solution

by:
sbarwood earned 1600 total points
ID: 24851722
basirana, What data volumes are you looking to update? Angellll's solution is fine for small volumes but if the updates are going to hit 10000's rows then you would need a bulk update. Something like this:


CREATE OR REPLACE PROCEDURE fix_y2k IS
  
  TYPE t_rowid IS TABLE OF ROWID INDEX BY PLS_INTEGER;  
  TYPE r_rec IS RECORD ( row_id t_rowid );
  
  rec  r_rec;
  v_max_dt DATE;  
  
BEGIN
    
	SELECT MAX(expiration_date)
    INTO v_max_dt
    FROM FD_LOG_DATA;
	
	LOOP 
	
	  SELECT ROWID
	  BULK COLLECT INTO rec
	  FROM FD_LOG_DATA	  
	  WHERE ADD_MONTHS(expiration_date,1200) <= v_max_dt
	  AND   ROWNUM < 10001;
	  
	  EXIT WHEN rec.row_id.COUNT = 0;
	  
	  FORALL i IN 1..rec.row_id.COUNT
	    UPDATE FD_LOG_DATA 
		SET expiration_date = ADD_MONTHS(expiration_date,1200)
		WHERE ROWID = rec.row_id(i);
	  
	  COMMIT;
	
  END LOOP;
END;
/

Open in new window

0
 

Author Comment

by:basirana
ID: 24852845
The table is having more then 700 million records so it is a very large table.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Expert Comment

by:sbarwood
ID: 24857326
How many records on average do you think will be updated on a call to that function?
0
 

Author Comment

by:basirana
ID: 24872236
nearly 100000 records.. I am not user how undo tablespace effects.
0
 

Expert Comment

by:sbarwood
ID: 24901809
Have you tried the bulk collect method I suggested and if so did it make any difference to performance? Could be worth trying it with different row counts to see the optimal commit count. i.e. 25000 commit count could be a lot faster than 10000 due to there being 6 less calls to the database but you need to make sure that memory allocation etc is ok. To be honest though, with the volumnes you are talking about (i.e. 100000) memory usage shouldn't be an issue.
0
 

Author Closing Comment

by:basirana
ID: 31603294
Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question