{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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 Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right Arrow Icon

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

View Full Document Right 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 doesn’t 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

{[ snackBarMessage ]}

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 bookmark
Ask a homework question - tutors are online