-
I have to do number of updates on a table which has million records.
eg.
Update mwebMatrix
Set Mat_Approved = 10
Where Mat_Category in(1,3) ;
commit;
If I do this, it takes hours and nothing happens.
So I broke these statements in small parts..
Update mwebMatrix
Set Mat_Approved = 10
Where Mat_Category = 3 and mat_id between 1 and 250000;
commit;
Update mwebMatrix
Set Mat_Approved = 10
Where Mat_Category = 3 and mat_id between 250001 and 350000;
commit;
Update mwebMatrix
Set Mat_Approved = 10
Where Mat_Category = 3 and mat_id between 350001 and 450000;
commit;
Even these once are taking a long time.. and I don't know if anything is getting updated or not..
I can't do select count(*).. either.
Is there any faster way to do this.
How about a cursor with loop and commits for a batch or do you think that will be bad ?
Can I use ROWNUM function to do the batch updates, like in thread:
http://www.dbasupport.com/forums/sho...0&pagenumber=2
thanks for help
Sonali
-
Oh, I forgot to mention..
mat_category is indexed and mat_id is PK.
One more question:
I am doing one of the updates..
update mwebMatrix
set Mat_Role_ID = 1
where Mat_role_id is null;
commit;
I want to see what is happening with this update..because its been 2 hours since I ran it.. and its still not finished.
I did select * from v$session and the session is active.
I cannot do.. to see if any records are getting updated because it hungs and never comes back.
select count(*) from mwebmatrix where mat_role_id = 1; show
When I did this it said..
SQLWKS> Select event from v$session_wait
2> where sid=11
3>
EVENT
-----------
db file sequential read
what does this mean ?
thanks
[Edited by sonaliak on 06-28-2001 at 10:27 AM]
-
Hi,
for this count of records is loop far better method.
But you should use ROWID, not ROWNUM:
...
FOR rec IN (SELECT ROWID FROM mwebMatrix WHERE Mat_Category in(1,3) )
LOOP
UPDATE mwebMatrix SET Mat_Approved = 10 WHERE ROWID = rec.ROWID; --you can also select PK instead of ROWID and change WHERE clause to "mat_id=rec.mat_id"
COMMIT --or you can count rows and commit every 100, 1000 ...
END LOOP;
...
I can't promise you it'll be faster, but you can see progress from another session since small batch is commited, not the whole update. Another advantage: if you break this script you can start it again without waiting for rollback.
The piece of code above is without warranty, I haven't tested it ...
And your second question...
If a session makes long update, other sessions aren't able to see changes until update is commited.
Try to estimate count of records with "Mat_role_id is null" and consider using of loop with batch commit again.
The table seems to be large and Mat_role_id is probably not indexed, so full table scan is the only method to find records with "Mat_role_id is null" and that's exactly what you see in v$session_wait.
HTH,
Ales
-
Some how I managed to get the updates done.. though it took really long long time.. I could not make it any faster.
Mat_role_id was indexed.
But now I want to do a delete:
delete from mwebmatrix where mat_category = 2000;
To make it faster I am doing it in batches like..
delete from mwebmatrix where mat_category = 2000 and mat_id <500000;
mat_id is PK and mat_category is FK and indexed.
I am the only one using this database at this time and this is the only query I will be running. Do you think dropping the index on mat_category will make it faster ?
thanks
