Why is it so slow this oracle query?

Question

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

More than one row with the same value repeated in column 1 and column 2
Or column 3 is greater than 5

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'm working with Oracle SQL Developer
This query takes so much time that I have to cancel it but if I remove the 'OR column3 > 5' the query ends in 6 seconds

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.

Durga Viswanath Gadiraju Durga Viswanath Gadiraju · Accepted Answer · 2016-01-07T05:42:23

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.

Why is it so slow this oracle query?

1 Answers