| This query is running forever - urgent help pls [message #64756] |
Wed, 21 January 2004 21:45  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
