cs411-07-indexing-2 - 1 CS411 Database Systems 07: Indexing...

Info iconThis preview shows pages 1–17. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 CS411 Database Systems 07: Indexing 2 2 Why Do We Learn This? Find out the desired information (by value) quickly! Declarative No/less physical dependency 2 3 Indexing Indexing types of indexes B+ trees hash tables 4 Q: What is indexing? To build an index. But what is an index? A labeled pointer to an (a collection of) item that satisfies some common property Examples in the real world? Books ToC, Topic indices 5 What is indexing? 6 Indexes An index on a file speeds up selections on the search key field(s) Search key = any subset of the fields of a relation Search key is not the same as key (minimal set of fields that uniquely identify a record in a relation). Entries in an index: (k, r), where: k = the key r = the record OR record id OR record ids 7 Types of Indexes Clustered/unclustered Clustered = records sorted in the key order Unclustered = no Dense/sparse Dense = each record has an entry in the index Sparse = only some records have Primary/secondary Primary = on the primary key Secondary = on any key Some textbooks interpret these differently B+ tree / Hash table / 8 Ex: Clustered, Dense Index Clustered: File is sorted on the index attribute Dense : sequence of (key,pointer) pairs 10 20 30 40 50 60 70 80 10 20 30 40 50 60 70 80 9 Clustered, Sparse Index 10 30 50 70 90 110 130 150 10 20 30 40 50 60 70 80 Sparse index: one key per data block Save more space Sacrifice efficiency 10 How if duplicate keys? 11 Clustered Index with Duplicate Keys Dense index: point to the first record with that key 10 20 30 40 50 60 70 80 10 10 10 20 20 20 30 40 12 Clustered Index with Duplicate Keys Sparse index: pointer to lowest search key in each block: OK? Try search for 20 10 10 20 30 10 10 10 20 20 20 30 40 Additional pointer doesnt help Check Backward? 13 Clustered Index with Duplicate Keys Better: pointer to lowest new search key in each block: Search for 20 10 20 30 40 50 60 70 80 10 10 10 20 30 30 40 50 14 Unclustered Indexes Often for indexing other attributes than primary key Always dense (why ?) The locality of values has been broken! 10 10 20 20 20 30 30 30 20 30 30 20 10 20 10 30 15 Summary Clustered vs. Unclustered Index Data entries ( Index File ) ( Data file ) Data Records Data entries Data Records CLUSTERED UNCLUSTERED 16 Composite Search Keys Composite Search Keys : Search on a combination of fields. Equality query : Every field value is equal to a constant value. E.g. wrt <sal,age> index: age=20 and sal =75 Range query : Some field value is not a constant. E.g.: age =20; or age=20 and sal > 10 sue 13 75 bob cal joe 12 10 20 80 11 12 name age sal <sal, age> <age, sal> <age> <sal> 12,20 12,10 11,80 13,75 20,12 10,12 75,13 80,11 11 12 12 13 10 20 75 80 Data records sorted by name Data entries in index sorted by <sal,age> Data entries sorted by <sal> Examples of composite key indexes using lexicographic order. 17...
View Full Document

This note was uploaded on 02/17/2012 for the course CS 411 taught by Professor Winslett during the Fall '07 term at University of Illinois at Urbana–Champaign.

Page1 / 63

cs411-07-indexing-2 - 1 CS411 Database Systems 07: Indexing...

This preview shows document pages 1 - 17. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online