This discussion is archived
3 Replies Latest reply: Jun 26, 2011 11:44 PM by Alain RSS

merge slow on big (but not huge) table

Alain Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)

Legend

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