| this query is taking 58 seconds to execute [message #116404] |
Tue, 19 April 2005 17:33  |
rajuakula
Messages: 63
Registered: March 2005
|
Member |
|
|
I have query as below and the output of the query is 92189 records.
select s1.abc_nbr,s1.1cd,s1.2cd,
s1.3cd,s1.4CD,s1.formula,s1.weight,
s2.reference,s3.srno
from owner1.subs s1,owner1.lot s2,OWNER2.MTAB s3
where s1.cid = s2.cid
and s1.nbr = s3.nbr
and s1.create_dt between to_date('04/19/2003','mm/dd/yyyy') and to_date('04/19/2004','mm/dd/yyyy')
order by s2.reference
In table MTAB srno is the pk and index is created on nbr field.
In table lot index is created on cid field and it is also a fk field from table substance.Index is created on reference field also.
In table subs cid is the pk and index is created on the field nbr.
now when I execute the above query it is taking 58 seconds.
How should I tune this query.I created the required indexes as mentioned above.How should I solve this problem.Even I tried with rule based hint also, but no change.
thanks,
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
