ics321-20091027-storage3

ics321-20091027-storage3 - ICS 321 Fall 2009 Storage &...

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

View Full Document Right Arrow Icon
ICS 321 Fall 2009 Asst. Prof. Lipyeow Lim University of Hawaii at Manoa 10/27/2009 1 Lipyeow Lim -- University of Hawaii at Manoa
Background image of page 1

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

View Full DocumentRight Arrow Icon
Creating Indexes Most DBMS (eg. DB2) supports only B+ tree indexes: CREATE INDEX myIdx ON mytable(col1, col3) CREATE UNIQUE INDEX myUniqIdx ON mytable(col2, col5) CREATE INDEX myIdx ON mytable(col1, col3) CLUSTER If a primary key is specified in the CREATE TABLE statement, an (unclustered) index is automatically created for the PK. To create a clustered PK index: Create table without PK constraint Create index on PK with cluster option Alter table to add PK constraint To get rid of unused indexes: DROP INDEX myIdx; 10/27/2009 Lipyeow Lim -- University of Hawaii at Manoa 2
Background image of page 2
Most DBMSs have very few knobs for storage heap files are dominant In contrast, index creation is user-controlled What indexes should we create ? Which relations should have indexes ? Which columns should be indexed ? How many indexes do we need ? What kind of indexes should we use ? Clustered ? Hash or Tree ? 10/27/2009 Lipyeow Lim -- University of Hawaii at Manoa 3
Background image of page 3

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

View Full DocumentRight Arrow Icon
Depends on the “Workload” For each query in the workload: How frequent does this query occur ? Which attributes are involved in selection/join conditions ? How selective are these conditions? For each update in the workload: How frequent does the update occur ? Which attributes are involved in selection/join
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/15/2010 for the course ICS 321 taught by Professor Lim during the Fall '09 term at University of Hawaii, Manoa.

Page1 / 13

ics321-20091027-storage3 - ICS 321 Fall 2009 Storage &...

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

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