Transcript refineserver

Refining the Server Model
Adding New Tables
New Table
New View
New Snapshot
New Cluster
New Column
New Man FK
New Opt FK
Add a Foreign Key
Create Index for FK
DDT Automatically Creates Index for FK
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
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 > '1-JUN-98';
HIREDATE = '31-DEC-98';
Indexes:
NAME JOB HIREDATE
NAME
JOB
HIREDATE
Oracle Sequence
• Database object:
– Generates unique
numbers
– Minimizes contention
• Gaps in sequence
1
Adding Surrogate Key
Create Key
Create a Sequence
Assign to Column
DDT Surrogate Keys
• Automatically creates key
• Automatically creates sequence
• Automatically assigns sequence to key
column
Oracle Sequences
• Efficient
• Have Gaps
– Rolled back transactions do not return
used sequence numbers
Eliminating the Gaps
CG_CODE_CONTROLS
CC_DOMAIN CC_NEXT VALUE CC_INCREMENT
EMP_SEQ1
2017
2018
1
EMPLOYEES
IDENTIFIER LAST_NAME
New
row
2016
2017
SMITH
HAMBURG
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
Sequence Within Parent
• Generate a value within the context
of the parent record
RENTALS
RE_ID
1
2
RENTAL_ITEMS
RE_ID
LINE_NO
1
1
2
2
1
2
1
2
Creating a Sequence Within a
Parent
Column Properties
Derivation
Autogen Type
…
Seq in Parent
Set AutoGen Type to Seq in
Parent
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