Chapter 11: Indexing and Hashing - Manhattan College

Chapter 11: Indexing and Hashing - Manhattan College

Indexing Database System Concepts, 6th Ed. Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Basic Concepts Indexing mechanisms used to speed up access to desired data. E.g., author catalog in library Search Key - attribute or set of attributes used to look up records in a file.

An index file consists of records (called index entries) of the form search-key pointer Index files are typically much smaller than the original file Two basic kinds of indices: Ordered indices: search keys are stored in sorted order Hash indices: search keys are distributed uniformly across

buckets using a hash function. Database System Concepts - 6th Edition 11.2 Silberschatz, Korth and Sudarshan Index Evaluation Metrics Access types Access time Insertion time

Deletion time Space overhead Database System Concepts - 6th Edition 11.3 Silberschatz, Korth and Sudarshan Ordered Indices In an ordered index, index entries are stored sorted on the search key value. E.g., author catalog in library.

Primary index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file. Also called clustering index The search key of a primary index is usually but not necessarily the primary key. Secondary index: an index whose search key specifies an order different from the sequential order of the file. Also called non-clustering index.

Database System Concepts - 6th Edition 11.4 Silberschatz, Korth and Sudarshan Dense Index Files Dense index Index record appears for every search-key value in the file. E.g. index on ID attribute of instructor relation Database System Concepts - 6th Edition 11.5 Silberschatz, Korth and Sudarshan Dense Index Files (Cont.)

Dense index on dept_name, with instructor file sorted on dept_name Database System Concepts - 6th Edition 11.6 Silberschatz, Korth and Sudarshan Sparse Index Files Sparse Index: contains index records for only some search-key values. Applicable when records are sequentially ordered on search-key

To locate a record with search-key value K we: Find index record with largest search-key value < K Search file sequentially starting at the record to which the index record points Database System Concepts - 6th Edition 11.7 Silberschatz, Korth and Sudarshan Sparse Index Files (Cont.)

Compared to dense indices: Less space and less maintenance overhead for insertions and deletions. Generally slower than dense index for locating records. Database System Concepts - 6th Edition 11.8 Silberschatz, Korth and Sudarshan Secondary Indices Example Secondary index on salary field of instructor

Index record points to a bucket that contains pointers to all the actual records with that particular search-key value. Secondary indices have to be dense Database System Concepts - 6th Edition 11.9 Silberschatz, Korth and Sudarshan Multilevel Index Database System Concepts - 6th Edition 11.10 Silberschatz, Korth and Sudarshan

Recently Viewed Presentations

  • IN THE NAME OF GOD - bpums

    IN THE NAME OF GOD - bpums

    -nested case-control study . Hospital and community controls. If cases are selected from a hospital ward, the controls would be selected from patients with different diseases , apparently unrelated to the exposure and disease of interest, in the same hospital.
  • 1. Which of the following is NOT one of the domains (highest ...

    1. Which of the following is NOT one of the domains (highest ...

    Example- Polydactyly in the Pennsylvania Amish. Genetic Drift. ... The trees in a mature/climax community in a temperate deciduous forest biome generally _____ than trees earlier in the succession process. ... Which of the following is NOT one of the...
  • Eloquence: HP e3000 IMAGE Migration

    Eloquence: HP e3000 IMAGE Migration

    Eloquence HPe3000 IMAGE Migration Michael Marxmeier Marxmeier Software AG [email protected]
  • EOQ Model 2/26/2016 Business Idea UTD-Branded Hover Boards

    EOQ Model 2/26/2016 Business Idea UTD-Branded Hover Boards

    Quiz. A skateboard retailer enjoys a constant demand of exactly 200 customers every month.The cost of ordering and receiving shipments is $100 per order.Their accounting department estimates that annual carrying costs are $3.00 per unit.The lead time for shipments of...
  • Transforming conversations: UNMASKING your student conduct hearing MACHUO

    Transforming conversations: UNMASKING your student conduct hearing MACHUO

    RA Christina walked into the living room where Samuel's roommate Kevin was and observed an open bottle of Corona on the living room table. RA CHRISTINA asked Kevin to open the fridge and freezer. There were 8 bottles of Corona...
  • The Power of Support Groups for Breast Cancer

    The Power of Support Groups for Breast Cancer

    Erin L Nau, LCSW, & Angela Papalia, LMSW . Adelphi NY Statewide Breast Cancer Hotline and Support Program. Chris, Madeline, & Myra. Former and Current Support Group Members. Tonight's Presentation. Norms.
  • Title

    Title

    Council's job is to develop laws and policies, which the City's administration enforces. The Municipal Act governs the creation, administration and government of Ontario's municipalities. However, it is council's job to develop laws and policies specific to Cambridge, which the...
  • Optical Flow - UBC Computer Science

    Optical Flow - UBC Computer Science

    MotionVis Donovan Parks Outline Project motivation and goal Details of projects Video showing results Future work and conclusions Motivation Large motion capture DB's widely used in the film and video game industries This has created a desire to be able...