Slideshow:
Repeat:
the index-join algorithm
Pseudo code:
while ( R ≠ EOF )
{
read next block of R in buffer b;
for ( each tuple t ∈ b ) do
{
(1) Use t(Y) to lookup in index Y of relation S;
|
Note: the index can be an ordered index or a hash index !!!
The performance of
the index-join algorithm using
a non-clustering index
What is the IO cost to Compute R ⋈ S when:
The performance of
the index-join algorithm using
a non-clustering index
What is the IO cost to Compute R ⋈ S when:
The join algorithm will scan the relation R once:
IO cost so far: B(R) (or: T(R) if R is not clustered)
The performance of
the index-join algorithm using
a non-clustering index
What is the IO cost to Compute R ⋈ S when:
For
each tuple
t ∈ R,
we read T(S)/V(S,Y) blocks of
relation S
(and
join (⋈)
with tuple t):
(We assume average 1 tuple in a block)
IO cost so far: B(R) + T(R) × T(S)/V(S,Y)
The performance of
the index-join algorithm using
a non-clustering index
(The cost to scan relation R depends on whether R is clustered or unclustered)
- Requirement:
R = (X, Y) S = (Y, Z) S has a non-clustering index on Y
- Algorithm for
R ⋈ S using index
S.Y:
while ( R ≠ EOF ) { read next block of R in buffer b:
for ( each tuple t ∈ b ) do
{
(1) Use t(Y) to lookup in index Y of S:
(2a) read all tuples s with s(Y)=t(Y);
(2b) output t ⋈ s:
}
}
- The join algorithm
will scan the
relation R
once:
- For each tuple
t ∈ R,
we access
following T(S)/B(V,Y) tuples in
relation S
to perform the
join (⋈) operation
with tuple t:
Assuming that different tuples s are stored in different blocks, we will access:
T(S) # blocks of S accessed per tuple t ∈ R = -------- blocks V(S,Y)
- # Disk IO used in
join algorithm using
a NON-clustering index:
# disk IOs = Scan R (once + # tuples in R × # blocks of S read per tuple of R
If relation R is clustered: T(S) = B(R) + T(R) × -------- // Scan cost for clustered file R = B(R) V(S,Y) If relation R is non-clustered: T(S) = T(R) + T(R) × -------- // Scan cost for non-clustered file R = T(R) V(S,Y)