Refining the Server Model

1
Refining the Server Model
2
Adding New Tables
New Table
New View
New Snapshot
New Cluster
New Column
New Man FK
New Opt FK
3
Add a Foreign Key
4
Create Index for FK
DDT Automatically Creates Index for FK
5
Other Columns to Index

• Key columns
• Columns used frequently in queries
• Columns with many distinct values
• Guidelines
• Start with the least number of indexes
• Add indexes as the data grows
• Check both query and DML perfomance

6
Choosing the Number of Indexes

• More indexes slower insert and delete
• Concatenated indexes can help

Queries NAME 'SMITH' JOB LIKE 'SALES' AND
NAME 'SMITH' JOB 'CLERK'AND NAME
'SMITH'AND HIREDATE gt '1-JUN-98' HIREDATE
'31-DEC-98'
Indexes
NAME
JOB
HIREDATE
7
Oracle Sequence

• Database object
• Generates unique numbers
• Minimizes contention
• Gaps in sequence

8
Adding Surrogate Key
Create a Sequence
Create Key
Assign to Column
9
DDT Surrogate Keys

• Automatically creates key
• Automatically creates sequence
• Automatically assigns sequence to key column

10
Oracle Sequences

• Efficient
• Have Gaps
• Rolled back transactions do not return used
  sequence numbers

11
Eliminating the Gaps
CG_CODE_CONTROLS
EMPLOYEES
IDENTIFIER
LAST_NAME
2016
SMITH
Newrow
2017
HAMBURG
12
Creating a Code Control Sequence
Sequence Definitions
?
Create Sequence Name
1
2
3
Name of the sequence
Purpose of the sequence
Sequence type
ORACLE sequence
Code control sequence
13
Sequence Within Parent

• Generate a value within the context of the parent
  record

RENTAL_ITEMS
RENTALS
RE_ID
LINE_NO
RE_ID
1
1
1 2
2
1
2
1
2
2
14
Creating a Sequence Within a Parent

• Set AutoGen Type to Seq in Parent

15
Choosing a Method

• Oracle sequence
• Sequential values with gaps
• Recommended
• Code control sequence
• Databases other than Oracle
• Sequential values without gaps
• Contention can be tolerated
• Sequence within a parent
• Minimal number of detail records