Reallybig wrote:
> 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!
- Stop top posting.
- What does any of your code have to do with an update?
Why a record?
Why a cursor?
Just use a simple, explicit, UPDATE statement. PL/SQL is not required.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Thu Apr 28 2005 - 11:27:56 CDT
