Tried to send this yesterday, but since it hasn't appeared yet - I suppose I
failed.
---
Hm, no-one answering you Andrew? Maybe you are too demanding - after all,
before computers were invented a clerk being able to get that much
information from four separate archives of that size averaging six seconds
per order, would be considered pretty good!

Seriously, I saw your message at work, but thought others more used to views
than I am (I hardly ever need them) would answer.

You do not want to write plans. Nevertheless, viewing the plans is essential
for problems like yours in order to try to understand the optimizer.

You don't use anything limiting the returned rows - all that is in your
where clause is join criteria (by the way, you ought to use explicit join
rather than mingling in the where clause - try this first just in case it
should make any difference - you don't have to execute anything, just
prepare your statement and watch the plan). This means that at least one
table has to be processed in natural order. Common sense (not always
optimizer sense) says that this ought to be the Reserves table since it is
the smallest. My guess (it really is a guess since I don't use views myself)
is that it rather starts by evaluating the view going through the OrderItems
in natural order (in theory, the view could have a where clause reducing the
number of returned rows)! When doing the subselect it seems like it speeds
up, so it can change its plan if it believes it to be benefitial.

Basically, what you have to do is to be clearer as to what you want it to
scan in a natural order. I suggest you do this by removing the primary key
for the Reserves table. You can enforce uniqueness through triggers if you
want to, but make sure there is no index or primary key that can be used for
the Reserves table in your select. My guess (again) is that the optimizer
then will change the plan so that it goes through the Reserves table in
natural order, whilst using indexes for all the other tables.

I'm not completely certain this is what the optimizer will do, but I'm
positive that it what you want it to do.

Feedback please if it works - information about plans if it doesn't,
Set
-using Atkin at home for the first time
(and from the look of it, being bitten by the reply mechanism)