Have you considered alternative methods to achieve the desired end result?Quote:
Originally posted by simply_dba
Facing an age old problem :o
I am running a delete statement in a cursor (to commit in between)
Printable View
Have you considered alternative methods to achieve the desired end result?Quote:
Originally posted by simply_dba
Facing an age old problem :o
I am running a delete statement in a cursor (to commit in between)
How about deleting 100,000 rows in a single SQL statement, commiting, deleting another 100,000, commiting ... ad nauseum. Or until no more are deleted, i suppose.
Use parallelism for deleting/updating 8M rows.
All you need is set up undo tablespace correctly.
Tamil
How do we delete only 100,000 rows in single sql and then move to delete next 100,000 rows any examplesQuote:
Originally posted by slimdave
How about deleting 100,000 rows in a single SQL statement, commiting, deleting another 100,000, commiting ... ad nauseum. Or until no more are deleted, i suppose.
regards
Hrishy
delete from xyz where id between 1 and 100000;Quote:
Originally posted by hrishy
How do we delete only 100,000 rows in single sql and then move to delete next 100,000 rows any examples
regards
Hrishy
This is exactly the situation where a "fetch across commit" can skrew your data. You are deleting depending on the existance of data in the table you are deleting from. If you restart it after a 1555, the cursor will have a very different view of what is to be deleted compared with the first time. ***Quote:
Originally posted by simply_dba
Cursor del_row is select rowid from temp_tab a where exists
(select 1 from temp_tab b where ((a.amount>0
and b.AMOUNT<0) or(a.amount<0 and b.AMOUNT>0)) . . .
In your particular case it *might* be OK.
("fetch across commit" can work BUT ONLY if the target table is read by FTS and your change can be interrupted & restarted - a sal=2*sal update would NOT be a candidate. The best rule is DON'T)
If you use gandolf's suggestion, you could try sorting the collection or temporary table by rowid, so that all the changes to one block are made together.
The suggestion of a "delete . . . where . . . and rownum < 100000" might not be applicable for the reason stated above for "fetch across commit".
added later
*** I've just looked again at the logic. I think you are deleting pairs of balancing entries (where a.amount = - b.amount would have done the trick I think). Your "fetch across commit" could delete one of a pair without deleting the other. My! Your auditors will LOVE that!
I guess this rules out the "and rownum < 100000" option too.
Gandolf rules.
Well that was the first sql, but that was causing a whole lot of sortsQuote:
I've just looked again at the logic. I think you are deleting pairs of balancing entries (where a.amount = - b.amount would have done the trick I think). Your "fetch across commit" could delete one of a pair without deleting the other. My! Your auditors will LOVE that!
I think i will go for the collection
thanks every body
I love you Gandolf :D
btw:
Can you explain that in details DaPi?Quote:
If you restart it after a 1555, the cursor will have a very different view of what is to be deleted compared with the first time. ***
If I'm right about your business needs, you're deleting pairs of matching entries (transactions?):
1) The cursor "selects" 8mio rows including a pair +123 and -123
2) You loop & commmit several times, and by chance this deletes the row +123 & commits, by chance -123 hasn't be processed yet.
3) It crashes with a 1555
4) You restart. The cursor is re-evaluated and "selects" 7mio rows BUT DOES NOT INCLUDE -123 because this is not a paired entry any more; its matching entry no longer exists.
BINGO - your books don't balance
Let's just be friends! ;)Quote:
Originally posted by simply_dba
Well that was the first sql, but that was causing a whole lot of sorts
I think i will go for the collection
thanks every body
I love you Gandolf :D