The join that you are using is not an outer join because you have constrained
R.ID_ARHDAJ (which cannot be null in the table) to not be null, and the only
way it can be null is if the left join is a "miss", meaning that it is really
an inner join, not an outer join). The optimizer spots this and does not force
the nesting order implied by the "left join" operator -- choosing instead to
process the query as an inner join. If the reason that you are using this
construction is specifically for the purpose of enforcing the nesting order
then you should be phrasing your query as such:
select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
from IZBPLA P
cross join ARHDAJ R
on R.ID_ARHPLA = P.ID_PLA
where P.ID_PLA < 1000000000;
so SQLite3 knows that you *always without exception* want R to be an inner loop
to P no matter what anyone (including the statistics say about it).
Also, now that you have run ANALYZE the information about the relative shape of
your data has been saved for the optimizer to use when optimizing queries. You
do not need to run ANALYZE again unless the shape significantly changes.
Generally though you would be better served to express the select as what it
really is, run ANALYZE from time to time, and let the optimizer do what it is
supposed to do, which is to get what you asked for as effeciently as possible.
select R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
from IZBPLA P
join ARHDAJ R
on R.ID_ARHPLA = P.ID_PLA
where P.ID_PLA < 1000000000;
Perhaps the optimizer could make a "left join" that is not actually an outer
join into a "cross join" and preserve the nesting order ... ?
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
