2 Replies Latest reply on Oct 31, 2012 2:15 PM by ScottK

    Bulk Deletes

      I have a table A which has about 100 million rows and Table B which has 17 million rows. The common column bwtween the table is the CONTENT_ID. What i am trying to achieve is keep the CONTENT_ID which matches from Table B in the TABLE A and delete the rest of the coloumn in Table A.

      I can create a temp table with the common content_id values and then TRUNCATE Table A and rename the temp table with the original name. I dont want to do this as i dont have downtime window for the Table A which gets heavily used.

      Is ther a way i can do this through PL/SQL where in i dont use TRUNCATE or TEMP tables. If so can you please direct me to the steps what needs to be done.
        • 1. Re: Bulk Deletes
          David Last-Oracle

          This is the forum for the SQL Developer Data Modeler product.
          I suggest you try asking your question on the SQL and PL/SQL Forum: PL/SQL and SQL

          • 2. Re: Bulk Deletes
            David is correct in that this is the wrong forum, but a simple solution would be to use set theory with Oracle excels at. You don't need a temp table or truncate. The basic SQL statement would be...

            delete from a
            where a.content_id in (select a.content_id from a minus select b.content_id from b) /* give me all the rows that exist in A which don't exist in B */

            If you take the rename talbe approach you will also to collect up all the grants, synonyms, indexes and other dependent objects and reissue those commands to put them back in place.