If the aggregate function is min or max, duplicate
A
values do not have
any effect. However, there should be no duplicates on (
A
,
B
); the first
expression removes such duplicates, while the second does not.
13.9
Consider the issue of interesting orders in optimization. Suppose you are
given a query that computes the natural join of a set of relations
S
. Given
a subset
S
1 of
S
, what are the interesting orders of
S
1?
Answer:
The interesting orders are all orders on subsets of attributes that
can potentially participate in join conditions in further joins. Thus, let
T
be the set of all attributes of
S
1 that also occur in any relation in
S
−
S
1.
Then every ordering of every subset of
T
is an interesting order.
13.10
Show that, with
n
relations, there are (2(
n
−
1))!
/
(
n
−
1)! different join
orders.
Hint:
A
complete binary tree
is one where every internal node has
exactly two children. Use the fact that the number of different complete
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Exercises
7
binary trees with
n
leaf nodes is:
1
n
parenleftbigg
2(
n
−
1)
(
n
−
1)
parenrightbigg
If you wish, you can derive the formula for the number of complete binary
trees with
n
nodes from the formula for the number of binary trees with
n
nodes. The number of binary trees with
n
nodes is:
1
n
+
1
parenleftbigg
2
n
n
parenrightbigg
This number is known as the
Catalan number
, and its derivation can be
found in any standard textbook on data structures or algorithms.
Answer:
Each join order is a complete binary tree (every nonleaf node
has exactly two children) with the relations as the leaves. The number
of different complete binary trees with
n
leaf nodes is
1
n
(
2(
n
−
1)
(
n
−
1)
)
. This is
because there is a bijection between the number of complete binary trees
with
n
leaves and number of binary trees with
n
−
1 nodes. Any complete
binary tree with
n
leaves has
n
−
1 internal nodes. Removing all the leaf
nodes, we get a binary tree with
n
−
1 nodes. Conversely, given any binary
tree with
n
−
1 nodes, it can be converted to a complete binary tree by
adding
n
leaves in a unique way. The number of binary trees with
n
−
1
nodes is given by
1
n
(
2(
n
−
1)
(
n
−
1)
)
, known as the Catalan number. Multiplying this
by
n
! for the number of permutations of the
n
leaves, we get the desired
result.
13.11
Show that the lowestcost join order can be computed in time
O
(3
n
).
Assume that you can store and look up information about a set of relations
(such as the optimal join order for the set, and the cost of that join order)
in constant time. (If you find this exercise difficult, at least show the looser
time bound of
O
(2
2
n
).)
Answer:
Consider the dynamic programming algorithm given in Sec
tion 13.4. For each subset having
k
+
1 relations, the optimal join order
can be computed in time 2
k
+
1
. That is because for one particular pair of
subsets
A
and
B
, we need constant time and there are at most 2
k
+
1
−
2
different subsets that
A
can denote. Thus, over all the
(
n
k
+
1
)
subsets of size
k
+
1, this cost is
(
n
k
+
1
)
2
k
+
1
. Summing over all
k
from 1 to
n
−
1 gives the
binomial expansion of ((1
+
x
)
n
−
x
) with
x
=
2. Thus the total cost is less
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '13
 Dr.Khansari
 right hand

Click to edit the document details