Are common table expressions (aka temporary views) optimized like regular views?

[VolkerBarth]

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

• make clear they are only locally used and
• prevent to "pollute" the system catalog.

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?

[Breck_Carter]

WAG: The annotation phase described here flattens out all views and view-like things... if not that phase, then the semantic transformation phase.

[VolkerBarth]

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!":)

[Former Member · Accepted solution]

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 (for the uninitiated) "What the heck is a Recursive query?" ... since CTEs are the gateway feature to those.

Incidentally (again mostly for the uninitiated and not the 2 regular contributors on this thread) you can see what view expansions are taking place by reviewing the query using the rewrite() function. And for those more familiar with compilers etc., in SQL Anywhere views (temporary or permanent) are more like macros than virtual tables; the latter may imply some degree of pre-compilation/pre-optimization and extra metadata on some RDBMSs. This is possibly the easiest way to understand "view expansion". Views "... are derived each time they are used."

[VolkerBarth · Accepted solution]

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:)

[VolkerBarth]

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:

SELECT * FROM EmployeesAndDepartments WHERE DepartmentName = 'Sales' ORDER BY 1, 2

The REWRITE shows that the view is expanded ("inlined" would be a further fitting analogue, methinks):

select rewrite('SELECT * FROM EmployeesAndDepartments WHERE DepartmentName = ''Sales'' ORDER BY 1, 2');

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.