Slideshow:
Assumption
when computing the performance of a hash index
I.e.: reading the block database addresses in the directory file entry requires no disk access
Example: the performance of a hash index
Example: the performance of a hash index
Example: the performance of a hash index
Example: the performance of a hash index
Example: the performance of a hash index
Total: 2 disk accesses
Factor that
determines the performance of
a hash index
Best case performance using hash index
Hash index versus
ordered index (B+-tree)
Hash function produces psuedo random numbers that do not preserve ordering:
if k1 < k2, there is no guarantee that Hash(k1) < Hash(k2)
Hash index versus
ordered index (B+-tree)
Ordered index can support ordered searches (queries):
Problem with a hash index
Problem with a hash index
- Fact:
- The block numbers in the
index file
is an array of
block pointers
that is:
- small enough to be stored entirely in main memory
Graphically:
Therefore:
- We disregard the access time to a block pointer
- The block numbers in the
index file
is an array of
block pointers
that is:
- Performance of
a Hash index:
- Suppose we
look up using
key N:
- We read in the
first index block into the
memory:
The search for key N results in failure (not found)....
- We read in the
next (overflow) index block into the
memory:
The search for key N succeeds
We use the corresponding block/record pointer to access the data
- Suppose we
look up using
key N:
- Conclussion:
- Performance of a hash index depends of the number of overflow blocks used....
- The fastest
hash index is
when:
- No overflow blocks are used....
Graphically:
- Performance:
- 1 block access to
read the
index block
(and find the
record (database address) address
of the
data block):
- 1 block access to
read the
data block
(containing the searched data)
into memory
Best performance of hash index:
- Total: 2 disk blocks accesses to lookup any record !!!
- 1 block access to
read the
index block
(and find the
record (database address) address
of the
data block):
- Hash index
strengths and
weaknesses:
- Hash index is
generally
faster (less disk accesses)
than ordered index (B+-tree)
- Hash index
cannot support
ordered searches, like:
SELECT fname, lname FROM employee WHERE lname > 'Smith'
- Hash index is
generally
faster (less disk accesses)
than ordered index (B+-tree)
- Ordered index
strength over
a hash index:
- Hash index
can support
ordered searches, like:
SELECT fname, lname FROM employee WHERE lname > 'Smith'
Example:
- Hash index
can support
ordered searches, like:
- Problem:
- When many keys are inserted
into the hash table,
we will have
many overflow block
- Overflow blocks will requrie more disk block read operations and slow performance
- We can reduce the
# overflow blocks by
increasing
the size (= B) of the
hash table
- The
Hash Table size
(= B)
is
very hard to change
- Changing the
hash table size will
usually require:
- Re-hashing all keys in the existing hash table into a new table (with the new size) !!!
- Changing the
hash table size will
usually require:
- When many keys are inserted
into the hash table,
we will have
many overflow block
- Solution:
Dynamic hashing:
- Dynamic hashing = a hashing technique that allow the size of the hash table to change with relative low cost