Home » SQL & PL/SQL » SQL & PL/SQL » query taking long time
query taking long time [message #195515] Fri, 29 September 2006 02:48 Go to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Hi Guru's,

i am running the below query on oracle database and it is taking bit long time. I have 100,000 rows seleted. Can i refine this query as when i am producing the report from crystal with this query and the session is timing out.

select vsc.created,vsc.ID,vsc.DESCRIPTION,vf.parent
from V_service_call vsc,v_folder vf
where vsc.folder=vf.text and
vf.parent NOT IN ('Gold Coast','commisioned Customers') and vsc.status IN ('Closed','Completed');

Please help if you think i can modify the query.

Warm Regards
Rupi
Re: query taking long time [message #195518 is a reply to message #195515] Fri, 29 September 2006 03:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Other than checking that you've got indexes on vsc.status (although if you've got a lot of Closed and Completed records then that won't help) and vf.parent there's not a lot you can do with a query this simple.

You could try creating a materialized view of the query and running the report on the view instead.
Re: query taking long time [message #195571 is a reply to message #195515] Fri, 29 September 2006 08:50 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
rmalhi


Your query is quite simple and straight forward. May be you can try creating a bit map index on the vf.parent and vsc.status columns and check.

Thanks
Re: query taking long time [message #195780 is a reply to message #195515] Mon, 02 October 2006 06:25 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post EXPLAIN
Previous Topic: Stored Procedure Performance ..
Next Topic: Tough Query.
Goto Forum:
  


Current Time: Sun Dec 04 12:25:38 CST 2016

Total time taken to generate the page: 0.07878 seconds