searchspaceanalyzer n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SearchSpaceAnalyzer PowerPoint Presentation
Download Presentation
SearchSpaceAnalyzer

Loading in 2 Seconds...

play fullscreen
1 / 8

SearchSpaceAnalyzer - PowerPoint PPT Presentation


  • 95 Views
  • Uploaded on

SearchSpaceAnalyzer. Anisoara Nica , Daniel S. Brotherston , David W. Hillis “ Extreme Visualization of Query Optimizer Search Spaces ”, ACM SIGMOD International Conference on Management of Data, SIGMOD 2009, 1067-1070.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SearchSpaceAnalyzer' - caden


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
searchspaceanalyzer
SearchSpaceAnalyzer

AnisoaraNica, Daniel S. Brotherston, David W. Hillis “Extreme Visualization of Query Optimizer Search Spaces”,

ACM SIGMOD International Conference on Management of Data, SIGMOD 2009, 1067-1070

slide2

AnisoaraNica, Daniel S. Brotherston, David W. Hillis “Extreme Visualization of Query Optimizer Search Spaces”,

ACM SIGMOD International Conference on Management of Data, SIGMOD 2009, 1067-1070

generated queries object relational mapping orm
Generated Queries: Object Relational Mapping (ORM)

Q1 = SELECT

[Project9].[ID] AS [ID],[Project9].[C1] AS [C1],[Project9].[C2] AS [C2],[Project9].[ID1] AS [ID1],[Project9].[SalesOrderID] AS [SalesOrderID],

[Project9].[TotalDue] AS [TotalDue]

FROM ( SELECT         [Distinct1].[ID] AS [ID],        1 AS [C1],        [Project8].[ID] AS [ID1],        [Project8].[SalesOrderID] AS [SalesOrderID],

        [Project8].[TotalDue] AS [TotalDue],        [Project8].[C1] AS [C2]

        FROM 

(SELECT DISTINCT   [Extent1].[ID] AS [ID]

            FROM  [DBA].[Person] AS [Extent1]

                INNER JOIN [DBA].[Sales] 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].[ID] AS [ID]

                                FROM [DBA].[Person] AS [Extent3]  WHERE [Extent2].[ID] = [Extent3].[ID] )AS [Project1] ON cast(1 as bit) = cast(1 as bit)

                        LEFT OUTER JOIN  (SELECT  [Extent4].[ID] AS [ID]

                                FROM [DBA].[Person] AS [Extent4]  WHERE [Extent2].[ID] = [Extent4].[ID] ) AS [Project2] ON cast(1 as bit) = cast(1 as bit)

WHERE ([Extent1].[ID] = [Project1].[ID]) OR (([Extent1].[ID] IS NULL) AND ([Project2].[ID] IS NULL))  )

) AS [Distinct1]

LEFT OUTER JOIN 

(SELECT [Extent5].[ID] AS [ID],  [Extent6].[SalesOrderID] AS [SalesOrderID], [Extent6].[TotalDue] AS [TotalDue],   1 AS [C1]

                FROM  [DBA].[Person] AS [Extent5]

           INNER JOIN [DBA].[Sales] 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 [Extent9].[ID] AS [ID]

                                FROM [DBA].[Person] AS [Extent9]  WHERE [Extent6].[ID] = [Extent9].[ID] )AS [Project5] ON cast(1 as bit) = cast(1 as bit)

                        LEFT OUTER JOIN  (SELECT  [Extent8].[ID] AS [ID]

                                FROM [DBA].[Person] AS [Extent8]  WHERE [Extent6].[ID] = [Extent8].[ID] )AS [Project6] ON cast(1 as bit) = cast(1 as bit)

WHERE ([Extent5].[ID] = [Project5].[ID]) OR (([Extent5].[ID] IS NULL) AND ([Project6].[ID] IS NULL))

                )

) AS [Project8]

ON ([Project8].[ID] = [Distinct1].[ID]) OR (([Project8].[ID] IS NULL) AND ([Distinct1].[ID] IS NULL))

)  AS [Project9]

ORDER BY [Project9].[ID] ASC, [Project9].[C2] ASC

slide4

Generated Queries: ORM (cont.)

W = SELECT DISTINCT   [Extent1].[ID] AS [ID]    

            FROM  [DBA].[Person] AS [Extent1]

                INNER JOIN [DBA].[Sales] 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].[ID] AS [ID]

                                FROM [DBA].[Person] AS [Extent3]  WHERE [Extent2].[ID] = [Extent3].[ID] )AS [Project1] ON cast(1 as bit) = cast(1 as bit)

                        LEFT OUTER JOIN  (SELECT  [Extent4].[ID] AS [ID]

                                FROM [DBA].[Person] AS [Extent4]  WHERE [Extent2].[ID] = [Extent4].[ID] ) AS [Project2] ON cast(1 as bit) = cast(1 as bit)

WHERE ([Extent1].[ID] = [Project1].[ID]) OR (([Extent1].[ID] IS NULL) AND ([Project2].[ID] IS NULL))  )

Q1 = SELECT

[Project9].[ID] AS [ID],[Project9].[C1] AS [C1],[Project9].[C2] AS [C2],[Project9].[ID1] AS [ID1],[Project9].[SalesOrderID] AS [SalesOrderID],[Project9].[TotalDue] AS [TotalDue]

FROM ( SELECT         [Distinct1].[ID] AS [ID],        1 AS [C1],        [Project8].[ID] AS [ID1],        [Project8].[SalesOrderID] AS [SalesOrderID],

        [Project8].[TotalDue] AS [TotalDue],        [Project8].[C1] AS [C2]

        FROM   (

W

) AS [Distinct1]

  LEFT OUTER JOIN 

( W

) AS [Project8]

ON ([Project8].[ID] = [Distinct1].[ID]) OR (([Project8].[ID] IS NULL) AND ([Distinct1].[ID] IS NULL))

)  AS [Project9]

ORDER BY [Project9].[ID] ASC, [Project9].[C2] ASC

slide5

Generated Queries: ORM (cont.)

Q1 = selectExtent6.ID as,

1 as C1,

1 as C2,

Extent6.ID as ID1,

Extent6.SalesOrderID as SalesOrderID,

Extent6.TotalDue as TotalDue

from DBA.Sales as Extent6

order by Extent6.ID as ID asc

generated queries hibernate
Generated Queries: Hibernate

Q2 = SELECT DISTINCT Device.ID,Device.Name

FROM Device

WHERE Device.ID IN

(SELECT DISTINCT Device.ID

FROM Device

WHERE Device.ID IN

(SELECT DISTINCT Device.ID

FROM Device

WHERE Device.ID IN

(SELECT DISTINCT Device.ID

FROM Device

WHERE Device.ID IN

(SELECT DISTINCT Device.ID

FROM Device

WHERE Device.ID IN

(SELECT DISTINCT Device.ID

FROM Device

WHERE Device.ID IN

(SELECT DISTINCT Device.ID

FROM Device

WHERE Device.ID IN (SELECT Device.ID

FROM Device,Entry

WHERE Device.dbId = Entry.dd

AND UPPER(Entry.ss) LIKE '%192.16.%')

OR Device.ID IN ( ( SELECT ID FROM Device

WHERE ( UPPER(Device.sName) LIKE '%SSMC%' ) ) ) )

OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.sName) LIKE '%SSTB%' ) ) ) )

OR Device.ID IN ( ( SELECT dcrDeviceId FROM Device WHERE ( UPPER(Device.sName) LIKE '%XHFH%' ) ) ) )

OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.Name) LIKE '%SSTB%' ) ) ) )

OR Device.ID IN ( ( SELECT ID FROM Device WHERE ( UPPER(Device.Name) LIKE '%XHFH%' ) ) ) )

OR Device.ID IN ( ( SELECT Device.ID FROM Device,Entry WHERE ( ( Device.dbId = Entry.dd ) ) AND ( UPPER(Entry.ss) LIKE '%129.196.9.%' ) ) ) )

generated queries hibernate cont
Generated Queries: Hibernate (cont.)

sq(c0,c1): select DISTINCT T.X from T

where T.X IN (select T.X

from T JOIN R where R.Y like c0)

or T.X IN (select T.X from T where T.Y like c1)

sq(co,c1,…,cn): select DISTINCT T.X from T

where T.X IN ( sub(c0,c1,..,c(n-1)) )

or T.X IN (select T.X from T where T.Y like cn)

sq('%192.16.%‘, '%SSMC%, '%SSTB%‘, '%XHFH%, '%SSTB%‘, '%XHFH%‘, '%129.196.9.%‘)

generated queries hibernate cont1
Generated Queries: Hibernate (cont.)

Q2 = SELECT DISTINCT W0.ID,W0.Name

FROM Device W0

WHERE ( W0.ID IS NOT NULL and

( W0.ID IN

(SELECT W9.ID

FROM Device W9,Entry

WHERE W9.dbId = Entry.dd

AND UPPER(Entry.ss) LIKE '%192.16.%') )

OR ( UPPER(W0.sName) LIKE '%SSMC%' )

OR ( UPPER(W0.sName) LIKE '%SSTB%' )

OR ( UPPER(W0.sName) LIKE '%XHFH%' )

OR (UPPER(W0.Name) LIKE '%SSTB%' )

OR (UPPER(W0.Name) LIKE '%XHFH%' )

OR W0.ID IN ( ( SELECT W12.ID FROM Device W12,Entry WHERE W12.dbId = Entry.dd AND UPPER(Entry.ss) LIKE '%129.196.9.%' )