CS145 Lecture Notes (10) -- Indexes




Singular: "Index"
Plural: "Indexes" or "Indices"

Index on attribute R.A:

  1. Creates additional persistent data structure stored with the database

  2. Can dramatically speed up certain operations:
(picture of unordered relation and indexed attribute)
















Example
    SELECT *
    FROM Student
    WHERE name = 'Mary'

Indexes are built on single attributes or combinations of attributes.

Question: What data structures are used for indexes?

1.

2.

Example
    SELECT *
    FROM Student
    WHERE name = 'Mary' and GPA > 3.5
Could use:
Indexes can also speed up joins.

Example

    SELECT * 
    FROM Student, Apply
    WHERE Student.ID = Apply.ID
Could use:

Question: What are the disadvantages of creating an index?

1.



2.



3.



Index Selection

(diagram of how a design advisor works)





















SQL Syntax

For one index on R.A:
  CREATE INDEX IndexName ON R(A)
For one index on (R.A1, R.A2, ..., R.An):
  CREATE INDEX IndexName ON R(A1, ...,  An)
To destroy an index:
  DROP INDEX IndexName
Will enforce R.A as a key:
  CREATE UNIQUE INDEX IndexName ON R(A)