Transcript Data Definition Language - Department of Computer and

Department of Computer and Information Science,
School of Science, IUPUI
Data Definition Language
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
1
Context
Client Tool
SQLPlus, Toad
Database Management System
SQL – Query an object – SELECT
DML – Change data in an object –
INSERT, UPDATE, DELETE
DDL – Create objects - CREATE
SQLPlus
Dale Roberts
Toad
2
Basic CREATE TABLE
Physical implementation of a Relation.
Syntax:
CREATE TABLE tablename
(column_a type NOT NULL,
column_b type,
...
);
Data Types:
CHAR(n)
VARCHAR2(n)
NUMBER
NUMBER(n)
NUMBER(m,n)
DATE
TIMESTAMP
NOT NULL
character data of fixed size
up to 2000 characters.
variable length character data
up to 4000 characters.
integer data
integer data, length n
floating point data,
m = total number of digits (not width),
n = digits after decimal point
date/time data
date/time/fractions of a second
null constraint, requires a value for row to exist
Dale Roberts
3
Other CREATE TABLE clauses
Appendix shows all CREATE TABLE clauses pp. 1109-1121
Object tables
XML types
STORAGE clause – specifies how the data is stored in the
database
VARRAYs and Nested Tables
LOB (Large OBject) parameters
External Tables
And many others
Dale Roberts
4
Constraints
Candidate Keys:
Column(s) which uniquely identify rows in a table. Also called unique constraint.
Primary Keys:
Column(s) used to specify and enforce uniqueness among rows in a table.
create table employee (
employee_id varchar(8) not null,
column x, ....,
constraint emp_PK primary key (employee_id));
Foreign Keys:
Column(s) used to reference rows in another table.
These values are actually the primary keys in the other table.
alter table employee
add constraint emp_dept_FK
foreign key (dept_id)
references department(dept_id);
Check Constraint:
A constraint that forces a set of values (domain) for a column. NOT NULL columns
automatically get a system generated check constraint.
Dale Roberts
5
Constraint Example
SQL> desc customer
Name
Null?
----------------------- -------ID
NAME
STATE
Type
---NUMBER
VARCHAR2(40)
CHAR(2)
SQL> desc state2
Name
Null?
----------------------- -------STATE_CODE
NOT NULL
STATE_NAME
Type
---CHAR(2)
VARCHAR2(50)
Dale Roberts
SQL> alter table state2
2 add constraint PK_State2
3 primary key (state_code);
Table altered.
SQL> alter table customer
2 add constraint
cust_another
3 foreign key (state)
4 references
5 state2(state_code);
Table altered.
6
DROP vs TRUNCATE vs DELETE TABLE
Dropping Tables:
DROP TABLE tablename;
Dropping a table removes the table,
both the table’s data and definition are removed.
Truncating Tables:
TRUNC TABLE tablename;
Truncating a table removes only the data,
Table lock required, cannot be rolled back, implicit commit,
the table’s definition is still available.
Deleting Tables:
DELETE tablename WHERE condition;
Removes a set of data,
Table lock not required, read-consistent view, rollback.
Dale Roberts
7
ALTER TABLE
Tables can be altered to:
change column definitions,
add new columns (without NOT NULL),
rename columns
enable or disable constraints,
change storage parameters,
drop columns as of 8i.
ALTER TABLE emp
ADD (thriftplan NUMBER(7,2),
loancode CHAR(1) NOT NULL);
ALTER TABLE emp
MODIFY (thriftplan NUMBER(9,2));
alter table emp drop column thriftplan;
Dale Roberts
8
Views
Special considerations about views:
You cannot insert if the underlying table has any NOT NULL columns that
are not in the view.
You cannot insert/update if any one of the view’s columns referenced in the
insert/update contains functions or calculations.
You cannot insert/update/delete if the view contains group by, distinct, or a
reference to rownum.
INSTEAD OF triggers get around the above limitation.
View Stability:
dropping the underlying tables of a view prevents the view from working.
altering the underlying tables of a view are OK.
Order By in Views:
ORDER BY does not work in views before Oracle 8i.
GROUP BY still consolidates rows in views,
a side affect of GROUP BY is ordering rows.
READ ONLY:
The read only clause prevents update/insert/delete of rows in a view.
Dale Roberts
9
Creating a Table from Another Table

Includes the data:
create table RAIN as
select City, Precipitation
from TROUBLE;

Does not include the data, only the table definition:
create table RAIN as
select City, Precipitation
from TROUBLE
where 1=2;
Dale Roberts
10
Logging
A critical design feature of database
management systems is the safeguarding
of data in the event of a system failure.
All commercial databases achieve the
high standard of recovery the database to
the last committed transaction.
Logging is the mechanism by which
Oracle creates redo logs, two or more
preallocated files that store all changes
made to the database as they occur.
It is common practice to place redo logs
in a separate disk than the tablespaces.
A separate concept archiving is use to
copy redo logs for permanent storage.
During recovery, both archive logs and
redo logs are used to replay transactions
from the last full backup to “roll forward”
to the last committed transaction.
Dale Roberts
11
Logging and Mass Updates
Logging is critical for enabling recovery during
production updates.
Some activities occur when the database is not
operational, for example during software releases, initial
loads, or mass updates of data.
Typically, if a failure occurs when the system is not
operational, no recovery is required. In this case, it is
useful to create tables with NOLOGGING, load the initial
data or make a mass update, and then ALTER TABLE to
turn on LOGGING.
Use of NOLOGGING is important when working with
large tables. It is the difference between a job running
days versus a few hours.
Dale Roberts
12
Partitioning
Partitioning address key issues
with the management of very large
tables.
Decompose tables into more
manageable pieces called
partitions.
Partitioning is declarative
meaning that no SQL must be
rewritten.
Data is managed at partition-level
for loads, indexes creation,
backup and recovery.
If care is taken to partition by
expressions in WHERE clause,
queries can access a single
partition.
Dale Roberts
13
Partitioning Choices
List – values by partition
Range – range by partition
Hash – random (balanced) partition
Composite – Partitioning and subpartitioning
Dale Roberts
14
Partition Example
create table BOOKSHELF
(CategoryName VARCHAR2(20), …)
partition by range (CategoryName)
(partition PART1 values less than (‘B’)
tablespace PART1_TS,
partition PART2 values less than (MAXVALUE)
tablespace PART2_TS);
Dale Roberts
15
Indexing
Like the index in the back of your textbook, indexes provide a list of sorted
values and the location of where to find more information about the value.
Indexes require storage separate from the rows of a table.
Individual tables rows are accessed via ROWID.
Default index is B*-tree, depicted below, and discussed in detail in any data
structures text.
Dale Roberts
16
Types of Indexes
Primary Key – Unique and not null – PK_*
Unique Key – Unique and allows null – UK_*
Foreign Key – Nonunique and references PK in related
table – FK_*
Nonuniqies index – Nonunique allows null – IX_*
Bitmap indexes – useful for WHERE clauses looking up
combinations of values. While maintaining B* adds to
the cost of DML statement, maintaining bitmap index
during DML is prohibitive. Do not use bitmap indexes
on tables whose values change regularly.
Important: Indexes are an example of an important
Computer Science concept – the space vs time tradeoff.
Also typical of database tradeoff DML vs SQL.
Dale Roberts
17
Index Examples
Unique:
create unique index UK_BOOKSHELF_AUTHOR
on BOOKSHELF_AUTHOR(Title, AuthorName)
tablespace INDEX_TS;
Support queries
WHERE Title=value AND AuthorName = value;
or
WHERE Title=value; - -Leading value
Function-based nonunique index
create index IX_BOOKSHELF_2 on
BOOKSHELF(Upper(Title));
Supports queries
WHERE Upper(Title) = value; -- must match exactly
Dale Roberts
18
Sequences
Sequence are declared counters used to
generate sequences of unique values.
Often use to generate unique id during INSERT
and UPDATE.
create sequence CustomerId increment by 1
start with 1000;
insert into CUSTOMER_DEMO(Name, Contact,
ID)
values (‘COLE CONSTRUCTION’, ‘Veronica’,
CustomerId.NextVal);
Dale Roberts
19
Acknowledgements
Loney, Oracle Database 10g The Complete Reference
Dale Roberts
20