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

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