How does the query optimizer handle simple regular views?

[Breck_Carter]

Does the query optimizer perform any semantic transformations or other optimizations on simple regular views with the following characteristics?

1. Provided to make ad-hoc queries a bit easier to write: a "legacy cleanup" effort.
2. One view per base table, no joins or WHERE clauses, not MATERIALIZED.
3. Some "legacy artifact" base table columns are omitted (not applicable, not reliable, not used, impossible to explain, purpose lost in time, etc).
4. Some base table columns are given different names.
5. The remaining base table columns are included "as is", perhaps in a different order.
6. No additional columns.

[Former Member · Accepted solution]

The SQL Anywhere Optimizer has a very extensive set of semantic transformations which are applied in a preoptimization phase, before the join enumeration process starts. The normal views and derived tables are treated exactly in the same way, hence a query using the normal views and a query using derived tables containing the definitions of the views are equivalent and will be optimized in the same way. An example of how extensive the semantic transformations are is given below - the SQL Anywhere Optimizer applied 7 semantic transformations for this query, some of them patented in 2008.

Original query:

SELECT [Project9].[ContactID] AS [ContactID],[Project9].[C1] AS [C1],[Project9].[C2] AS [C2],[Project9].[ContactID1] AS [ContactID1],[Project9].[SalesOrderID] AS [SalesOrderID], [Project9].[TotalDue] AS [TotalDue] FROM ( SELECT         [Distinct1].[ContactID] AS [ContactID],        1 AS [C1],        [Project8].[ContactID] AS [ContactID1],        [Project8].[SalesOrderID] AS [SalesOrderID],         [Project8].[TotalDue] AS [TotalDue],        [Project8].[C1] AS [C2]         FROM   (SELECT DISTINCT   [Extent1].[ContactID] AS [ContactID]                 FROM  [DBA].[Contact] AS [Extent1]                 INNER JOIN [DBA].[SalesOrderHeader] AS [Extent2] ON  EXISTS (SELECT   cast(1 as bit) AS [C1]                         FROM    ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]                         LEFT OUTER JOIN  (SELECT [Extent3].[ContactID] AS [ContactID]                                 FROM [DBA].[Contact] AS [Extent3]  WHERE [Extent2].[ContactID] = [Extent3].[ContactID] )AS [Project1] ON cast(1 as bit) = cast(1 as bit)                         LEFT OUTER JOIN  (SELECT  [Extent4].[ContactID] AS [ContactID]                                 FROM [DBA].[Contact] AS [Extent4]  WHERE [Extent2].[ContactID] = [Extent4].[ContactID] ) AS [Project2] ON cast(1 as bit) = cast(1 as bit)                         WHERE ([Extent1].[ContactID] = [Project1].[ContactID]) OR (([Extent1].[ContactID] IS NULL) AND ([Project2].[ContactID] IS NULL))  ) ) AS [Distinct1]   LEFT OUTER JOIN  (SELECT [Extent5].[ContactID] AS [ContactID],  [Extent6].[SalesOrderID] AS [SalesOrderID], [Extent6].[TotalDue] AS [TotalDue],   1 AS [C1]                 FROM  [DBA].[Contact] AS [Extent5]            INNER JOIN [DBA].[SalesOrderHeader] AS [Extent6] ON  EXISTS (SELECT    cast(1 as bit) AS [C1]                         FROM    ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]                         LEFT OUTER JOIN  (SELECT [Extent7].[ContactID] AS [ContactID]                                 FROM [DBA].[Contact] AS [Extent7]  WHERE [Extent6].[ContactID] = [Extent7].[ContactID] )AS [Project5] ON cast(1 as bit) = cast(1 as bit)                         LEFT OUTER JOIN  (SELECT  [Extent8].[ContactID] AS [ContactID]                                 FROM [DBA].[Contact] AS [Extent8]  WHERE [Extent6].[ContactID] = [Extent8].[ContactID] )AS [Project6] ON cast(1 as bit) = cast(1 as bit)   WHERE ([Extent5].[ContactID] = [Project5].[ContactID]) OR (([Extent5].[ContactID] IS NULL) AND ([Project6].[ContactID] IS NULL))                 ) ) AS [Project8] ON ([Project8].[ContactID] = [Distinct1].[ContactID]) OR (([Project8].[ContactID] IS NULL) AND ([Distinct1].[ContactID] IS NULL)) )  AS [Project9] ORDER BY [Project9].[ContactID] ASC, [Project9].[C2] ASC

After semantic transformations applied by the SQL Anywhere Optimizer:

select  Distinct1.ContactID as ContactID as ContactID,  1 as C1 as C1,  1 as C1 as C2 as C2, Extent6.ContactID as ContactID as ContactID1 as ContactID1,   Extent6.SalesOrderID as SalesOrderID as SalesOrderID as SalesOrderID, Extent6.TotalDue as TotalDue as TotalDue as TotalDue from(select distinct  Extent2.ContactID as ContactID      from DBA.SalesOrderHeader as Extent2) as Distinct1(ContactID)        left outer join DBA.SalesOrderHeader as Extent6,     on Extent6.ContactID as ContactID = Distinct1.ContactID order by Distinct1.ContactID as ContactID asc

[MCMartin · Accepted solution]

This is the reason why I am a fan of this product ;-) You can write lousy SQL but SQL Anywhere will fix this for you without complaining.

[justin_willey · Accepted solution]

That's pretty impressive.

BTW what is the significance of the double alaising in the re-written query, eg:

1 as C1 as C1

[Former Member · Accepted solution]

The transformed query string is obtained using 'select rewrite([original query])' statement. If you look at the original query, the column named [C1] in the most outer SELECT list is [Project9].[C1] AS [C1]

while the column [Project9].[C1] is 1 AS [C1]

Hence, there is no magic in how '1 as C1 as C1' was obtained by the semantic transformations.

[VolkerBarth · Accepted solution]

Aaaah, these human beings - allowed to write lousy SQL but still interested why the optimizer does it his way:)

(Yes, I was irritated/puzzled by the doubled alias, too...)

[VolkerBarth · Accepted solution]

I guess this excellent sample is taken from the some of the whitepapers on ORMs (like Hibernate), cf. the page from Glenn's blog - recommended reading:)

[Former Member · Accepted solution]

The REWRITE() function has a difficult job because the semantic transforms performed by the optimizer don't work on an SQL representation. In fact, in some cases the result of the transformations can not map exactly to SQL. The appearance of doubled aliases is one of the consequences of this fact.

[VolkerBarth]

I always have thought that "optimizing (regular) views" would be identical to

1. replace the view's name in the query with the according view definition
2. and optimize that as usual.

EDITED: The docs seem to describe this (in a rather general way) here (under Remote Access) and here (under Query processing phases).

But I would not be surprised if it's not that simple (and dumb?) like that:)

[Breck_Carter]

How hard did you have to search to find that doc topic?

FWIW, it is in a section that is talking about remote access, which is not what I'm asking about... but it's good to know. There should be a badge for Intrepid Explorer. 🙂

Your assumption is the same as my assumption... but lately I've been getting some big surprises when I check my assumptions.

[Breck_Carter]

Ahhh, but now we know the technical term is "view expansion"... too bad the docs refer to it in several places but give no definition...

[Breck_Carter]

...which may be explained by today's Friday File 🙂

[VolkerBarth]

No, I simply used the builtin search in DCX (as I remembered to have read something in the docs a while ago) ... but didn't check what topic this was filed under. - See my second link...

[VolkerBarth]

No clarification on my vague answer by the adored Sybase experts? - I'm absolutely sure there's much more to tell than I'm aware of:)

[VolkerBarth]

From Ani's answer I would conclude that my simple answer is still correct. After the view expansion is done, then the powerful semantic transformations can take place...