True, but an optimizer could only intelligently exclude OUTER JOINS in most
cases.
For instance, if I have a convenience view that combines a series of tables to
provide an overall list of something or another:
View ->
SELECT ...
FROM a
INNER JOIN b ON ...
INNER JOIN c ON ...
INNER JOIN d ON ...
As the schema designer, I might know that all three joins will always, ALWAYS
work so that if I use that view and only look at fields in table a, then don't
need those joins. However, since SQLite can't really know that, it always has
to perform all three joins regardless of which fields are returned.
Also, unless you do rewrite the query, how about this view?
SELECT ...
FROM a
LEFT OUTER JOIN b ON a.c1 = b.c1
LEFT OUTER JOIN c ON b.c1 = c.c1
Let's say I only request columns from tables a and c. As it turns out, since
I'm using the same linkage columns (c1), the query can be optimized to:
SELECT ...
FROM a
LEFT OUTER JOIN c on a.c1 = c.c1
This was just a simple example. I can only imagine how much work it would take
to handle all the possible variations.
As this is a lite database, I would much prefer optimization to worry about
making the joins I specify as fast as possible and let me worry about the joins
in the query. If you don't control how your views are used (as in the Ops
example), then the view is truly a convenience function and, as with most
conveniences, has a cost.
I could, however, imagine a module that compiled precompile views (or even
queries) and generate multiple plans based on combinations of desired output
fields. Those could then be stored or something for quick lookup. Sort of
like preparing and storing the result for later use.
-----Original Message-----
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Nico Williams
Sent: Thursday, May 31, 2012 12:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins?
On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman <donts...@home.nl> wrote:
>> Actually, anytime you have VIEWs that join table sources you can
>> benefit from this optimization.
>
> No, there is only a benefit if the optimization actually finds a table
> that can be removed from the join.
When you create a VIEW from a JOIN you have no idea if queries on that VIEW
will need the JOIN.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
