Problem with QUERYHELP!!!!!!!!!!!!!!!!!!!!!!!

[dsudhir]

Hi,

I am facing a problem with a select statement
I am doing a join on three tables and my query is as follows

Select
Date,Pressure
from
SystemName SN,
WellName WN,
DailyTB DT
where
SN.SID = WN.SID and
WN.ID = DT.ID and
DT.Date between '2000-06-01' and '2000-07-01'

if i use the above query it takes about 5 minutes to return the results.
But if i change the where clause to
DT.Date between '2000-06-01' and '2000-08-01'
it works fine and gets the result in 10 seconds.

If i select some other dates then the range has to be more than 3 months .

Can anyone please explain to me the reason why the query is behaving like this or am i missing something here.

I would appreciate any help regarding this problem

Thanks in advance

sudhir

 

[dbmsguy]

1. what indexes do you have specifed on the table?
2. are your statistics up-to-date?

Paul

http://www.dbmsguy.com

 

[dsudhir]

I have indexes on the Id and Date field and the statistics are up-to-date.

If i tried Month(date) between 6 and 7 then the query works fines and i dont have any problems, i am facing this problem only when i use th exact date range like
Date between 2000-06-01 and 2000-07-01

sudhir

 

[dbmsguy]

in the end, you have an optimizer issue here. for what ever reason, it would look at least that he is probably doing a table scan....

i would suggest you

1. consider (and I do mean consider) making the date field a cluster (if not already)
2. compare the access plan to see what each query is using
3. try forcing the index to the one with date in it. you can do this with:

Select
Date,Pressure
from
SystemName SN,
WellName WN,
DailyTB DT (index idx_name)
where
SN.SID = WN.SID and
WN.ID = DT.ID and
DT.Date between '2000-06-01' and '2000-07-01'


see what results you get. there are possibly bugs in the optimizer that is causing it to do a scan. I do not recomend putting a query in production with an index hint, but sometimes it's the only way.

4. if you are running 11.9.2 or better, you can turn on the 302 trace flag and see exactly what the optimizer is doing in both cases.

Hope some of these ideas help resolve your problem

Paul

http://www.dbmsguy.com

 

[dbmsguy]

sorry, before using the 302 flag, you should probably do a showplan on both queries. Paul

http://www.dbmsguy.com

 

[dsudhir]

Thanks dbmsguy,

I am now using the Month(Date) and it is working fine.

sudhir