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
) ;

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

---

Would that work on a 8i database?

Quote:

---

Originally posted by ramit
Would that work on a 8i database?

---

Test it.

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