Synopses & Reviews
Oracle Design looks thoroughly at the field of Oracle relational database design. The design of both databases and applications is an often neglected area of Oracle, but one that has an enormous impact on the ultimate power and performance of a system. If the initial design is poor, then the most powerful hardware, the most sophisticated software tools, and the most highly tuned data and programs won't make your system run smoothly and efficiently. Indeed, applications that have been designed poorly will never be able to perform well, regardless of the tuning and retrofitting performed later on.There are three main areas of Oracle design:
- The design of the specific database objects (e.g., tables, views, indexes, stored functions) that will be implemented in a database.
- The design of the screens, reports, and programs that will maintain the data and allow inquiries against it.
- Under certain circumstances, the design must also be concerned with the specific environment or technology (e.g., the network topology, the hardware configuration, and the use of a client/server, parallel processing, or distributed database architecture).
This book examines all aspects of database and code design. Part I examines the project life cycle and where design fits in that cycle; it shows a sample case study, identifies the areas of Oracle7 that are of particular interest to designers, takes a look ahead at Oracle8, and provides an in-depth discussion of data modeling (e.g., entities, relationships, attributes, entity models, function hierarchies). Part II describes design issues for the database itself -- denormalization, data types, keys, indexes, temporal data, import/export, backup, recovery, security, and more. Part III explores design issues for specific architectures and environments -- client/server, distributed database, data warehouses, and parallel processing. Part IV describes design issues for the code that accesses the database -- metrics and prototypes, locking, the toolset, design of screens, reports, batch programs, etc. Part V contains summary appendixes.The table of contents follows:Part I: Getting Started with Design
- 1. Introduction
- 2. Why is Design so Important for Oracle?
- 3. Data Modeling
Part II: Designing the Database
- 4. Deciding When to Denormalize
- 5. Choosing Data Types and NULLs
- 6. Choosing Keys and Indexes
- 7. Dealing with Temporal Data
- 8. Loading and Unloading Data
- 9. Deciding on Object Placement and Storage
- 10. Safeguarding Your Data
Part III: Designing for Specific Architectures
- 11. Designing for Client/Server
- 12. Designing Distributed Databases
- 13. Designing for Data Warehouse
- 14. Designing for Parallel Processing
Part IV: Designing the Code Modules
- 15. Introduction to Code Design
- 16. Determining Where to Locate the Processing
- 17. Metrics, Prototypes, and Specifications
- 18. Locking
- 19. Selecting the Toolset
- 20. Designing Screens, Reports, Batch Programs, Error Handling, and Help
Part V: Appendixes
- A. Off-the-Shelf Packages
- B. Tricks of the Trade
Synopsis
This book focuses exclusively on Oracle database design. It covers the most up-to-date Oracle issues and technologies, including massively parallel processors, very large databases, data warehouses, client-server, and distributed database. The design advice is detailed and thorough. The book delves deeply into design issues and gives advice that will have a major impact on your database and system performance.
About the Author
Dave Ensor is manager of Worldwide Solutions, PATROL R&D, with BMC Software where his roles are to assist customers in their use of both BMC's PATROL product and the Oracle Server, and to feed the results of his field work back into product planning. He has more than 30 years of IT experience and has been involved with the design and performance issues surrounding Oracle since 1987. For many years he led Oracle Worldwide's Performance Studies Group based in the UK, which provided consultancy support to both customer and internal projects with critical performance requirements. Dave is well known as a speaker on performance management and design; he presents his papers at user conferences and writes and delivers one-day seminars. He lives in the UK just outside London, but spends much of the year traveling to user sites and meetings. In his spare time he also travels, but in this case without his laptop and with his wife. He can be reached at
[email protected]. Ian Stevenson is a freelance consultant specializing in database design and development. He has worked with database technology for 19 years, starting with early hierarchical models. He worked for Oracle (UK) for two years in post-sales support and Human Resources Development. This is where he formed his friendship with Dave Ensor. Ian has a first-class honors degree in mathematics from the University of Southampton and is a member of the British Computer Society. He is married to Brenda and has two children, Todd and Tara. He is a fanatic supporter of the Southampton football club. He can be contacted via
[email protected].
Ian Stevenson is a freelance consultant specializing in database design and development. He has worked with database technology for 19 years, starting with early hierarchical models. He worked for Oracle (UK) for two years in post-sales support and Human Resources Development. This is where he formed his friendship with Dave Ensor. Ian has a first-class honors degree in mathematics from the University of Southampton and is a member of the British Computer Society. He is married to Brenda and has two children, Todd and Tara. He is a fanatic supporter of the Southampton football club. He can be contacted via [email protected].
Table of Contents
Table of Contents
Preface
I. Getting Started With Design
1. Introduction
What Is Design?
A Case Study
Looking at Alternative Methods and Approaches
Planning the Design Phase
Design Tasks
2. Why Is Design So Important for Oracle?
Designing for Specific Architectures
Designing for Performance
Other Design Considerations
Designing for Oracle7
Introducing Oracle8
3. Data Modeling
Types of Models
What Is Data Modeling?
Entity Relationship Diagrams
Quality-Assuring the Conceptual Information Model
Entity Life Histories and Data Flow Diagrams
Data-Driven Design and Meta Models
II. Designing the Database
4. Deciding When to Denormalize
Denormalization: What, Why, and When?
Methods of Implementing Denormalization
Other Types of Denormalization
5. Choosing Datatypes and Nulls
Available Datatypes
Numeric Data
Date and Time (Temporal) Data
String Data
Unstructured Data and BLOBs
Other Datatypes
Null Values
6. Choosing Keys and Indexes
Primary Keys
Other Keys
Indexes: An Overview
Index Types and Indexing Techniques
7. Dealing with Temporal Data
The Problem with Temporal Data
Design Issues for Temporal Databases
Temporal Data: A Summary
8. Loading and Unloading Data
Dealing with External Systems
Data Compatibility Issues
Data Migration Steps
Data Transformation
File Formats
Ordering, Recovery, and Commit Frequency
Using SQLxLoader
Oracle7 to Oracle7: a Special Case
Outbound Data
9. Deciding on Object Placement and Storage
Specifying Object Placement
Sizing
Specifying Storage Parameters
Creating Scripts
Implementation Planning
10. Safeguarding Your Data
Archiving
Auditing
Security
Backup
III. Designing for Specific Architectures
11. Designing for Client/Server
Why Client/Server?
What Is Client/Server?
What Kind of Hardware?
Basic Design Issues for Client/Server
Designing for Client/Server
SQLxNet Basics
Anatomy of a SQL Statement
Cutting in the Middle Man
Special Design Considerations
12. Designing for Distributed Databases
When Should You Use Distributed Databases?
Evolution of Oracle Support for Data Distribution
Selecting a Data Distribution Strategy
Example Scenarios
Using Distributed Database for Fallback
Other Design Considerations
Data Distribution Summary
13. Designing for Data Warehouses
Why Data Warehouses?
What Is a Data Warehouse?
Design Issues for Data Warehouses
Extracting and Loading the Data
Meta Data
Transformation Types and Methods
Retrieving the Data via Data Mining and OLAP Tools
14. Designing for Parallel Processing
Why Do We Need Parallelism?
Designing to Achieve Parallelism
Parallel Query Option (PQO)
Oracle Parallel Server (OPS)
IV. Designing the Code Modules
15. Introduction to Code Design
Analysis Deliverables
Mapping Functions to Modules
Don't Forget the System Modules
Source and Version Control
Template Code
Designing for Testing
CASE Products in Run Unit Design
16. Determining Where to Locate the Processing
Data Rules vs. Process Rules vs. Interface Rules
Logic Placement
Locking Issues
How Does This Relate to Three-Tier Architectures?
17. Metrics, Prototypes, and Specifications
Developing Design and Build Metrics for Modules
Banishing Megamodules
Shall We Prototype?
Where are my Specs? Guidelines for Module Specifications
Specifying Screens and Reports
Specifying Batch Processes
18. Locking
Locking Strategies
Deadlocks
Serializability
Other Design Issues
19. Selecting the Toolset
Types of Tools
Which Selection Criteria Are Important?
Client/Server Tools
Designing for the World Wide Web
20. Designing Screens, Reports, Batch Programs, and More
Designing Screens
Designing Reports
Designing Batch Programs
Error Handling
Navigation
Online Help
V. Appendixes
A. Off-the-Shelf Packages
Evaluating a Package
Does It Fit?
Can It Be Tailored?
The Big Data Model Issue
B. Tricks of the Trade
Fixing Those Mutating Tables
Dealing with the Millennium Date Problem: An Oracle Perspective
Providing User Extensibility
Index