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.

asked 05 Aug '11, 04:59

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862
accept rate: 20%

edited 06 Aug '11, 17:14


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

permanent link

answered 11 Aug '11, 10:19

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

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:

1 as C1 as C1

(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
showing 4 of 6 show all flat view

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

permanent link

answered 05 Aug '11, 07:08

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 05 Aug '11, 15:14

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
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×19

question asked: 05 Aug '11, 04:59

question was seen: 1,000 times

last updated: 11 Aug '11, 21:43