deleting large volumes of data

From: Andreas Haunschmidt <Haunschmidt_at_gmx.net>
Date: Mon, 10 Dec 2001 14:10:40 -0800
Message-ID: <F001.003D956C.20011210140026@fatcity.com>

Dear listers!

what would you suggest as the best method for

        delete from <a large table> where <some rows have to disappear>

e.g. delete from a partitioned data warehouse table, where the delete transaction
would not fit into a rollback segment ?

method 1

        What do you think of updating the partition key in order to move 
        the unwanted rows to a special "waste basket" partition and 
        then truncate this partition?

method 2
        how about the following code ?
              loop
                      delete from T where <some predicate> AND rownum < 10000;
                      exit when sql%rowcount = 0;
              commit;
              end loop;
              commit;

( source:
http://asktom.oracle.com/pls/ask/f?p=4950:8:313997::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1261400453244 )

Thanks

        Andreas
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Andreas Haunschmidt
  INET: Haunschmidt_at_gmx.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Dec 10 2001 - 16:10:40 CST