Home » RDBMS Server » Performance Tuning » This query is running forever - urgent help pls
This query is running forever - urgent help pls [message #64756] Wed, 21 January 2004 21:45 Go to next message
Prabha R
Messages: 21
Registered: October 2001
Junior Member
HI,

  I wanted to find the duplicate rows in a table which is having around 300k records. The following is the query i wrote:

SELECT 'E0005',
       'NB ID is duplicate - Matching Email (' || a.email_address || ') with other ABC record (' || b.resource_id || ') in input data',
   a.person_number,
   a.resource_id
       FROM ABC a, ABC b
      WHERE a.transaction_type IN ('A', 'C')
        AND b.transaction_type IN ('A', 'C')
        AND a.person_number <> b.person_number
        AND NVL (a.email_address, 'a') = NVL (b.email_address, 'b')
     AND NOT EXISTS (SELECT 'a' from EXP
          WHERE person_number = a.person_number
        AND error_code in ('E0003','E0005'))

The table ABC has NON-unique indexes on transaction_type, person_number and email_address.  The table EXP is having indexes on person_number and error_code.

I did a explain plan for this query. But could not understand anything from that as i am newbie.

Could somebody pls help me out in tuning this query.  I have been struggling with this for the past 2 days. 

Thanks in advance for any immediate help.

Regards,

prabha.
Re: This query is running forever - urgent help pls [message #64762 is a reply to message #64756] Thu, 22 January 2004 20:51 Go to previous message
Shesh
Messages: 16
Registered: July 2003
Junior Member
Hi,

For finding duplicate rows you can use

'Select * FROM ABC A
WHERE ROWID < (SELELCT MAX(ROWID) FROM ABC B
WHERE A.NBID=B.NBID)'

you can add more conditions in where clause of subquery

Thanks
Shesha
Previous Topic: Partitioned table, Merge Query
Next Topic: how to increase max_processes?
Goto Forum:
  


Current Time: Fri Mar 29 07:14:04 CDT 2024