troubleshooting Question

duplicates

Avatar of anumoses
anumosesFlag for United States of America asked on
Oracle Database
23 Comments2 Solutions367 ViewsLast Modified:
select SCHEDULE_DATE, SOCIAL_SECURITY_NO,
          SITE_CODE, START_TIME, END_TIME
  from dept_staff
where schedule_date = '25-jun-2011'      
----------------------
SCHEDULE_DATE|SOCIAL_SECURITY_NO|SITE_CODE|START_TIME|END_TIME
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|aaaaaaaaa||630|300
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
6/25/2011|bbbbbbbbb||800|400
-------------------
Here data for each employee is repeated 13 times. We go by social_security_no(key).
I need to find the duplicates for the schedule date.
 I tried this query below. but takes too long to execute. I have to kill the execution.

select * FROM dept_staff a
 WHERE a.ROWID >
          ANY (SELECT ROWID
                 FROM dept_staff M
                WHERE M.SOCIAL_SECURITY_NO = a.SOCIAL_SECURITY_NO
           and m.schedule_date between '01-JAN-2011' and '30-JUN-2011');
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 23 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros