1 / 19

Indexing

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.

perdy
Download Presentation

Indexing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Vu Phan SE157B Spring2007 04/15/2007 Indexing

  2. 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

  3. 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

  4. Evaluation Factors • Access types • Access time • Insertion time • Deletion time • Space overhead

  5. 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.

  6. Ordered Indices (Cont.) • Sequential file for account records

  7. Types of Ordered Indices • Dense index – an index record appears for every search-key value in the file.

  8. Types of Ordered Indices (Cont.) • Sparse index – an index record appears for only some of the search-key values in the file.

  9. Dense Index vs. Sparse Index

  10. 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.

  11. Multilevel Indices (Cont.)

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. Secondary Indices • Secondary Indices have to be dense

  19. References • Silberschatz, Korth, & Sudarshan. Database system Concepts • http://codex.cs.yale.edu/avi/db-book/ • Dr. Lee’s lecture

More Related