> 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
