Results 1 to 7 of 7

Thread: slow delete sql

  1. #1
    Join Date
    Apr 2004
    Location
    sydney, oz
    Posts
    4

    Unanswered: slow delete sql

    Hi there,

    I have a delete sql that runs too slow (around 15 mins). I would like
    to improve it.

    It looks like this:

    delete from table_a a
    where exists (select *
    from table_b b
    where b.columnX = a.columnX
    and b.columnY = a.columnY)

    There are two indexes. IndexA on table_a(columnX,columnY) and IndexB
    on table_b(columnX,columnY).

    Looking at the statement in explain plan, it shows that it's doing
    full scans on both tables. It's not picking up an index hint like
    this:

    delete /*+ INDEX(a IndexA) */ from table_a a
    where exists (select *
    from table_b b
    where b.columnX = a.columnX
    and b.columnY = a.columnY)

    any suggestions?

    Thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    I think the hint would need to be in the sub select,

    delete from table_a a
    where exists (select /*+ INDEX(b IndexB) */ 1
    from table_b b
    where b.columnX = a.columnX
    and b.columnY = a.columnY)

    but if you have a primary key you could try

    delete from table_a
    WHERE primary_key IN (
    SELECT /*+ INDEX(a IndexA, b IndexB) */ a.primary_key
    from table_a a, table_b b
    where b.columnX = a.columnX
    and b.columnY = a.columnY)

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you are using CBO then ensure you statistics are up-to-date ...
    Also, are there any foreign keys (delete cascades) that are not properly
    indexed ??

    HTH
    Gregg

  4. #4
    Join Date
    Apr 2004
    Location
    sydney, oz
    Posts
    4
    The hint does work in the subselect. Thanks, mate.

    It's performing still a bit too slow (table_a has over 5 million rows).

    Can't use the primary key, unfortunately.

  5. #5
    Join Date
    Apr 2004
    Location
    sydney, oz
    Posts
    4
    stats are up-to-date. There are no cascades, thanks!

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try this ...

    delete /*+ INDEX(a IndexA) */ from table_a a
    where exists (select * <==
    from table_b b
    where b.columnX = a.columnX
    and b.columnY = a.columnY)

    (select 'x' from table_b b
    where b.columnX = a.columnX
    and b.columnY = a.columnY)

    Change select * (everything) to 'x' (existance)

    Sorry I didn't catch that earlier ...

    HTH
    Gregg

  7. #7
    Join Date
    Apr 2004
    Location
    sydney, oz
    Posts
    4
    great, haven't tried that before!! thanks i'll try that tomorrow, it's midnight here .. have to catch latest bus.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •