Em 13/5/2014 17:14, Kevin Donn kd...@msedd.com [firebird-support] escreveu:
Try this:
select * from
(
select a.User_ID
from Advocate
where a.USER_ID=37
) as FILTER1, supprog sp
Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code
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.
Why isn't Firebird using the index? Do the index statistics have
anything to do with it?
Are the statistcs up to date ?
The two tables you mentioned has the same amount of records ? The
statistics are quite diferent, so, or the number of rows are bery
diferent or the index statistics are out of date.
Try this one:
select sp.STUDENTSEQ, a.User_ID
from supprog sp
join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code+0
where a.USER_ID=37
see you !
