DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tuning a Self-Join sql query

  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Tuning a Self-Join sql query

    I need some help in making the query below faster. Basically I want to retrieve all values from table1 where col1 has the same value, but col2 has different values.

    I have non unique indexes on both col1 and col2. The below query works, but table1 has 40 million rows in, so it takes forever (3 days...)

    Doing an explain on the query, it does use the index on col1, but not col2 (due to the not equal cluase I presume)

    Any help to speed up/rewrite this query would be greatly appriecated!


    SELECT a.col1
    , a.col2
    FROM table1 a
    WHERE EXISTS ( SELECT 'x'
    FROM table1 b
    WHERE a.col1 = b.col1
    AND a.col2 <> b.col2
    ) ;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Here's a couple of alternatives that might be worth trying. No promises, mind. The second one would benefit from a composite (col1,col2) index, I would think

    Code:
    Select Distinct
       a.col1,
       a.col2
    From
       table1 a,
       table1 b,
    Where
       a.col1 = b.col1 And
       a.col2!= b.col2
    /
    Code:
    Select
       col1,
       col2
    From
       (
       Select
          col1,
          col2,
          Count(Distinct col1) Over
             (Partition By col1) cd_col2
       From
          table1
       Where
          col1 is not null
       )
    Where cd_col2 > 1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2003
    Posts
    3
    Would that work on a 8i database?

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Originally posted by ramit
    Would that work on a 8i database?
    Test it.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1.If col1 has repeated values, then create an index on (col1, col2) with compress 1, and put this index in buffer_pool keep (or recycle), to reduce I/O calls.

    2.Also try to run the query in parallel mode, since all the rows are selected in the outer query.

    3.Create 2 bit map indexes on col1 and col2 separately , and use it in the subquery with index_combine hint.

    Tamil

Posting Permissions

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


Click Here to Expand Forum to Full Width