Why Query Optimization? Access Path Selection in a - - PDF document

• Access Path Selection in a

Relational Database Management System

• P. Selinger, M. Astrahan, D. Chamberlin, R. Lorie, T. Price
• Why Query Optimization?
• How to come up with the right

query plan?

! " ! # $! % & '(

Simple Example

#)#*+,-."/010*23#-#/!4

1!/561!4*!

/4 14 *4 2(

− 78*078/0199 − 78/07810*99

But the search space is too big

:

;

'<

70

− <40=$00>0?@0@=0

AAA(

6

& 2A: BAA( !* C0!!0 A:: ( !"0AA %:AC :%B(

• *:1:"C

.B

& DE)61>FB: .B & D 'G".6FB:# .B & D 'G".6F6C/A

• :B:

.B"C**H

Use Statistics

%

#

,

* 5:::

• ,C:

H;A

: ':.

But*

%

#% *I;

*

Predicates

40J05.0!

• 3H K#%
• System R Optimizer

& /;

& & C:

& %

• System R Optimizer

& *

& H & % & *H'.

& *

• $

Interesting Orders

G-.H1L.-6#-1LC

:;::

*:::

• Back to the issue of search

space

• K:8<9

6::

Dynamic programming (Wikipedia)

& /K*

• &

%!

Dynamic programming

& /5C7;C7 ! 1C! ! %:C %! $! H :!

6

& 2MN & 2M ! *OP/-"6): 61P0A /*"%:!*A *O:A ;( !'C ! AB!1A %:A('0 AA:(

Major Contributions of Paper

*B

− − *HB80!9

6:: ':.

• =

Summary of the Approach

#

− − ':

#;

8 9

− 589 − ":8:9

*A%

• Example Schema

Example Query Example Initial Access Paths for single relations Example Search Tree

7::

2nd Relations Nested Loop

• 2nd Relations Merge Join

Prune and 3 Relations