database design considerations
DESCRIPTION
Database Design Considerations. Overview. Oracle specific Design Considerations Data Integrity Issues Performance Considerations Storage Issues. Why Adapt Data Design?. User Expectations. Volumes Hardware Network O.S. Adapted Physical Design. Initial design. Oracle specifics. - PowerPoint PPT PresentationTRANSCRIPT
Database Design Considerations
Oracle specific Design Considerations Data Integrity Issues Performance Considerations Storage Issues
Overview
Why Adapt Data Design?• User Expectations
• Oracle specifics
• Volumes• Hardware• Network• O.S.
AdaptedPhysicalDesign
Initial design
Depending on: Domains Storage issue Performance Use
Select a data type for columns: Character Number Date Large Objects
Oracle Data Types
Primary key columns Unique Key columns Foreign key columns Mandatory columns Optional columns
Large object columns always at the end
Suggested Column Sequence
Primary KeysCREATE TABLE countries( code NUMBER(6) NOT NULL, name VARCHAR2(25) NOT NULL, currency NUMBER (10,2) NOT NULL);ALTER TABLE countries ADD CONSTRAINT cty_pk PRIMARY KEY (code);
Constraint and Index name
Choosing the Right Key Simplicity Ease of use Performance Size Meaningless Stability
Primary Keys
Artificial Keys
DS (D)pkpkpk
fk1 = d_a_fk fk2 = d_b_fk fk3 = d_c_fk
fk = x_d_fk
uuu
pk * Id
,fk1,fk2,fk3
****
A_idB_idC_idC4
pk * Id * C2
BS (B)
XS (X)pkfk1fk1fk1
****o
IdD_a_idD_b_idD_c_idC5
fk * D_id
pk * Id * C3
CS (C)pk * Id * C1
AS (A)
Sequences
CREATE SEQUENCE sequence_nameINCREMENT BY numberSTART WITH numberMINVALUE numberMAXVALUE numberCACHE number | NOCACHECYCLE | NOCYCLE;
223
224225
Restrict
Cascade
Default Nullify
Delete Update
Foreign Key Behavior
Supported by Oracle through declaration
Indexes• Performance
ALBERT 2655
ALFRED 3544
ALICE 7593
ALLISON 3456
ALVIN 8642
ALPHONSO 2841
Name Phone
• Uniqueness
bcdefghijklmnopq
rstuvwxyz
Choosing Indexes
aba .1.2.5abb .1.3.5abc .1.1.5bba .1.4.5. . .
B*tree
aba .1.2.5abb .1.4.5bba .1.3.5cba .1.1.5...
Reverse
YXZXZ
abcabaabbbbabbc
C1 C2
Bitmap
Y10000
X01010
Z00101
XZYZX
abaabbabcbbabbc
I.O.TableC1 C2
Primary key columns and Unique Key columns (Up to Version 6)
Foreign Key columns When significant better performance can be
observed in SELECT statements
Which Columns to Index?
Avoid indexing:
• Small tables• Columns frequently updated
!
When referenced in a Where clause or Order By
When the Where clause does not include some operators
When the optimizer decides With hints in the SQL statement
When Can Indexes be Used?
CUSTOMERSRegionCol1 Col2 Col3
CUSTOMERS_R1RegionCol1 Col2 Col3
CUSTOMERS_R2RegionCol1 Col2 Col3
Partitioning Tables and Indexes
Restricting access Presentation of data Isolate applications from data structure Save complex queries Simplify user commands
Views
T1 T2 T3 T4
V1 V2 V3 V4
Advantages Dynamic views Present denormalized data from normalized tables Simplify SQL statements
Disadvantages May affect performances Restricted DML in some cases
Reasons for Views
Unique index Views with “Check option” clause Generic Arc implementation
Old Fashioned Design
Generic Arc ImplementationA# Id* Name
AS (A)
Y# Id* Name
**
. . . Table_nameFk_id
(X or Y)
X# Id* Name
Different physical databases appear as one logical database.
Distributed Database
Resilience Reduced line traffic Location transparency Local autonomy Easier growth path
but Increased, distributed, complexity
Benefits of Distributed Databases
Database Structure
DATA BLOCK
SEGMENT
DATABASE
DATA FILE
INDEXSEGMENT
TABLESPACE
TABLE OR INDEX PARTITION
EXTENT FREE
TABLESEGMENT
part of
located inpart
of
residence of
part of
consists of
resides in
consists of
container of
consistsofpart
ofpart
of
sliced in sliced in
USED
OTHERSEGMENT
consists of
part of
resides inresidence
of
Data Types Primary, Foreign, and Artificial Keys Indexes Partitioning Views Distributed design
Summary
Data Types Artificial Keys Product Pictures
Practices