cs411-07-indexing - CS411 Database Systems 7: Indexing 07:...

Info iconThis preview shows pages 1–18. 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

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: CS411 Database Systems 7: Indexing 07: Indexing 1 Why Do We Learn This? 2 Indexing Indexing types of indexes + trees B+ trees hash tables 3 Q: What is indexing? To build an index. But what is an index? Examples in the real world? 4 What is indexing? 5 Indexes d fil d l ti th h k 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 6 Types of Indexes Clustered/unclustered lustered = records sorted in the key order Clustered records sorted in the key order Unclustered = no ense/sparse Dense/sparse Dense = each record has an entry in the index parse = only some records have Spa se o y so e eco ds ave Primary/secondary rimary = on the primary key Primary on the primary key Secondary = on any key Some textbooks interpret these differently B+ tree / Hash table / 7 Ex: Clustered, Dense Index Clustered: File is sorted on the index attribute Dense : sequence of (key,pointer) pairs 10 10 20 30 40 20 30 50 60 70 40 50 80 60 70 8 80 Clustered, Sparse Index Sparse index: one key per data block 10 10 30 50 70 20 30 90 110 130 40 50 150 60 70 9 80 How if duplicate keys? 10 Clustered Index with Duplicate Keys Dense index: point to the first record with that key 10 20 10 10 30 40 50 10 20 60 70 80 20 20 30 40 11 Clustered Index with Duplicate Keys Sparse index: pointer to lowest search key in each l k block: 10 10 20 10 10 30 10 20 OK? 20 20 Try search for 20 30 40 12 Clustered Index with Duplicate Keys Better: pointer to lowest new search key in each l k block: Search for 20 10 10 20 30 40 10 10 50 60 20 30 70 80 30 40 13 50 Unclustered Indexes Often for indexing other attributes than primary key Always dense (why ?) 10 20 10 20 20 30 30 20 20 30 30 10 20 30 10 30 14 ummary Clustered vs Unclustered Summary Clustered vs. Unclustered Index Data entries dex File Data entries ( Index File ) ( Data file ) Data Records Data Records CLUSTERED UNCLUSTERED 15 omposite Search Keys Composite Search Keys i t S h K h l f i t k Composite Search Keys : Search on a combination of fields. quality query: Every field Examples of composite key indexes using lexicographic order. Equality query: Every field value is equal to a constant value. E.g. wrt <sal,age> name age sal 12,20 12,10 11,80 11 12 12 index: age=20 and sal =75 bob cal joe 12 10 20 80 11 12 <age, sal> <age> 13,75 13 Range query: Some field value is not a constant. E.g.: sue 13 75 20,12 10,12 75,13 10 20 75 Data records sorted by name age =20; or age=20 and sal > 10 <sal, age> <sal> , 80,11 80 ata entries in index ata entries 16 Data entries in index sorted by <sal,age> Data entries sorted by <sal> Q: Our textbook as example: Indexes? Q: Our textbook as example: Indexes?...
View Full Document

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

Page1 / 64

cs411-07-indexing - CS411 Database Systems 7: Indexing 07:...

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

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