20-index-notes

20-index-notes - 1 Indexing CPS 116 Introduction to...

Info iconThis preview shows pages 1–4. 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
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Indexing CPS 116 Introduction to Database Systems 2 Announcements (Thu. Nov. 17) ยก Project milestone #2 feedback will be emailed to by this weekend ยก Homework #4 will be assigned next Tuesday 3 Basics ยก Given a value, locate the record(s) with this value SELECT * FROM R WHERE A = value ; SELECT * FROM R , S WHERE R . A = S . B ; ยก Other search criteria, e.g. ยข Range search SELECT * FROM R WHERE A > value ; ยข Keyword search database indexing Search 2 Bart Jessica Lisa Martin Milhouse Nelson Ralph Sherri Terri Windel 123 456 857 4 Dense and sparse indexes ยก Dense: one index entry for each search key value ยก Sparse: one index entry for each block ยข Records must be clustered according to the search key Sparse index on SID Dense index on name 123 Milhouse 10 3.1 142 Bart 10 2.3 279 Jessica 10 4 345 Martin 8 2.3 456 Ralph 8 2.3 512 Nelson 10 2.1 679 Sherri 10 3.3 697 Terri 10 3.3 857 Lisa 8 4.3 912 Windel 8 3.1 5 Dense versus sparse indexes ยก Index size ยข Sparse index is smaller ยก Requirement on records ยข Records must be clustered for sparse index ยก Lookup ยข Sparse index is smaller and may fit in memory ยข Dense index can directly tell if a record exists ยก Update ยข Easier for sparse index 6 Primary and secondary indexes ยก Primary index ยข Created for the primary key of a table ยข Records are usually clustered according to the primary key ยข Can be sparse ยก Secondary index ยข Usually dense ยก SQL ยข PRIMARY KEY declaration automatically creates a primary index, UNIQUE key automatically creates a secondary index ยข Additional secondary index can be created on non-key attribute(s) CREATE INDEX StudentGPAIndex ON Student(GPA) ; 3 7 ISAM ยก What if an index is still too big?...
View Full Document

This document was uploaded on 01/17/2012.

Page1 / 9

20-index-notes - 1 Indexing CPS 116 Introduction to...

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

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