Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Question Unanswered: Query optimization

    I need to delete some records and I don't know how to optimize the query.

    I have two tables, TA and TB, each having two fields (see attached gif for graphical explanation).

    also:
    TA1, TA2 is the primary key of TA.
    TA1 and TA2 are foreing keys in TA, both referencing TB.TB1.
    TB.TB1 is the primary key of TB.

    And now, for the question itself:

    I want that, given a value for TB.TB2 (x, y or z in the image), delete all rows in TA that reference (either by TA1 or by TA2 or both) a column in TB having this value in TB2.

    For example, if the value is x, the associated TB1s are (b,d) and erased records should be 1, 2, 4, 5, 7, 9, 11

    if the value is y, the associated TB2s are (e, f, g) and erased records should be 3, 8, 10

    if the value is z, the associated TB3s are (a, c) and erased records should be 1, 2, 4, 5, 6, 7, 8, 10, 11.

    In the real database, TA has about 55.000 records and TB about 25.000.

    I have tried this query:

    DELETE FROM TA
    WHERE EXISTS
    (SELECT TB.TB1
    FROM TB B
    WHERE
    B.TB2= ??? AND
    (TA.TA1=TB.TB2 OR TA.TA2=B.TB2))

    and also this one

    DELETE
    FROM TA
    WHERE TA1 IN
    (SELECT TB1
    FROM TB
    WHERE TB2= ???)
    OR
    TA2 IN
    (SELECT TB1
    FROM TB
    WHERE TB2= ???)

    And both take about one minute, which is too much time.

    I am using Oracle9i Release 9.2.0.7.0.

    Any help will be much appreciated. Thanks for reading!
    Attached Thumbnails Attached Thumbnails schema.gif  

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What is the execution plan for that query?
    What kind of indexes are defined on the tables?

  3. #3
    Join Date
    Feb 2009
    Posts
    62
    Post a set of create table and insert statements, and I'll have a look at it.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Wrong logic?

    Maybe you got the wrong delete logic, try this:
    Code:
    SQL> select * from ta order by 1;
    
            SQ TA1        TA2
    ---------- ---------- ----------
             1 c          b
             2 a          d
             3 f          g
             4 b          a
             5 d          c
             6 a          e
             7 b          a
             8 e          c
             9 d          b
            10 a          f
            11 c          b
    
    11 rows selected.
    
    SQL> select * from tb;
    
    TB1        TB2
    ---------- ----------
    a          z
    b          x
    C          z
    d          x
    e          y
    f          y
    g          y
    
    7 rows selected.
    
    SQL> 
    SQL> DELETE FROM ta
      2        WHERE EXISTS (SELECT 'ok'
      3                        FROM tb b
      4                       WHERE b.tb2 = 'x'
      5                             AND (ta.ta1 = b.tb1 OR ta.ta2 = b.tb1))
      6  /
    
    7 rows deleted.
    
    SQL> select * from ta order by 1
      2  /
    
            SQ TA1        TA2
    ---------- ---------- ----------
             3 f          g
             6 a          e
             8 e          c
            10 a          f
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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