Home » RDBMS Server » Performance Tuning » Query taking time
Query taking time [message #401506] Tue, 05 May 2009 05:44 Go to next message
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 #401510 is a reply to message #401506] Tue, 05 May 2009 05:54 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's quite difficult to help tune a query we can't see.

Read the orafaq forum guide and post all the required information.
Re: Query taking time [message #401515 is a reply to message #401506] Tue, 05 May 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More precisely you can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Query taking time [message #401820 is a reply to message #401510] Wed, 06 May 2009 09:52 Go to previous messageGo to next message
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 #401821 is a reply to message #401506] Wed, 06 May 2009 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
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); 


http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Query taking time [message #401827 is a reply to message #401820] Wed, 06 May 2009 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sunilcse1 wrote on Wed, 06 May 2009 16:52
Ya my query is like this:
...
Now can you please suggest now how I'll retrive the query faster.


Read the posts and links we provided you and give us the required information.

Regards
Michel

Re: Query taking time [message #401831 is a reply to message #401821] Wed, 06 May 2009 10:36 Go to previous messageGo to next message
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 #401833 is a reply to message #401831] Wed, 06 May 2009 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Read the posts and links we provided you and give us the required information.

Regards
Michel

Re: Query taking time [message #401871 is a reply to message #401506] Wed, 06 May 2009 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way that I could optimise it.
NO
IS NULL forces Full Table Scan (FTS).
Re: Query taking time [message #401882 is a reply to message #401506] Wed, 06 May 2009 17:22 Go to previous messageGo to next message
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 #401897 is a reply to message #401506] Wed, 06 May 2009 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN
Re: Query taking time [message #401956 is a reply to message #401506] Thu, 07 May 2009 02:43 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
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.

[Updated on: Thu, 07 May 2009 02:44]

Report message to a moderator

Re: Query taking time [message #401970 is a reply to message #401956] Thu, 07 May 2009 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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 #402077 is a reply to message #401956] Thu, 07 May 2009 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
varu123 wrote on Thu, 07 May 2009 00: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.


Have at http://www.orafaq.com/forum/m/402021/136107/#msg_402021 & show us how it should be down.
Re: Query taking time [message #402214 is a reply to message #401506] Fri, 08 May 2009 02:10 Go to previous message
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.
Previous Topic: Transaction memory/space amount configuration
Next Topic: 10-15 seconds on a 200 record DataSet
Goto Forum:
  


Current Time: Fri Dec 09 21:13:24 CST 2016

Total time taken to generate the page: 0.06103 seconds