Slideshow:
What is an index ?
Index = a list of (keyword, location) pairs
Index in database systems
Query processing without an index
Scan all data blocks and look for matching records
Query processing with an index
Access only data blocks that contains a matching record
Record structure of an index file
- Sample of an
index
(found in the back of
a text book):
- Index:
- Index = a data structure that enable the user to find (= locate) data items efficiently (quickly) using search keys
- Consider
the following query on
relation
stored on
disk:
- Relation R is
stored on
disk:
- Query:
select * from employee where lname = 'Smith';
- Relation R is
stored on
disk:
- Without
further information,
the only way to
process the
above query is:
- Read
all blocks of
the relation employee
- Look for tuples with lname = 'Smith'
- Read
all blocks of
the relation employee
- More efficient processing:
- Maintain
location information
on
specific attributes
(most useful ones, e.g.,
key attributes)
in the relation
Graphically:
We only need to read a setset of the data block
- Maintain
location information
on
specific attributes
(most useful ones, e.g.,
key attributes)
in the relation
- Search key:
- Search key = field(s) used to create the additional search information used to speed up a look up operation
- Index file:
- Index file =
a file that store
records of the
following format:
+------------+----------------------+ | Search key | block/record pointer | +------------+----------------------+Graphically:
Note:
- The size of an index file is usually much smaller than the size of a data file
- Index file =
a file that store
records of the
following format:
- Note:
- The pointer is
usually a
block pointer
- So the index allow you to locate the block that contain the record quickly
- The record is
found by
a search operation
inside the block
(after the block is
read into
main memory)
- Because the search operation only access main memory, the search is relatively quick (because you don't use disk access operations in the search)
- The pointer is
usually a
block pointer