Slideshow:
Review: V(R,A)
Database systems store
statistics on the
relations
V(R,A)
(where A is an
"important" attribute) are
some of the
statistics stored
Processing a selection
(σA=c(R))
using
a clustering index
How to process σA=c (R) using a clustering index on attribute A:
IO cost of
selection
(σA=c(R))
using
a clustering index
-
reading the
index file
# disk IOs to read the index file:
Comment: the internal nodes of a B+ tree can usually be stored in memory
IO cost of
selection
(σA=c(R))
using
a clustering index
-
reading the
data file
# disk IOs to read the data file:
How many disk blocks do we need to read ???
IO cost of
selection
(σA=c(R))
using
a clustering index
-
reading the
data file
Estimating the # disk IOs needed to read the data file:
# disk block with tuples A=c ~= B(R)/V(R,A) blocks
IO cost of
selection
(σA=c(R))
using
a clustering index
IO cost of
selection
(σA=c(R))
using
a clustering index
-
special case:
key attribute
- Recall: V(R, A)
- V(R,a) =
# distinct values
a1,
a2, ...
ak found
in attribute A in
the relation R
V(R,a) = | πA(R) |
Example:
R(A,B): { (a1, b5), (a1, b1), (a2, b3) } V(R,A) = 2 V(R,B) = 3
- V(R,a) =
# distinct values
a1,
a2, ...
ak found
in attribute A in
the relation R
- If index A on
R is a
clustering index,
we process
σA=c(R)as follows:
- Lookup
the key c in the
index file
- The lookup process will return the first record location containing the attribute value c
- Scan the relation (data file) for all records with attribute value = a
- Lookup
the key c in the
index file
- Graphically:
- # disk IOs:
- # disk IOs to
read the
index file:
- Because the index file is much smaller than the data file, we can ignore the disk IOs used to access the index
# disk IOs to read the index file = negligible
- # disk IOs to
read the
data (relation) file:
- We access
all records with
one specific value
of attribute value c:
- There are B(R) number of
blocks in R:
- There are V(R, A)
different attribute values
So there are V(R, A) number of segments in R:
Assuming a uniform distribution of attribute value (= each attibute value appears the same number of times):
Total # block of R = B(R) # different "segments" = V(R,A) 1 # blocks per "segment" = ------ B(R) V(R(A)
Therefore: 1 Total # blocks in R with (A=c) = ------ B(R) V(R,A)
- We access
all records with
one specific value
of attribute value c:
- # disk IOs to
read the
index file:
- Summary:
Clustering index: 1 # blocks accessed in R with (A=c) = ------ B(R) V(R,A)(plus a negligible number of disk IOs for accessing the index file)
- Note:
- The cost to execute
σA=c(R)
without an
index is:
Cost of σA=c(R) without index = B(R) Blocksbecause we must scan the entire relation !!!
- The cost to execute
σA=c(R)
without an
index is:
- Special case: index on a
key attribute
- If A is a
key of the
relation R, then:
# Disk I/O = 1 block !!!because there is only 1 tuple with that specific attribute value !!!
- If A is a
key of the
relation R, then: