Bulk Update

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

basiranaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
.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
sbarwoodCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
basiranaAuthor Commented:
The table is having more then 700 million records so it is a very large table.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sbarwoodCommented:
How many records on average do you think will be updated on a call to that function?
0
basiranaAuthor Commented:
nearly 100000 records.. I am not user how undo tablespace effects.
0
sbarwoodCommented:
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
basiranaAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.