If I do a search on a view and the where clause is against a field in
the leftmost stream, the plan uses indexes and is quick. If the where
clause is against an interior stream, the plan doesn't use the
associated index (for the primary key) of that interior stream and is
20x slower.

Do I need to rewrite the view? Right now the leftmost stream is for the
largest table, and the rightmost is for the smallest.

If this is answered in the book, you can just point me to it. Here's
the FROM clause. The WHERE criteria that's the problem is searching for
CLAIMAUDIT.ID=? (of course I actually use the name of the field as
exposed by the view).

FROM (CLAIMSPAIDREVERSED VC LEFT JOIN CLAIMAUDIT CA
ON (VC.RXCLAIMNBR = CA.RXCLAIMNBR
AND VC.CLMSEQNBR = CA.RXCLAIMSEQ
AND VC.CLAIMSTS = CA.CLAIMSTS))
LEFT JOIN CHCPATIENTTYPES PT
ON (VC.ACCOUNTID=PT.CHCCODE AND VC.GROUPID=PT.GROUP_ID)

Rick DeBay
Senior Software Developer
RxStrategies.net