Home » SQL & PL/SQL » SQL & PL/SQL » fastest way to update value
fastest way to update value [message #425013] Wed, 07 October 2009 03:04 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi all guru's,

Kindly need your help here on any ways to do updating data without taking too much time...currently i have a proc which will update table Q with data in table P base on another data in table P are same with table Q...here are the code :

create or replace
PROCEDURE A (tbl_name varchar2 default ' ',
l_name varchar2 default ' ',
employee_id varchar2 default ' ') IS

v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_empid varchar2(30) := employee_id;

BEGIN





if (v_tbl = ' ') then

dbms_output.put_line('Must put table name');
else

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tbl INTO v_row;



if (v_lname <> ' ') and (v_empid <> ' ') then
for x in 1.. v_row loop
execute immediate '
update '||v_tbl||' set
'||v_lname||'=(select last_name from P
where emplid = (select '||v_empid||' from
(select row_number() over (order by '||v_empid||') as n, '||v_empid||'
from '||v_tbl||')
where n= '||x||'))

where '||v_empid||' =(select '||v_empid||' from
(select row_number() over (order by '||v_empid||') as n, '||v_empid||'
from '||v_tbl||')
where n= '||x||')' ;

end loop;
end if;

end if;
end;

It will take value for table name, name column and employee id column. Then it will update the data in name column by selecting data from staging table P which the employee id are same with id in employee id column. But currently this proc works but its taking a bit of time...could anyone suggest me the best way to update this kind of process in very fast time?

Thanks for help, really appreciate that
Re: fastest way to update value [message #425016 is a reply to message #425013] Wed, 07 October 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have been several times asked to read and follow OraFAQ Forum Guide.
Read in particular "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Quote:
Thanks for help, really appreciate that

Thanks for helping us help you, "really appreciate that".

Regards
Michel

[Updated on: Wed, 07 October 2009 03:25]

Report message to a moderator

Re: fastest way to update value [message #425035 is a reply to message #425016] Wed, 07 October 2009 04:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This code is a bit of a mess.
- You are updating row-by-row. Set-based updates are better.
- Not only are you updating row-by-row, you are selecting row-by-row with separate SQL statements. Ever heard of a CURSOR?
- You are using dynamic SQL without bind variables. This will fill up and blow out your SGA.

Try to rewrite it so that there is no loop and just one MERGE statement. If you insist on making it dynamic you probably still can.

Ross Leishman
Re: fastest way to update value [message #425038 is a reply to message #425013] Wed, 07 October 2009 04:32 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Thanks for replying, previously i've already did using cursors but it show this error

ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "BENAEADM.SCRAMBLE_QUERY3", line 161
ORA-00904: "BROCKBERG": invalid identifier
ORA-06512: at line 1

I thought maybe because of value size...but check again i've already assign the length with same size

Is there any suggestion on this?
Re: fastest way to update value [message #425039 is a reply to message #425038] Wed, 07 October 2009 04:42 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The SQL that is generated is most likely wrong.

Dynamic SQL is usually

- Slow.
- Messy.
- Pretty much impossible to debug for a beginner.

So the first suggestion would be NOT to use dynamic SQL.
Re: fastest way to update value [message #425040 is a reply to message #425038] Wed, 07 October 2009 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01401: you give a value to large to be inserted in the field.

Regards
Michel
Re: fastest way to update value [message #425102 is a reply to message #425013] Wed, 07 October 2009 09:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
carillpower wrote on Wed, 07 October 2009 10:04
create or replace
PROCEDURE A (tbl_name varchar2 default ' ',
l_name varchar2 default ' ',
employee_id varchar2 default ' ') IS

v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_empid varchar2(30) := employee_id;

BEGIN
if (v_tbl = ' ') then
dbms_output.put_line('Must put table name');
else

If you don't allow null values for tbl_name, why give it a default value?

Furthermore: write in full the update you would do if the tablename and columnnames were not dynamic. What is it you are trying to do here?
Re: fastest way to update value [message #425160 is a reply to message #425013] Wed, 07 October 2009 20:42 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi, thanks for answering...here are the update code. I use cursor into the system


Quote:

create or replace
PROCEDURE A (tbl_name varchar2,
l_name varchar2,
employee_id varchar2) IS

v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_empid varchar2(30) := employee_id;

CURSOR c1 IS
SELECT emplid, last_name
FROM Q
where emplid is not null;

rec c1%rowtype;

BEGIN

if (v_tbl = ' ') then

dbms_output.put_line('Must put table name');
else

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tbl INTO v_row;


if (v_lname <> ' ') and (v_empid <> ' ') then
for rec in c1 loop
execute immediate '
update '||v_tbl||' set
'||v_lname||' = upper('||rec.last_name||')
where '||v_empid||' = '||rec.emplid||'
';
end loop;
end if;

end if;
end;


But still i got the same error

ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "BENAEADM.A", line 161
ORA-00904: "JORDAN": invalid identifier
ORA-06512: at line 1


Is there anything that i missing? Coz it works fine when i compile. Hope to got any suggestion from u guys...thanks
Re: fastest way to update value [message #425161 is a reply to message #425013] Wed, 07 October 2009 20:44 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Sorry, here are the update one

Quote:

create or replace
PROCEDURE A (tbl_name varchar2,
l_name varchar2,
employee_id varchar2) IS

v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_empid varchar2(30) := employee_id;
v_row integer;

CURSOR c1 IS
SELECT emplid, last_name
FROM Q
where emplid is not null;

rec c1%rowtype;

BEGIN

if (v_tbl = ' ') then

dbms_output.put_line('Must put table name');
else

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tbl INTO v_row;


if (v_lname <> ' ') and (v_empid <> ' ') then
for rec in c1 loop
execute immediate '
update '||v_tbl||' set
'||v_lname||' = upper('||rec.last_name||')
where '||v_empid||' = '||rec.emplid||'
';
end loop;
end if;

end if;
end;

Re: fastest way to update value [message #425180 is a reply to message #425161] Wed, 07 October 2009 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted, read and follow forum guide.

Regards
Michel
Re: fastest way to update value [message #425198 is a reply to message #425180] Thu, 08 October 2009 00:39 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi guys, i've already got the solution.. im using update with joining two table's...without using cursors or looping...it works!

The previous update data that took 2mins now already decrease to 2second...thank all for giving your thought...it helps a lot Smile
Re: fastest way to update value [message #425204 is a reply to message #425198] Thu, 08 October 2009 00:47 Go to previous message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for following the guidelines next time.

Regards
Michel
Previous Topic: Join issue
Next Topic: how can i return the resultset directly to the calling code
Goto Forum:
  


Current Time: Fri Sep 30 05:29:29 CDT 2016

Total time taken to generate the page: 0.09916 seconds