Hi all,

I have a query that executes with adequate speed, but as soon as I add
an and to the where clause, things go down hill real fast...

Query is simplified as below:

Select
Table2.Code,
Table1.Code,
Table1.Name
from A_Table1 Table1
inner join A_Table2 Table2 on Table1.ForeignKey2 = Table2.PrimaryKey
inner join A_Table3 Table3 on Table1.ForeignKey3 = Table3.PrimaryKey
inner join A_Table4 Table4 on Table3.ForeignKey2 = Table4.PrimaryKey
where
((Table1.ForeignKey2 = 962) and (Table1.Status = 8) and
(Table4.ForeignKey1 = 170))

Now this plan uses no Natural scans, does a couple thousand indexed
reads and returns fairly quick...

But what I want is:
Where (((Table1.ForeignKey2 >= 962) or (Table1.ForiegnKey2 <= 973))
and (Table1.Status = 8) and (Table4.ForeignKey1 = 170))

As soon as I add this it puts in a Natural Scan and takes hours....

I have tried various combninations of where clauses, using IN, using
lots of Ors instead of the > and < but nothing seems to work. Running
the query manually 12 times, changing the where clause each time takes a
lot less time than adding to the where clause (including me doing the
editing!).

I have seen this same problem several times and have not come up with
the answer... The Optimiser doesn't seem to be optimising...

If anyone could point me in the right direction, I would be greatly
appreciative.

Regards
Colin


######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################