Transcript presentation source

Indexes and more Table
Creation
Indexes
• Increase database performance
• must be explicitly defined
• once defined, are transparent to the user
• once created, the system maintains it
• more than one can exist on a given table
Creating an Index
• Syntax
CREATE [UNIQUE] INDEX index_name
ON table_name (column_name)
• Example
create index auind
on authors (au_id)
Composite Index
•Used when columns have a logical
relationship and would be searched as a
unit
•Example
create index au_name_ind
on authors (au_lname, au_fname)
•order not important, but performance is
better when primary search col is first
2 Kinds of Indexes
• Unique Index
• Clustered Index
Unique Index
• No 2 rows are permitted to have the same
value
• system checks data upon creation and data
addition
• rejects duplicates and returns an error
• should only be created on a column that
requires uniqueness eg. ssn, acct code
• can be created as a composite or single
column
• helps in maintaining data integrity
• boosts search performance
Clustered Index
•System sorts rows on an ongoing basis so that
the physical order is the same as the indexed
order
•only 1 can exist per table
•should only be created for a column that is most
often retrieved in order
•greatly increases performance when searching
for contiguous key values… especially a range
•slows down data updates due to the sorting
involved
Things to Consider
• Indexes greatly increase query response time
• every index requires system resources to store
and maintain
• indexes can actually slow down the
performance of UPDATES, INSERTS, and
DELETES due to index maintenance
So… don’t over index
What Should We Index?
• Any column frequently used in retrieval
• primary key columns
• columns that are often queried in a sorted
order
• columns that are used in joins
• columns that are often searched for ranges
We Should NOT Index…
• Columns rarely used in queries
• columns with 2 or 3 possible values
eg. Male or Female
• small tables
SQL-92 Create Table Constraints
• PRIMARY KEY
– rejects duplicates and nulls
• UNIQUE
– rejects duplicates, allows nulls
• DEFAULT
– inserts the default value when no value is entered
• CHECK
– validates data format
• FOREIGN KEY and REFERENCES
– ties foreign key to the primary key it references
Put it on paper!
Column
Datatype
Null?
Key
title_id
title
char(6)
varchar(80)
not null
not null
primary, unique
unique
type
pub_id
price
advance
char(12)
char(4)
money
money
Default
Reference
2 letter then 2 dig
unclass
null
null
Check
business,
mod_cook,
trad_cook
publishers,
pub_id
Then write your SQL
create table title
(title_id char(6) not null
constraint tididx primary key
constraint tidcheck check
(title_id like ‘[A-Z] [A-Z] [0-9] [0-9]…’),
title varchar(80) not null
constraint titleidx unique,
type char(12)
default ‘unclassified’ null
constraint typechk check
(type in(‘business’, ‘mod_cook’, ‘trad_cook’)),
pub_id char(4) null
reference publishers (pub_id),
price money null,
advance money null)
Changing a Table
• Syntax
– ALTER table table_name
add column_name datatype null|not null
Removing Objects
• Database
– DROP DATABASE db_name
– deletes ALL tables and data within it!!
• Table
– DROP TABLE table_name
– deletes table and its contents
• Index
– DROP INDEX table_name.index_name
– deletes named index on named table
Joins
•
•
•
•
What columns do I need?
What tables have these columns?
Are all the tables related in some way?
If not, are there other tables that can relate
them?
• How are they all related?
• Link them together by setting their common
fields equal in the WHERE clause.
• Restrict the WHERE clause to the record(s) of
interest.
2 ways of looking at a Join
• Looking at all the tables, linking them
together and treating them like one big
table.
• Setting the main search criteria and then
linking the common fields to the data
that is of interest.