I have a situation i which i have to delete almost 98% data from a 3 TB table. I used bteq delete and had to abort it in between. This caused the table to go for a rollback that lasted for more than a day.
Now , i can use the mload delete which doesn't use transient journaling so the table won't go into rollback if i abort the job. Is there any other benifit of using mload delete apart from this point i.e is mload delete faster than the bteq delete(if yes then how much of a difference can we see) or does it have any other benifit?
Yes, it is faster due to the reason you already mentioned: It doesn't use a journal.
It scans and deletes...
But did you consider to
1. copy the 2% of data which remains into a new table
2. rename table to old and new table to current table
3. drop old table
Yes, i am trying to implement the same solution that you suggested. But this question was lingering in my mind..For that i have another question(i have posted that as well..but didn't get a proprt reply till now). Could you suggest?
Yes, if you give any inequality condition in where clause, it will go for FTS (as per my knowledge)....
And to answer your question...let me rephrase the question this way......Do you want to know/check the values which are in No Range paritition ? If this is your question then.....condition like "where col in (0,27,100)" will go to No Range partition and search for it....
I havent tried this...but this is what it should do....
Finally the conclusion is that the other than the values which are not paritioned (like 0,27,28,29.......so on) will be in NO Range parition..and (partition is on 1,2,3,4,5,6,.....,26)..
Does it answers your question.....?