This discussion is archived
2 Replies Latest reply: Oct 31, 2012 7:15 AM by ScottK RSS

Bulk Deletes

499195 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

    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: SQL and PL/SQL

    David
  • 2. Re: Bulk Deletes
    ScottK Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points