MINUS / NOT IN / Not Exists - which one is the best
Ash_Jul 7 2009 — edited Jul 13 2009Dear All,
I have to run delete for parent and child records on production database which has very high volume of data (millions of records) on Oracle10g. Currently the scripts are using NOT INs to delete child records as below:
delete from T1 where T1.x is not null and T1.x not in (select x from T2);
I was planning to change it to :
delete from T1 where T1.x is not null and T1.x not in (select x from T2 where x is NOT NULL);
Could any one please help me with a better way of writing this ? Will it be faster if I use MINUS or NOT Exists instead on NOT IN?
Sorry to bother you all with the basic question but there doesn't seem to be specific rule for use of any one above (may be because Oracle10g uses CBO) ?.
Any help is appreciated and many thanks in advance.