-
Originally posted by DaPi
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
Yeah you are right.That's why I am doing it on a cloned table instead of the original one.After the procedure is succesful, I will run it on the original table.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Originally posted by simply_dba
After the procedure is succesful, I will run it on the original table.
The problem is that a 1555 will depend on the work-load, db configuration etc. So a successful test-run does NOT guarantee success in production.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Originally posted by DaPi
The problem is that a 1555 will depend on the work-load, db configuration etc. So a successful test-run does NOT guarantee success in production.
That's true. but now I have developed a procedure to delete using collection ( A DBA doing a Developer's work. I wonder what Mr Hanky will say) storing all the rowids in another table.I think this ay I can ensure data consistency. Even if I ran into 1555.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Originally posted by simply_dba
. . .but now I have developed a procedure to delete using collection . . . . storing all the rowids in another table.
Sounds good to me.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
cant you do smth like this?
Code:
loop
Delete 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))
and abs(a.amount)=abs(b.AMOUNT)
and a.md=b.MD
and a.comment=b.COMMENT
and a.type=b.TYPE)
and rownum <= 100000;
commit;
exit when sql%rowcount < 100000;
end loop;
-
Originally posted by pando
cant you do smth like this?
NO ! ! ! !
If my reading is right (simply_dba hasn't said otherwise) the requirement is to delete pairs of matching rows. This technique could delete one of a pair in the first loop and would NOT select the second in subsequent loops beacuse it no longer matches.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
yes
it might work if the number of matching rows is not bigger than 100000 rows correct?
-
Originally posted by pando
yes
it might work if the number of matching rows is not bigger than 100000 rows correct?
If it's less than 100000 you don't need a loop.
If it's more than 100000 you risk your data integrity.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
I wonder how no one asked.... wat is the number of records you want to retain?
If you are trying to delete more than 25% of records from a table.. go for parallel insert into temp table the records you want to retain.. drop main table.. rename temp table to main table..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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
|