performance on delete the rows from a huge table

From: <linda_shi10_at_yahoo.com>
Date: 18 Mar 2005 20:17:32 -0800
Message-ID: <1111205852.160171.71280@o13g2000cwo.googlegroups.com>

I am new to ORACLE. Please advise! Thanks. I have a table which contains about 20-30 million rows. I need to delete some 'duplicate' rows by certain rules. So I create a produce try to do the job.

my query example:

        delete from A m
           where max(a.x5) is not in
             (select * from A n
                 where m.x1 = n.x1 and
                       m.x2 = n.x2 and
                       m.x3 = n.x3 and
                       m.x4 = n.x4 )
                ;

I need to commit it every 2000 row is processed. Say rownum <= 2000.

The problem is that the table A contains too many rows. It seems that it will take forever to do the job. How can I improve the query performance?

Thanks,
Linda Received on Fri Mar 18 2005 - 22:17:32 CST