Does the query optimizer perform any semantic transformations or other optimizations on simple regular views with the following characteristics?
|
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 2
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.
(11 Aug '11, 12:42)
Martin
That's pretty impressive. BTW what is the significance of the double alaising in the re-written query, eg:
(11 Aug '11, 13:28)
Justin Willey
Replies hidden
2
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.
(11 Aug '11, 14:21)
Nica _SAP
Replies hidden
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...)
(11 Aug '11, 17:00)
Volker Barth
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:)
(11 Aug '11, 17:18)
Volker Barth
2
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.
(11 Aug '11, 21:43)
Ivan T. Bowman
|
I always have thought that "optimizing (regular) views" would be identical to
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:) 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.
(05 Aug '11, 09:02)
Breck Carter
Replies hidden
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...
(05 Aug '11, 09:08)
Breck Carter
Replies hidden
...which may be explained by today's Friday File :)
(05 Aug '11, 09:10)
Breck Carter
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...
(05 Aug '11, 15:16)
Volker Barth
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:)
(09 Aug '11, 18:02)
Volker Barth
Replies hidden
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...
(11 Aug '11, 17:22)
Volker Barth
|