All Forums Database
teradatauser2 236 posts Joined 04/12
08 Oct 2012
Is mload delete faster than the Bteq delete?

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?
Thanks !!

ulrich 816 posts Joined 09/09
08 Oct 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

teradatauser2 236 posts Joined 04/12
08 Oct 2012



partition question

I have a table tab1 with partition on a integer type column col with partition defn
PARTITION BY RANGE_N(col BETWEEN 1 ,2 ,3 ,4 ,6 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,21 ,23 ,24 ,25  AND 26 ,
I have a query like:
sel * from tab1 where col<>3. This query is going for a full table scan and not using partition.
I wanto write a query like :
sel* from tab1 where col in (,1,2,4..). This uses partition elimination. But the question is, how do i give the values in the 'no range' as i am not aware what values it might have.
This is a very big table with a size of around 3 TB and the first query shows a large time in explain.

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?

suresh1802 8 posts Joined 10/12
08 Oct 2012

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, on) will be in NO Range parition..and (partition is on 1,2,3,4,5,6,.....,26)..
Does it answers your question.....?

williams22 3 posts Joined 04/12
09 Oct 2012

mload delete is faster as it deletes data row by row!!

You must sign in to leave a comment.