|Subject||Re: [firebird-support] VIEW optimization|
On Thu, Apr 7, 2016 at 2:27 PM, Gabor Boros gaborboros@... [firebird-support] <firstname.lastname@example.org> wrote:2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' mark@...
> 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 isintegrated 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 beevaluated, even if you reference only fields from one table. Considerthis case:select d.DepartmentName from Departments djoin Employees e on e.DeptID = d.DeptIDSuppose there are no Employees at all, but 10 Departments. Thatquery should return no results because what was asked was to returnthe DepartmentName of Departments with Employees.Suppose you again have 10 Departments, but only 1 Employee andthat 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 theEmployees have the same DeptID. Then you should get the sameDepartmentName ten times.You're thinking of a case where there is a (possibly unstated) referentialrelationship between the table that you're getting fields from and the othertable in the view. The table you're getting data from is the referencingtable and the other is the referenced table. In that particular case, thejoin doesn't matter, but there's no way that Firebird can know that.Good luck,Ann