I have this query that runs slow li

[discusmania]

I have this query that runs slow like hell (event until script time out when running from ASP page) and it take minutes when query from sqlplus prompt. Does any one have ideas to speed up the query? please help me...

SELECT /*+rule*/ count(cause) cause_occ FROM act_item t1,DOWNTIME t2 ,location_hier T3
WHERE t1.date_rqs BETWEEN to_date('1-Jan-2003','dd-mon-yyyy') AND to_date('1-Mar-2003','dd-mon-yyyy') AND t1.item_id=t2.item_id
AND t1.issue_type='ST' and t1.cause is not null and t1.category in ('REPAIR','MISCHUCK') and t2.nickname=t3.id_name
and t3.area='FE'

 

[sguslan]

Check your indexes. You probably need an index on one or more fields.
By the way you may find more help at an Oracle Thead at TekTips. Here is the URL fro the ORacle Thead forum186

 

[topher0303]

I would have to agree that it could very well be an index problem. The other thing I noticed you are using rule based optimizer. Why? Cost based is much more efficient assuming you have your statistics up to date.

Chris.