Is mload delete faster than the Bteq delete?

Database
Enthusiast

Is mload delete faster than the Bteq delete?

Hi

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

4 REPLIES
Senior Supporter

Re: Is mload delete faster than the Bteq delete?

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

?

Enthusiast

Re: Is mload delete faster than the Bteq delete?

 

partition question

Hi,

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 ,

 NO RANGE);

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?

Enthusiast

Re: Is mload delete faster than the Bteq delete?

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.....?

Re: Is mload delete faster than the Bteq delete?

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