OraFAQ Forum: SQL & PL/SQL » How to tune SQL Query

Home » SQL & PL/SQL » SQL & PL/SQL » How to tune SQL Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to tune SQL Query [message #19930]

Thu, 18 April 2002 18:14

Rajesh Mohan
Messages: 3
Registered: April 2002

Junior Member

Hi,
I am facing a problem. I have one query which is fetching hundreds of records from a table having 6 million records. My query is following

select roll_no, sum(expiry_date) from v_master
group by roll_no having sum(expiry_date) <= sysdate;

This query is taking more than 4 minutes to retrieve the data. How can I make it fast.

Pl. give me some solution.

Thanks in anticipation.

Regards
Rajesh Mohan

Report message to a moderator

Re: How to tune SQL Query [message #19936 is a reply to message #19930]

Fri, 19 April 2002 00:39

Mike
Messages: 417
Registered: September 1998

Senior Member

Hi,

As far as I know you can not sum a date datatype. So could you please say what kind of datatype your expiry_date is and what is the sense of makink the sum of a date???

Mike

Report message to a moderator

Re: How to tune SQL Query [message #19940 is a reply to message #19930]

Fri, 19 April 2002 06:54

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

You are doing a sum() on expiry_date ? You logic does not make sense ? Be more specific.

Report message to a moderator

Re: How to tune SQL Query [message #19956 is a reply to message #19930]

Sun, 21 April 2002 18:23

Rajesh Mohan
Messages: 3
Registered: April 2002

Junior Member

Hi,

Sorry for giving wrong query. I am using max() not sum(). Actually my query is

select roll_no,max(expiry_date) from v_master
group by roll_no
having max(expiry_date)<=sysdate;

Thia v_master table is having 6 million records and this query is taking more than 4 minutes.
Please provide me some solution to make it fast.

Thanks & Regards
Rajesh Mohan

Report message to a moderator

Re: How to tune SQL Query [message #19969 is a reply to message #19930]

Mon, 22 April 2002 05:53

Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida

Senior Member

You won't be hitting the index becoz you have a group function on it (max()). Else the SQL is pretty simple and correct.

Report message to a moderator

Previous Topic:	Can you pls give sql query for a little complex functionality
Next Topic:	The query behind the DESCRIBE command?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 03:13:36 CDT 2024