Re: update statement PL/SQL

From: Reallybig <bixbig_at_gmail.com>
Date: 27 Apr 2005 22:52:25 -0700
Message-ID: <1114667545.483649.305930@l41g2000cwc.googlegroups.com>

Like I mentioned that i'm a newbie.

I'm using oracle 10g Enterprise.
My OS is win 2003 server with 2.8 ghz and 512 of ram. Index are on empid field for both tables. The actual code would be sth like

DECLARE TYPE emp_record_type IS record(

	empno 	Emp.empno%type,
	sales	Emp.amount%type);

emp_record emp_record_type;

CURSOR c1 IS
SELECT emp.empno, emp.sale from sales;

BEGIN

	OPEN c1;
	FETCH c1 INTO emp_record;

while c1%FOUND
loop

	update employee
	set total_sales = emp_record.sales
	where empid = emp_record.empno;
	FETCH c1 INTO emp_record;

END LOOP;
CLOSE c1;
END;
/

Thanks in advance.

IANAL_VISTA wrote:
> "Reallybig" <bixbig_at_gmail.com> wrote in
> news:1114649010.662975.82850_at_f14g2000cwb.googlegroups.com:
>
> > I'm a newbie here. I try to update data from 1 table from another
> > table. So I used cursor to update my employee table on Total_sale
> > column = amount column on Sales Table where employee.empid =
> > sales.empid. The problem is for 700,000, it took 2 hours to
update.
> > That's too long. Any Ideas or some sample codes would really
helpful.
> >
> Sorry my crystal ball is in the shop for polishing.
>
> You don't specify OS or version.
> You don't state the hardware paltform.
> You don't mention the version of Oracle ( to 4 places, please).
> You don't post the actual code.
> You don't post the EXPLAIN PLAN.
> You don't mention how many triggers or indexes are involved.
>
> Yet you expect some automagical solution.
>
> You're On Your Own (YOYO)!
>
>
> P.S.
> Where ever possible use only SQL & avoid PL/SQL. It's faster!
Received on Thu Apr 28 2005 - 00:52:25 CDT