Well I have to write sql like that because I must have one table in
from. Some component I have
depend on this. This has worked fast before but I have workaround so is
not a problem.
Regards Radovan
On 06.12.2019 11:35, Keith Medcalf wrote:
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 ... ?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
