CODEWITHSUNDEEP
sqloracle-databaseperformance
SOF
SOF

Reputation: 3

Why is it so slow this oracle query?

I have a very big table and I need to take the individual rows that meet these requirements:

TABLE:

id  c1  c2  c3  c4  c5
1   101 102 1   2   3
2   101 102 3   2   1
3   105 104 6   0   1
4   103 108 2   0   0


RESULTS:

id  c1  c2  c3  c4  c5
1   101 102 1   2   3
2   101 102 3   2   1
3   105 104 6   0   1


QUERY

SELECT * FROM table
  WHERE ((column1, column2) IN (
                                  SELECT column1, column2 FROM table
                                    WHERE ...
                                    GROUP BY column1, column2
                                    HAVING COUNT(*) > 1
                                )
        )
        OR column3 > 5;

I want to know the internal functionality of this query to understand what happens and why takes so much time.

And what would be the best way to do it ?

Thank you and sorry for my English.

Upvotes: 0

Views: 307

Answers (1)

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

Try analytic queries which will eliminate in clause. It can be faster as it will eliminate the join. If there is where condition it should be applied inside the nested query 

select distinct * from (
  select t.*, count(1) over (partition by column1, column2) cnt
  from table t where ...)
where cnt > 1

Also your query might run faster if you have index on column1 and column2. It will perform full table scan and also might perform hash join. If you can share the explain plan, I can pin point the issue.

Upvotes: 2

Related Questions