AFAIK, a query that references a regular (i.e. non-materialized) view will replace that view's appearance with its definition early in the query processing phase (as "view expansion" in the so-called annotation phase, as documented here). When using a (non-recursive) common table expression (CTE) within a query, is it treated the same way as a regular view when both have the same definition? In other words: Say, I want to simplify a complex query by using a divide-and-conquer approach and are going to put some aspects within separate "views". Now, in case I would expect that those views are not generally useful outside that query, then I would personally prefer to code them as CTEs and not as regular views in order to
So when I have to choose between a regular view and a CTE can I expect that both are as good in terms of optimization, or does the optimizer "know" more about regular views? |
kibitzer comment . . . hopefully helpful for other readers of this posting Maybe a simpler way to see this is to substitute an equivalent name for a CTE, which is a "temporary view". That way the only surprises should be the next question ( Incidentally So that is a "Yes, they are optimized identically", right? I tend to forget the rewrite() function as a tool to catch a glimpse of the optimizer's actions... - thanks for the pointer:)
(08 Apr '15, 11:47)
Volker Barth
|
Nick, I have tried to verify the (nearly) identical optimization of regular views and CTEs with the help of the rewrite() function, as you have suggested. Witin the 12.0.1 sample database, I have created the following sample view (taken from the docs on the CREATE VIEW statement): CREATE VIEW EmployeesAndDepartments AS SELECT Surname, GivenName, DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID Now I have made a simple SELECT based on that view:
The REWRITE shows that the view is expanded ("inlined" would be a further fitting analogue, methinks):
returns select Employees_1.Surname,Employees_1.GivenName,Departments_1.DepartmentName from GROUPO.Employees as Employees_1,GROUPO.Departments as Departments_1 where Employees_1.DepartmentID = Departments_1.DepartmentID and Departments_1.DepartmentName = 'Sales' order by 1 asc,2 asc In contrast, when using the view definition in a similar temporary view / CTE: WITH CTE_EmployeesAndDepartments AS (SELECT Surname, GivenName, DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID) SELECT * FROM CTE_EmployeesAndDepartments WHERE DepartmentName = 'Sales' ORDER BY 1, 2 the rewrite select rewrite('WITH CTE_EmployeesAndDepartments AS (SELECT Surname, GivenName, DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID) SELECT * FROM CTE_EmployeesAndDepartments WHERE DepartmentName = ''Sales'' ORDER BY 1, 2'); returns a somewhat different result, still containing the CTE reference (though the SELECT itself seems to have "inlined" the CTE definition like with the view definition): with CTE_EmployeesAndDepartments as(select Surname,GivenName,DepartmentName from Employees join Departments on Employees.DepartmentID = Departments.DepartmentID) select Employees_1.Surname,Employees_1.GivenName,Departments_1.DepartmentName from Employees as Employees_1,Departments as Departments_1 where Employees_1.DepartmentID = Departments_1.DepartmentID and Departments_1.DepartmentName = 'Sales' order by 1 asc,2 asc Therefore I'm not quite sure if the rewrite() really helps here to understand the likeness of temporary vs. regular view expansion... - But given the fact in both cases the view definition is expanded, that should do for me. |
WAG: The annotation phase described here flattens out all views and view-like things... if not that phase, then the semantic transformation phase.
Yep, that's the exact link I had mentioned in my post, so we surely share that WAG:)
FWIW, Ani's white paper on Query Processing does also mention view expansion but does not explicitly tell about CTEs (and does not really explain view expansion - at least that's my impression - that content is beyond my cognitive abilities (*)):
Query Processing Based on SQL Anywhere 12.0.1 Architecture
(*) Therefore it would not come as a surprise if the general answer to my title question would be "Of course!":)