Query taking time [message #401506] |
Tue, 05 May 2009 05:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sunilcse1
Messages: 23 Registered: June 2007 Location: Bangalore
|
Junior Member |
|
|
I have a query which is searching a whole table to find the duplicate values in it. It is taking a long time to give the o/p (sometimes even 30min). Can some one help me to tune the query to effectly retrive the records.
Thanks,
Sunil Satapathy
|
|
|
|
|
Re: Query taking time [message #401820 is a reply to message #401510] |
Wed, 06 May 2009 09:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sunilcse1
Messages: 23 Registered: June 2007 Location: Bangalore
|
Junior Member |
|
|
Ya my query is like this:
SELECT * FROM emp A
WHERE rowid >
(SELECT min(rowid) FROM emp B
WHERE B.emp_ID = A.emp_ID
and B.dept_id = A.dept_id
and B.sal = A.sal
and B.DOB = A.DOB
and ((B.ADDR_CD = A.ADDR_CD) or (B.ADDR_CD is null and A.ADDR_CD is null) )
and B.END_TMS is NULL and A.END_TMS is NULL);
Now can you please suggest now how I'll retrive the query faster.
|
|
|
|
|
Re: Query taking time [message #401831 is a reply to message #401821] |
Wed, 06 May 2009 10:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sunilcse1
Messages: 23 Registered: June 2007 Location: Bangalore
|
Junior Member |
|
|
I think the SQL which reads the entire table to get a few rows hence I have a lot of unnecessary I/O that is not filling the SGA. I have a billions of records in the table.
Is there any way that I could optimise it. Can I use any other query instead.
Thanks,
Sunil Satapathy
|
|
|
|
|
Re: Query taking time [message #401882 is a reply to message #401506] |
Wed, 06 May 2009 17:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
Quote: | >Is there any way that I could optimise it.
NO
IS NULL forces Full Table Scan (FTS).
|
Are you sure about that?
Surely if the other join fields are indexed as the leading columns (in this case emp_id, dept_id, sal, dob) , it will perform index range scan.
|
|
|
|
|
Re: Query taking time [message #401970 is a reply to message #401956] |
Thu, 07 May 2009 03:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
varu123 wrote on Thu, 07 May 2009 09:43 | After 11 posts the question remains the same unanswered.
And the OP then leaves outta frustation.
You guys need to revive forum rules
or change your approach answering questions.
|
In whichh way? I see nothing in your post that can help us to do it and you are welcome to expose your thoughts in Suggestions & Feedback forum.
Anyway, if someone does not want to read the guide, does not follow the guide or does not reply to the questions we ask to help him, I do not see any way to make him/her answer and he/she is the ONLY responsible of his/her frustation.
Regards
Michel
[Edit: added missing word]
[Updated on: Thu, 07 May 2009 10:07] Report message to a moderator
|
|
|
|
Re: Query taking time [message #402214 is a reply to message #401506] |
Fri, 08 May 2009 02:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
varu123
Messages: 754 Registered: October 2007
|
Senior Member |
|
|
Don't try to paint everything with the same brush.
Your approach was definetly not appropriate.There are other ways to
handle things.You cannot always be tit for tat.
|
|
|