|
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|