indexing
DESCRIPTION
Vu Phan SE157B Spring2007 04/15/2007. Indexing. 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. - PowerPoint PPT PresentationTRANSCRIPT
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