CODEWITHSUNDEEP
sqloracle11gself-joinoracle11gr2sql-tuning
Naveenraja Subramaniam
Naveenraja Subramaniam

Reputation: 365

Self Join is making my task run slow in Oracle 11g

I have a table named as event_extra and on that to find duplicates based on some conditions I'd written the following self-join query, but when the records are more(1million) it is taking lot of time.

Table Fields are follows

evt_id
extra1
extra2
extra3
extra4
extra5
extra6
extra7
extra8
extra9

This table has Unique index on evt_id.

Self-Join Query

select cde1.evt_id,
       cde1.extra1,
       cde1.extra2,
       cde1.extra3,
       cde1.extra4,
       cde1.extra5,
       cde1.extra6,
       cde2.evt_id as evt_id2,
       cde1.extra7,
       cde1.extra8,
       cde1.extra9,
  from event_extra cde2
  join event_extra cde1
    on (( cde1.extra4 = cde2.extra4 and cde1.extra7 = cde2.extra7) or
       (cde1.extra4 = cde2.extra5 and cde1.extra7 = cde2.extra8) or
       (cde1.extra4 = cde2.extra6 and cde1.extra7 = cde2.extra9) or
       (cde1.extra5 = cde2.extra4 and cde1.extra8 = cde2.extra7) or
       (cde1.extra5 = cde2.extra5 and cde1.extra8 = cde2.extra8) or
       (cde1.extra5 = cde2.extra6 and cde1.extra8 = cde2.extra9) or
       (cde1.extra6 = cde2.extra4 and cde1.extra9 = cde2.extra7) or
       (cde1.extra6 = cde2.extra5 and cde1.extra9 = cde2.extra8) or
       (cde1.extra6 = cde2.extra6 and cde1.extra9 = cde2.extra9))
   and ((to_number(cde1.extra3) >= to_number(cde2.extra1) and
       (to_number(cde1.extra3) <= to_number(cde2.extra2))) or
       ((to_number(cde1.extra1) >= to_number(cde2.extra2)) and
       (to_number(cde1.extra1) <= to_number(cde2.extra3))) or
       ((to_number(cde1.extra2) >= to_number(cde2.extra1)) and
       (to_number(cde1.extra2) <= to_number(cde2.extra3))))
   and cde1.evt_id > cde2.evt_id
 order by cde1.evt_id;

Is there any other approach available so that the performance of the above query can be improved?

Database Version: Oracle11g

Upvotes: 0

Views: 334

Answers (1)

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

SEE the conditions the query is bound to run slow.Also , You have used order by this again is making things worst. What you can do is remove the order by and select this output in a temporary table and perform the operation and selection on that table. Also, Try placing indexes on the columns.

Upvotes: 1

Related Questions