indexing

Vu PhanSE157B

Spring200704/15/2007

Overview

Basic Concepts Evaluation Factors Ordered Indices Types of Ordered Indices: Dense

Index and Order Index Multilevel Indices Index Update: Insertion and Deletion Secondary Indices

Basic Concepts

Purpose: to look up and access desired data quickly

How: Compress files into index files An index file consists index records, or index

entries

Two basic kinds of indices: Ordered indices – store search-keys in a sorted

order Hash indices – store search-keys across “buckets”

using a hash function

Search-key Pointer

Evaluation Factors

Access types

Access time

Insertion time

Deletion time

Space overhead

Ordered Indices

Data is sorted sequentially based on the search-key value.

Clustering index Also called primary index Its search key defines the sequential

order of data. Nonclustering index

Also called secondary index Its search key specifies an order

different from the sequential order of data.

Ordered Indices (Cont.)

Sequential file for account records

Types of Ordered Indices

Dense index – an index record appears for every search-key value in the file.

Types of Ordered Indices (Cont.) Sparse index – an index record

appears for only some of the search-key values in the file.

Dense Index vs. Sparse Index

Dense Index Sparse Index

Access Time Less More

Space More memory Less memory

Space Overhead More Less

Multilevel Indices

One index may be too large for efficient processing.

Solution: using multilevel index Treat the primary index as a sequence file

and create an sparse index on it Outer index: a sparse index of primary index Inter index: the primary index

If the outer index is still large, creating another level of index is necessary.

Multilevel Indices (Cont.)

Index Update

Index must be updated whenever a record is either inserted or deleted from the file.

Two types of index update: insertion and deletion

Index Update – Insertion

Perform a lookup using the search-key value appearing in the record being inserted.

Dense indices: If search-key value does not appear in the

index, insert the new record to an appropriate position

Otherwise, If the index record stores pointers to all records

with the same search-key value, add a pointer to the new record to the index record

If the index record stores a pointer to only the first record with the same search-key value, just place the record being inserted after the other records with the same search-key values

Index Update – Insertion (Cont.)

Sparse indices: Assume the index stores an entry for

each block of the file If no new block is created, no change

needs to be made to the index If a new block is created, add the first

search-key value in the new block to the index

Index Update - Deletion

Dense indices: If the deleted record was the only record

with its unique search-key value, delete it Other wise,

If the index record stores pointers to all records with the same search-key value, delete the point to the deleted record from the index record

If the index record stores a pointer to only the first record with the same search-key value, and if the deleted record was the first record, update the index record to point to the next record

Index Update – Deletion (Cont.)

Sparse indices: If the deleted record was the only record with its

search key, replace the corresponding index record with an index record for the next search-key value (in search-key order). If the next search-key value already has an index

entry, the index record is deleted instead of being replaced

If the record being deleted was one of many records with the same search-key value, and if the index record points to it, update the index record to point to the next record with the same search-key value

Secondary Indices

Sometimes we want to find records by an order differed from the sequential order of primary index

Solution: create a secondary index which has different index records of each search-key value

Secondary index record points to a bucket that contains pointers to all actual records

Secondary Indices

Secondary Indices have to be dense

References

Silberschatz, Korth, & Sudarshan. Database system Concepts

http://codex.cs.yale.edu/avi/db-book/ Dr. Lee’s lecture