lect10 - 1.264 Lecture 10 SQL, part 3 Connecting to...

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

View Full Document Right Arrow Icon
1.264 Lecture 10 SQL, part 3 Connecting to database servers
Background image of page 1

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

View Full DocumentRight Arrow Icon
Data definition language (DDL) We’ve explored the data manipulation language (DML) so far. SQL also has a data definition language (DDL): CREATE DATABASE CREATE TABLE CREATE INDEX (and other CREATE statements) ALTER TABLE ALTER VIEW (and other ALTER statements) DROP DATABASE DROP TABLE DROP VIEW (and other DROP statements)
Background image of page 2
Indexes Index is a separate data object in the database that lists the table rows in order to allow rapid lookup. Each index for each table is a separate object Primary keys and foreign keys are automatically indexed Rapid access to indexed columns Each index may be updated when a row is updated, so indexes slow updates, insertions and deletes Practical maximum of 3 or 4 indexes per table. If others are needed on occasion, add and drop them as needed If a database is mostly read, use many indexes to speed performance If database is mostly updates, use as few indexes as possible Clustered indexes Physically rearrange rows by a single index to maximize disk access speed
Background image of page 3

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

View Full DocumentRight Arrow Icon
Example of indexes Customer database Customer ID is primary key We also want to search by: Customer name (last, first) City, state Postal (zip) code Address Index the name, city/state, zip and address Four indexes: slow insert, update, delete, but fast lookup If customer database is fairly stable, this is fine Similar logic for parts catalog, bill of materials, etc. Internet search engines use ‘text retrieval engines’ Index every word in the entire database; count occurrences and rank matches. Recent advances (frequency of links, usage…) enhance this. Syntax:
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 12/06/2011 for the course ESD 1.264j taught by Professor Georgekocur during the Fall '06 term at MIT.

Page1 / 14

lect10 - 1.264 Lecture 10 SQL, part 3 Connecting to...

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