CS122aSlides13

CS122aSlides13 - ICS122A / EECS116 Introduction to Data...

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

View Full Document Right Arrow Icon
1 ICS122A / EECS116 Introduction to Data Management Spring 2009 TA Alex Behm (for Prof. Mike Carey) Bren School of ICS UC Irvine Slides based on previous CS122a lecture notes as well as material borrowed  slides  on Information Retrieval  and Approximate String Search by Alex Behm
Background image of page 1

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

View Full DocumentRight Arrow Icon
Announcements HWs and projects HW #5 due next Tuesday Project Step #3 due next Thursday Discussion section meeting Midterm solutions Today Indexing (continued) Introduction to Information Retrieval
Background image of page 2
Indexes in SQL  Create an index: o Syntax:   CREATE INDEX  <index_name>  ON  <table_name>(<col1, col2,…>); o Example:    CREATE INDEX  sal index  ON  emp(sal); Drop an index o Syntax:   DROP INDEX < index-name>; o Example:  DROP INDEX  sal_index; Unique index o Syntax:   CREATE UNIQUE INDEX    <index_name> ON <table_name>(<cols>); o Semantics:   <cols> are constrained to be unique (key). o Example:   CREATE UNIQUE INDEX name_index ON emp(ename);   All employees must have different names.  Most DBMS automatically create an index for primary key and unique attributes
Background image of page 3

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

View Full DocumentRight Arrow Icon
Choosing Indexes More indexes = better performance? Indexes take space Indexes need to be maintained when data is updated Indexes add one level of indirection We pay some random I/Os, e.g. for scanning the leaves of a B+tree Hard to decide best set of indexes Optimal index selection depends on both query and  update workload and many other things Automatic index selection is still an area of active research
Background image of page 4
Evaluating an Index  Access types supported efficiently, e.g. Tuples with a specified value in the attribute Or tuples with an attribute value falling in a  specified range of value B+-Tree vs. Hash Based Index Useful Indexes Attributes appearing often in WHERE On relation’s key On attributes that are almost keys On a foreign key (for a join)
Background image of page 5

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

View Full DocumentRight Arrow Icon
Index Selection A simple cost model: Tuples of a relation are stored on many pages of a disk One page can hold many tuples Accessing one tuple requires a whole page to be  brought into main memory Evaluating Index (with and without): Access time Insertion time Deletion time Space overhead (ignore for now)
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 06/18/2009 for the course CS 122a taught by Professor Carey during the Spring '09 term at UC Irvine.

Page1 / 26

CS122aSlides13 - ICS122A / EECS116 Introduction to Data...

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

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