| This query is running forever - urgent help pls [message #10431] |
Wed, 21 January 2004 21:43  |
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 #10441 is a reply to message #10431] |
Thu, 22 January 2004 06:08  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
It is difficult to tune query remotely. You can write it differently, but did you ANALYZE your tables?
I would first ANALYZE both abc and exp tables (compute statistics for tables for all indexes for all indexed columns) and then I would SET TIMING ON SET AUTOTRACE TRACEONLY and play with different selects. For example,
1.
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 WHERE a.transaction_type IN ('A', 'C')
AND a.email_address IN (SELECT email_address FROM ABC WHERE person_number<>a.person_number)
AND person_number IN (SELECT person_number FROM abc
MINUS
SELECT person_number FROM exp WHERE error_code IN ('E0003','E0005') );
2. SELECT 'E0005',
'NB ID is duplicate - Matching Email (' || a.email_address || ') with other ABC record (' || AA.resource_id || ') in input data',
aA.person_number,
aA.resource_id FROM abc AA WHERE transaction_type IN ('A', 'C'),
(SELECT email_address FROM abc WHERE transaction_type IN ('A', 'C')
HAVING COUNT(person_number)>1
GROUP BY email) A
WHERE aa.email=a.email
AND NOT EXISTS...
3.
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 WHERE a.transaction_type IN ('A', 'C')
AND EXISTS (SELECT 'x' FROM abc WHERE transaction_type IN ('A', 'C')
AND email_address=A.email_address
AND person_number<>a.person_number)
AND NOT EXISTS ...
etc.
|
|
|
|
| Correction [message #10442 is a reply to message #10441] |
Thu, 22 January 2004 06:11 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
I did not run these queries and I hope you will be able to correct them... I just saw an error in my first query. It should be
SELECT 'E0005',
'NB ID is duplicate - Matching Email (' || a.email_address || ') with other ABC record (' || a.resource_id || ') in input data',
a.person_number,
a.resource_id
FROM ABC a WHERE a.transaction_type IN ('A', 'C')
AND a.email_address IN (SELECT email_address FROM ABC WHERE person_number<>a.person_number)
AND person_number IN (SELECT person_number FROM abc
MINUS
SELECT person_number FROM exp WHERE error_code IN ('E0003','E0005') );
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
