-
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?
-
Originally posted by ramit
Would that work on a 8i database?
Test it.
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|