3 Replies Latest reply: Jun 27, 2011 1:44 AM by Alain RSS

    merge slow on big (but not huge) table

    Alain
      11g xe default config (no archive log).
      I have a table borabiec.abiec_animal, pk=id, ~4.000.000 rows
      I have a table borabiec.todelete, ~1.500.000 rows, unique key is id.

      I would like to remove from borabiec.abiec_animal where id is in borabiec.todelete.

      Tried:
      delete from borabiec.abiec_animal aa where exists(select 1 from borabiec.todelete where id=aa.id);
      =>no answer in less than 10 hours, cancelled.

      I also tried:
      merge into borabiec.abiec_animal aa
      using borabiec.todelete dd
      on (aa.id=dd.id)
      when matched then
      update set aa.coat='delete'
      delete where 1=1;
      =>no answer in less than 10 hours, cancelled.

      A full db export takes 20 minutes, but deleting half of a table seems to take days (and db is 11g xe, less than 11gb data).

      =>Is there a way to speed this process? Should I toggle to archivelog mode?

      Regards,
      Alain

      Edited by: Alain on 24-juin-2011 0:18
        • 1. Re: merge slow on big (but not huge) table
          Udo
          Alain,

          did you ensure that you've gathered fresh and valid statistics for those tables, or did you try a hint to enforce index-usage?
          But it could be that bad anyway with that amount of data to be processed in one step. XE doesn't only have the 11 GB data limit, but also uses a maximum of 1 GB of memory in total (SGA+PGA). I'd expect your queries will both exceed that value.

          "Partitioning breaks bottlenecks" => You could try to split your "todelete"-table into multiple smaller parts, and to ensure they are in memory to keep speed, you could use a temporary table for that purpose: use a loop to iterate over a block of perhaps 250000 entries and delete using that block as reference.
          I know, this sounds complicated, but after all, that's part of what paid versions are supposed make life easier...

          -Udo
          • 2. Re: merge slow on big (but not huge) table
            Etore Schiavini
            Why dont use the simplest way:

            delete from borabiec.abiec_animal aa where aa.id in( select td.id from borabiec.todelete td);
            • 3. Re: merge slow on big (but not huge) table
              Alain
              Because this would take more time to complete:
              at each row he will make a full scan of todelete.

              I also tried
              delete from borabiec.abiec_animal aa where aa.id in( select td.id from borabiec.todelete td where td.id=aa.id);
              but it is slow as well.

              (and my experience tells me that exists is faster than a subselect with an IN)