Home » SQL & PL/SQL » SQL & PL/SQL » Update all the retrieved records at once (meged)
Update all the retrieved records at once (meged) [message #215895] Wed, 24 January 2007 04:00 Go to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
Hi,
can u please tell me which is the fastest way to fetch records from a table based on a certain condition and then update those records(a few columns) with new values.

The table has around 1 million rows and around 20 columns
Re: Fastest way to fetch and update records from a table consisting of million rows [message #215898 is a reply to message #215895] Wed, 24 January 2007 04:11 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
This is far too generalized a question to be able to give an accurate response. Where are you getting the values being used to update the table ? Are they hard-coded or looked up in another table or tables ? Are you updating all the rows in the table or just a few of them ?
Update all the retrived records at once [message #215900 is a reply to message #215895] Wed, 24 January 2007 04:26 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
Hi,
can anyone please tell me if there is a method to update all the records retrieved from a table at once. What i mean is suppose
a update statement needs to update say 2000 records in a table, instead of updating the records one by one is there a method by which all the records can be updated at one stretch. SO that it wil save a lot of time when the nimber of records to be updated are more..
Re: Update all the retrived records at once [message #215906 is a reply to message #215900] Wed, 24 January 2007 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If those 2000 records are ALL records in a table, simply issue an UPDATE statement and set new column values.

If there are more than 2000 records, UPDATE statement will have to have a WHERE clause which would identify record set to be updated.
Re: Update all the retrived records at once [message #215946 is a reply to message #215906] Wed, 24 January 2007 06:29 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
Thanks for the reply..
Well the table has more than 50,000 records. And what i exactly need is that whenever we write an update statement based on a condition (i.e by using a where clause) it retrives all the records to be updated and updates them one by one. But what i want oracle to do is to update all the retrived records in one go instead of updating the records individually..

I have tried bulk update method where i update the records by treating them as a plsql type but that method is also taking the same time as a normal update statement with where clause would take.

Re: Update all the retrived records at once [message #215947 is a reply to message #215900] Wed, 24 January 2007 06:34 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
why specifically 2000?

Re: Update all the retrived records at once [message #215950 is a reply to message #215947] Wed, 24 January 2007 06:52 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
it can be any no of records....... 2000 is just an example.
Re: Fastest way to fetch and update records from a table consisting of million rows [message #215951 is a reply to message #215895] Wed, 24 January 2007 07:07 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
what i am doing is getting a few records from a table based on a condition say i get some 3000 records which have to be updated.

oracle generally updates the rows one by one. I want to update all the rows at a time.. so that it wil reduce the total time taken. I dont have to update all the records only some records from a table. But the number of records to be updated are more may be around 3000 to 5000 records.

I have tried using bulk update method. but this takes the same time as the normal update statement would take.

so can you please tell me which is the fastest way to update
Re: Fastest way to fetch and update records from a table consisting of million rows [message #215956 is a reply to message #215895] Wed, 24 January 2007 07:20 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If the rows can be identified quickly using indexed columns, then a block update statement would probably be the best option, as in:

update table set column1='x', column2='y'
where column3 ='y' and column4='z'

However, if you find out which rows to update by looking up their IDs in another table, it might be quicker to write a cursor to select rows from the lookup table and then call a bulk update using FORALL.

Re: Fastest way to fetch and update records from a table consisting of million rows [message #215959 is a reply to message #215895] Wed, 24 January 2007 07:34 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
Thanks a lot for the reply.
This is exactly what i am doing. I too am using the bulk update method. But this method is also taking the same time as a normal update statement would take.

Is there a method to make this work faster than bulk update.
I am using cursor to fetch the rows from the source table and then using the bulk update method. but this makes no difference to the time taken to update the records.

can you please help me and suggest some other method
Re: Update all the retrived records at once [message #215960 is a reply to message #215900] Wed, 24 January 2007 07:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
zohra_banu83 wrote on Wed, 24 January 2007 11:26
Hi,
can anyone please tell me if there is a method to update all the records retrieved from a table at once.

zohra_banu83 wrote on Wed, 24 January 2007 13:29
I have tried bulk update method where i update the records by treating them as a plsql type but that method is also taking the same time as a normal update statement with where clause would take.

I do not know what are you asking as you did not used a question mark. Surely not the way how to update all records at once as you stated both ways (sql update, pl/sql forall bulk).
If you have condition values in list, use pl/sql forall, else normal update is fine. As you state both methods taking approximately same amount of time.
I see no problem here.
Re: Fastest way to fetch and update records from a table consisting of million rows [message #215962 is a reply to message #215895] Wed, 24 January 2007 07:48 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
I have tried using bulk update method. but this takes the same time as the normal update statement would take.

What does that "bulk collect" and that "normal" update look like

Would be easier if you provide us with an example of your table/indexes etc and code.

[Updated on: Wed, 24 January 2007 07:57]

Report message to a moderator

Re: Update all the retrived records at once [message #215966 is a reply to message #215900] Wed, 24 January 2007 07:57 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
The problem is that i want a method which will update the records faster than the bulk update method. Initially is used the normal update method but since this method was taking a long time to update the records i tried using the bulk update method but the problem still exists because even the bulk update method is also taking a long time infact same time as the normal update method.

I want a method which wil update the records faster than the bulk update
Re: Update all the retrived records at once [message #215971 is a reply to message #215900] Wed, 24 January 2007 08:39 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Wouldn't it have been better to continue in your other eerily similar thread?

http://www.orafaq.com/forum/m/215895/66800/#msg_215895
Re: Update all the retrived records at once [message #215978 is a reply to message #215900] Wed, 24 January 2007 09:00 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Quote:
I want a method which wil update the records faster than the bulk update


How do you update a table in Oracle ?

Use the update statement.

How can we use it:

a) all rows in one go (block update statement)
b) one row at a time (cursor method)
c) something in between (bulk updates)

So, no, I don't think there are any other options. Perhaps if you actually told us what you are trying to do rather than demanding something which doesn't exist, we might be able to help.
Re: Update all the retrived records at once [message #215989 is a reply to message #215900] Wed, 24 January 2007 09:47 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
Hi, thanks a lot.

Let me tell you clearly what exactly the scenario is.
I have a table called Jnl and another table called jsl. jnl_num is the primary key for both the tables.
I have written a cursor j1 which wil select the jnl_num column values from the table jnl.

The code looks like this:
procedure update_jsl
is

cursor j1 is select jnl_num from jnl where completed is null

type ty_jnl is table of jnl.jnl_num%type;
t_j ty_jnl;
i: number:=1;
begin
open j1
loop
fetch j1 into t_j(i)
exit when j1%not_found;
i:=i+1;
end loop;
close j1;
forall j in t_j.first..t_j.last
update jsl
set c1=value1;
c2=value2;
where length(jnl_num)>1 and
jnl_num=t_j(j);
commit;
end update_jsl;


I have done the same thing using block update method also. i.e fetching the records to be updated using a cursor and updating them one by one using a cursor.

Actually the bulk update method basically takes less time compared to the other 2 methods. but in my case due to the database structure the bulk update method is also taking the same time as the block update or update by cursor method.

so can you please tell me if there is any other method using which i can update the records at a faster rate..
Re: Fastest way to fetch and update records from a table consisting of million rows [message #215990 is a reply to message #215895] Wed, 24 January 2007 09:49 Go to previous messageGo to next message
zohra_banu83
Messages: 9
Registered: January 2007
Location: bangalore
Junior Member
Hi, thanks a lot.

Let me tell you clearly what exactly the scenario is.
I have a table called Jnl and another table called jsl. jnl_num is the primary key for both the tables.
I have written a cursor j1 which wil select the jnl_num column values from the table jnl.

The code looks like this:
procedure update_jsl
is

cursor j1 is select jnl_num from jnl where completed is null

type ty_jnl is table of jnl.jnl_num%type;
t_j ty_jnl;
i: number:=1;
begin
open j1
loop
fetch j1 into t_j(i)
exit when j1%not_found;
i:=i+1;
end loop;
close j1;
forall j in t_j.first..t_j.last
update jsl
set c1=value1;
c2=value2;
where length(jnl_num)>1 and
jnl_num=t_j(j);
commit;
end update_jsl;


I have done the same thing using block update method also. i.e fetching the records to be updated using a cursor and updating them one by one using a cursor.

Actually the bulk update method basically takes less time compared to the other 2 methods. but in my case due to the database structure the bulk update method is also taking the same time as the block update or update by cursor method.

so can you please tell me if there is any other method using which i can update the records at a faster rate..
Re: Update all the retrived records at once [message #215994 is a reply to message #215900] Wed, 24 January 2007 10:00 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
When you say jnl_num is the primary key, do you mean there is a primary key constraint on the two tables ?

Has dbms_stats been run against the tables and have you checked that the update statement is definitely using the primary key index ? You can do this by getting the explain plan or using sql*trace.

How many rows are being retrieved from jnl ? For this to work well, it should be a small number i.e. no more than a few thousand.

Why do you need the condition length(jnl_num) > 1 in the update ? Since jnl_num is matched to the jnl_num from jnl, you could just add this condition to the cursor i.e.

cursor j1 is select jnl_num from jnl where completed is null
and length(jnl_num) > 1.



Re: Update all the retrived records at once [message #216026 is a reply to message #215989] Wed, 24 January 2007 13:08 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Isn't it fun to now have to look in two separate forums to see the answers to your duplicate post?
Previous Topic: date condition help
Next Topic: comparing sum values
Goto Forum:
  


Current Time: Wed Dec 07 22:22:50 CST 2016

Total time taken to generate the page: 0.22280 seconds