{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

CS122aSlides13 - ICS122A EECS116 Introduction to Data...

Info iconThis preview shows pages 1–8. 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  from U-Wisconsin, Stanford, & Berkeley  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 Document Right 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 Document Right 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 Document Right 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
An Example on Calculating the  Best Index(es) Relations:
Background image of page 7

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

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

{[ snackBarMessage ]}