Slideshow:
Ordered clustering/non-clustering
indexes
An ordered clustering index (a1 < a2):
An ordered non-clustering index (a1 < a2):
Adapting the Sort-Join algorithm to a
ordered clustering index
We can access the tuples in sorted order using a (ordered) clustering index as follows:
1. Scan the
(ordered) index file
2. Access a
chunks of
tuples that contains
the next smallest join value
Adapting the Sort-Join algorithm to a
ordered non-clustering index
We can access the tuples in sorted order using a (ordered) non-clustering index as follows:
1. Scan the
(ordered) index file
2. Access the next
(one)
tuples that contains
the next smallest join value
The Zig-zag Join Algorithm
When can we use the Zig-zag Join Algorithm
(Assuming that
relations R and S are
not sorted !!!)
(If relations R and S are
sorted, we can
use the
(one-pass) Sort-Join algorithm !!)
- Recall: there are
2 basic types of
indices
(see:
click here)
-
Ordered indexes
- The search keys are stored in sorted order inside the index file
-
Hash indexes
- The search keys are stored in hash buckets.
-
Ordered indexes
- Fact:
-
Ordered (sorted) indexes
(such as the B+-tree)
can be used to
access tuples
of a relation in a
sorted order
- The data file does
not need to be
sorted
- We use the ordered index file to scan the data file in the sorted order!!!
- The data file does
not need to be
sorted
-
Ordered (sorted) indexes
(such as the B+-tree)
can be used to
access tuples
of a relation in a
sorted order
- Example:
B+-tree
Notice that:
- The (ordered) index file is
stored in the
leaf nodes of the
B+-tree
- We can use the record pointers in the (ordered) index file to access the data records in an sorted order
- The (ordered) index file is
stored in the
leaf nodes of the
B+-tree
- The ordered index can be:
- a clustering index:
Example: a primary index (see: click here)
- Or a non-clustering index:
- a clustering index:
- We can
access the
records in the
relation/file in a
sorted manner using
a ordered index:
- If the ordered index is
clustering,
we access the
records in
a
broad zig-zag pattern:
- If the ordered index is
non-clustering,
we access the
records in a
fine zig-zag pattern:
- If the ordered index is
clustering,
we access the
records in
a
broad zig-zag pattern:
- Zig-zag join algorithm:
- Zig-zag join =
a join algorithm
based on
the sort-join algorithm
where:
- We use an ordered index to access the joining tuples in a sorted order (without having to sort the input relations !!!)
- Zig-zag join =
a join algorithm
based on
the sort-join algorithm
where:
- Requirement
to use the zig-zag join algorithm
to compute
R(X,Y) ⋈Y=Y S(Y,Z):
- Relation R must have an ordered index on Y
- Relation S must have an ordered index on Y