σ
p
1
∧
p
2
(R)
=
σ
p
1
(
σ
p
2
(R)
)
=
σ
p
2
(
σ
p
1
(R)
)
σ
p
1
∨
p
2
(R)
=
(
σ
p
1
(
R
)
)
∪
set
(
σ
p
2
(
R
)
)
16 / 106

Algebraic Laws: Select
Push selections through the binary operators: product, union,
intersection, difference, and join.
if pushing select, select
. . .
1.
must be pushed through both arguments for union:
Union:
σ
p
(R
∪
S)
=
σ
p
(R)
∪
σ
p
(S)
2.
must be pushed through first arguments, optionally second for difference:
Difference:
σ
p
(R - S)
=
σ
p
(R) - S
σ
p
(R - S)
=
σ
p
(R) -
σ
p
(S)
3.
For the other operators it is only required that the selection be pushed to
one argument.
product, natural join, theta join, intersection
e.g.,
σ
p
(R
×
S)
=
σ
p
(R)
×
S
, if
p
contains only attributes from
R
17 / 106

Algebraic Laws: Select
If the condition
p
in
σ
p
(R
∩
S)
is compound (
p = p
1
and p
2
), to split
p
up, we can use:
σ
p
1
∧
p
2
(R)
=
σ
p
1
(
σ
p
2
(R)
)
=
σ
p
2
(
σ
p
1
(R)
)
Example
R(a,b)
S(c,d)
σ
a
=3
∧
c
=4
(
R
∩
S
) =
σ
a
=3
(
σ
c
=4
(
R
∩
S
)
)
=
σ
a
=3
(
R
∩
σ
c
=4
(
S
)
)
=
σ
a
=3
(
R
)
∩
σ
c
=4
(
S
)
18 / 106

Algebraic Laws: Select
If the selection condition
p
involves only the attributes of
R
and
q
involves
the attributes of
S
, then the select operation distributes.
σ
p
∧
q
(R
1
S)
=
σ
p
(R)
1
σ
q
(S)
Let
p
= predicate with only
R
attributes
q
= predicate with only
S
attributes
m
= predicate with
R,S
attributes
σ
p
(R
1
S)
=
σ
p
(R)
1
S
σ
q
(R
1
S)
=
R
1
σ
q
(S)
Some Rules can be Derived:
σ
p
∧
q
(R
1
S)
=
σ
p
(R)
1
σ
q
(S)
σ
p
∧
q
∧
m
(R
1
S)
=
σ
m
(
σ
p
(R)
1
σ
q
(S)
)
Derivation for first one
σ
p
∧
q
(
R
1
S
) =
σ
p
(
σ
q
(
R
1
S
)
)
=
σ
p
(
R
1
σ
q
(
S
)
)
=
σ
p
(
R
)
1
σ
q
(
S
)
19 / 106

Algebraic Laws: Select: Pushing Selections
Example
Employee(fname, salary, dno)
Dept(dname, dno)
σ
dname
=
Research
(
Employee
1
Dept
) =
Employee
1
σ
dname
=
Research
(
Dept
)
“
Pushing down
” a selection (
σ
) will result in a smaller intermediate result
set
σ
dname= Research
1
Employee
Dept
⇒
1
Employee
σ
dname= Research
Dept
20 / 106

Algebraic Laws: Select: Pushing Selections
Example
Employee(fname, salary, dno)
Dept(dname, dno)
σ
dname
=
Research
∧
fname
=
John
(Employee
1
Dept)
=
σ
fname
=
John
(
σ
dname
=
Research
(Employee
1
Dept)
)
=
σ
fname
=
John
(
Employee
1
σ
dname
=
Research
(Dept)
)
=
σ
fname
=
John
(Employee)
1
σ
dname
=
Research
(Dept)
21 / 106

Common used query optimization technique involving
σ
Simple query optimization
The running time of database operations depends on:
The size of the input relations (operands)
Therefore: It is always beneficial (for running time) to reduce the size of
the input relation(s)
22 / 106

Reducing the size of input relation using
σ
The selection operator
σ
can reduce the size of the input relation of some
operators
Example
π
movieTitle
σ
birthdate LIKE
%1960
1
starName=name
StarsIn
MovieStar
Input relations
π
movieTitle
1
starName=name
σ
birthdate LIKE
%1960
StarsIn
MovieStar
Input relations
The input relation of
1
in the second case
σ
birthday LIKE
%1960
(StarsIn)
can be much smaller than
the input relation
StarsIn
23 / 106

Simple query optimization technique: “push select
down”
One of the many query optimization techniques used by the DBMS is
execute a
σ
p
as soon as possible.

#### You've reached the end of your free preview.

Want to read all 106 pages?

- Fall '19
- Joseph Rosen
- Relational model, StarsIn, R 1c S