>  This doesn't quite execute.  I cleaned it up to this:
>
> select * from (
> select a.User_ID, a.ADVOCATE_CODE
> from Advocate a
> where a.USER_ID=37
> ) as FILTER1,supprog sp
> Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code
>
> It gets the same plan: "PLAN JOIN (SP NATURAL, FILTER1 A INDEX
> (ADVOCATE_))".  But it has another problem, too:  ultimately I'm wanting to
> use this as part of a view, so I can't do my filtering inside the query.

Do not use SQL 87 JOIN syntax:

) as FILTER1,supprog sp

It is very lazy and leave the relationships between the tables too undefined.

Always use explicit JOIN.

Like this:

select sp.STUDENTSEQ, Filter1.User_ID
from (
select a.User_ID, a.ADVOCATE_CODE
from Advocate a
where a.USER_ID=37
) as FILTER1
JOIN supprog sp ON sp.ADVOCATE_CODE=FILTER1.Advocate_Code


Questions:

1- What indexes do you have defined on a.User_ID?

2- What is the selectivity (aka uniqueness) of User_ID and Advocate_Code?

3- Is searching by User_ID and Advocate_Code a common query?


Sean