Lecture 14 Indexing 1

Lecture 14 Indexing 1 - Indexing Spring 2012 Slides...

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

View Full Document Right Arrow Icon
1 Indexing Spring 2012 Slides Courtesy of R. Ramakrishnan and J. Gehrke
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 Indexes v An index on a file speeds up selections on the search key fields for the index. § Any subset of the fields of a relation can be the search key for an index on the relation. § Search key is not the same as key (minimal set of fields that uniquely identify a record in a relation). v An index contains a collection of data entries , and supports efficient retrieval of all data entries k* with a given search key value k . § Data entry versus data record. § Given data entry k*, we can find record with search key k in at most one disk I/O. (Details soon …)
Background image of page 2
Creating indexes in SQL 3 CREATE INDEX age_index USING [BTREE | HASH] ON Emp(age) CLUSTER age_index ON Emp DROP INDEX age_index
Background image of page 3

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

View Full DocumentRight Arrow Icon
Using indexes in a database v Select indexes to build: § select tables, search keys, index type, clustering? § + speed up queries of interest § - costs space § - costs maintenance overhead v Build them (then run ANALYZE) v Check: are indexes being used for query Q: § check EXPLAIN output of Q v Run queries and watch them go faster! 4
Background image of page 4
5 Tree structured indexes v Tree-structured indexing techniques support both range searches and equality searches .
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 B+ Tree Indexes v Leaf pages contain data entries v Non-leaf pages have index entries; only used to direct searches: P 0 K 1 P 1 K 2 P 2 K m P m index entry Non-leaf Pages Pages (Sorted by search key) Leaf
Background image of page 6
7 Example B+ Tree v Equality selection : find 28*? 29*?
Background image of page 7

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

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

This note was uploaded on 03/27/2012 for the course CLASSICS 122 taught by Professor Smith during the Spring '12 term at UMass (Amherst).

Page1 / 18

Lecture 14 Indexing 1 - Indexing Spring 2012 Slides...

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

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