On Thu, Apr 7, 2016 at 2:27 PM, Gabor Boros gaborbo...@yahoo.com
[firebird-support] <firebird-support@yahoogroups.com> wrote:
> 2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl
> [firebird-support] írta:
> >
> >
> > For a join (inner join), the rows must exist in both tables, so both
> > tables need to be evaluated.
> >
>
> I understand this. Try describe my problem with other words.
> Is the VIEW an atomic (or compiled like a STORED PROCEDURE) thing?
>
For views, only the view definition is stored. When a query references a
view,
the view is compiled and optimized like any other query. If possible, it
is
integrated into the larger query.
> I have a VIEW with 4 fields (FIELD1..4), 1 come from the "FROM table"
> (FIELD1) and 3 from JOINs (FIELD2..4). If execute a SELECT FIELD1 FROM
> ... PLAN contains the JOINs. Why? If I write a SELECT manually not
> include JOINs if not needed for the resulted fields, because want
> maximum performance.
>
>
As Mark and others explained, both sides of a join must always be
evaluated, even if you reference only fields from one table. Consider
this case:
select d.DepartmentName from Departments d
join Employees e on e.DeptID = d.DeptID
Suppose there are no Employees at all, but 10 Departments. That
query should return no results because what was asked was to return
the DepartmentName of Departments with Employees.
Suppose you again have 10 Departments, but only 1 Employee and
that Employee has a DeptID that matches one of the Departments.
Then the query should return one DepartmentName.
Suppose you have 10 Departments and 10 Employees, but all the
Employees have the same DeptID. Then you should get the same
DepartmentName ten times.
You're thinking of a case where there is a (possibly unstated) referential
relationship between the table that you're getting fields from and the other
table in the view. The table you're getting data from is the referencing
table and the other is the referenced table. In that particular case, the
join doesn't matter, but there's no way that Firebird can know that.
Good luck,
Ann
