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:
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.
Ian was motivated to write this book after being involved in the design phase of several Oracle projects for both Version 6 and Version 7. He began to notice that the same questions and problems would arise time and time again. But the conclusion reached for similar questions varied from project to project. It simply wasn't possible to say firmly that one solution was right and another wrong. Indeed, each solution might well have been the best possible one for the particular project concerned. He wanted to create a repository of information where he could record the reasoning that went into making a particular design decision. Then, in the future, he (and others) could refer to this repository when they were faced with a similar problem. It didn't take long to make the leap to believing that a book would be the best medium for sharing this hard-won design experience with others in the Oracle community.
Dave spent many years leading Oracle's Worldwide Performance Studies Group. In doing this work, he found, again and again, that Oracle performance problems had a similar root cause. They usually weren't the result of defective build techniques or poor Oracle tuning. Most often, they were the consequence of project teams having made the wrong design decisions. This is not to say that the "traditional" causes of performance problems never apply. But it is simply the case that manyprojects can never perform well because of defective design decisions. It was against this background that Dave wrote a one-day seminar called "Designing for Oracle7" which he presented to audiences around the world. However, even that seminar did not provide a foolproof recipe for design. In writing it, Dave discovered, like Ian, that each case needed to be judged on its own merits against each of the precise requirements of the project and the precise technical environment in which that project was carried out.
If there are no firm rules, why attempt to write a book on the subject? And, come to think of it, why are there so few books about design available on the market? To answer the second question first, we believe that the difficulty of being unable to present categorical answers (even to basic questions such as "Should every table have a primary key?") has deterred many authors and publishers who believe that readers will only be able to cope with technical material if there are nice, neat, absolute answers to all of the questions they ask. It just isn't so.
We have two very good reasons for writing this book:
Relational database design can be very frustrating. Seemingly simple business requirements might translate into complex design problems. Often you go full circle, considering various options that might satisfy a business requirement, only to come back to the original proposal-by which time you're so confused that you can't remember what your original objections were to that proposal. You feel that you're making no forward progress, and depression sets in. Even with this book as your companion, such dark days are occasionally inevitable. Just remember that we all go through these dreadful design experiences and that eventually you'll see the light at the end of the tunnel.
The real purpose of this book is to help you work smarter, not harder. We want to help you get some real benefit out of the efforts that you're going to have to put into design.
One of the first questions our publisher asked us was, "Why a design handbook for Oracle? Why not one for all relational databases? Is Oracle so different?" This remains a very important question, and one that we're anxious to answer.
All design, not only the design of computer applications, is about making the best use of the materials and technology available to you. Oracle7 has a number of specific and unique technical characteristics that you will need to take into account when you're selecting a design solution. A good portion of this book is devoted to discussions of these features. In many cases, we propose tests that you may want to perform in your own environment before you select a specific design approach.
We don't pretend that this book will make you a successful Oracle designer overnight. Sadly, there is no substitute for experience. One of the hallmarks of the very best designers is that they are still learning all the time. What we've set out to create here is a framework in which you can design in a more structured and controlled environment. Each chapter presents alternatives for major design decisions and highlights advantages and pitfalls. Of course, each decision will still rest with you, but we hope that you'll be better armed to justify that decision.
Oracle7 is now a mature product. From the time of Oracle Corporation's original announcement of this product, it was clear that the system marked the beginning of a general trend within Oracle to take the business rules' intelligence out of the application (or user interface) and put it in the database (or server). This evolution means that substantially more time now needs to be spent in database design than was the case for an Oracle Version 6 project or a 3GL project. Building should now be a simpler task, particularly if the design is good. Once the application build phase has started in earnest, it becomes difficult and costly to correct any bad decisions that were made during design.
Poor design decisions can lead to a long and frustrating development period and are often ultimately reflected in the poor quality of the eventual system. In fact, if the design is bad enough, there may never be an eventual system or, if there is, it may be a rather sick beast. When you are designing a system, always take the usability and performance of the system into account. If you are told that response time doesn't matter or that "We'll fix it later on," we advise you to argue the case. Fixing a design shortcoming or a bug in a production system is an extremely costly exercise. Attempting to solve performance problems by "throwing more hardware at them" works, in our experience, only on low-end systems; even there, it's never a guaranteed fix.
Remember too that the lasting user perceptions of any application are usually determined by users' experiences during the first week they use the system. The performance and user response time associated with any new computer system, together with its ease of use and robustness, make the difference between user acceptance and rejection.
In summary, these are the goals we should have for any design process, and the goals we'd like this book to help you reach: to provide, at the time the system is launched and throughout the life of the system, the required:
Part I: Getting Started with Oracle Design
Chapter 1, Introduction, introduces the various phases of project development-in particular, strategy, analysis, design, and build; it provides a simplified example of a real project that illustrates the various project phases and deliverables.
Chapter 2, Why Is Design So Important for Oracle? focuses on Oracle, describing the special design issues and itemizing the most important features of Oracle7; it also takes a look ahead to Oracle8.
Chapter 3, Data Modeling, looks carefully at data modeling; it defines classic relational database terms like entity, relationship, and third normal form (3NF); and it itemizes the deliverables that the analysts must deliver to the designers so they can do the job of turning the conceptual data model into a logical data model.
Part II: Designing the Database
Chapter 4, Deciding When to Denormalize, examines specific techniques for denormalizing the data in the database to improve performance.
Chapter 5, Choosing Datatypes and Nulls, defines the various Oracle datatypes, describes how to choose the most appropriate datatypes for objects in the database, and explores the thorny issues of what null means and how nulls should be treated.
Chapter 6, Choosing Keys and Indexes, discusses how to choose the best keys for your particular database.
Chapter 7, Dealing with Temporal Data, explores a specific problem with Oracle and other relational databases-they don't adequately support time-series (temporal) data. It suggests a number of Oracle-specific techniques that you can use to overcome restrictions on this type of data.
Chapter 8, Loading and Unloading Data, explores the various ways that you can populate your Oracle7 database from external data sources. It also discusses how to extract data from the Oracle7 database.
Chapter 9, Deciding on Object Placement and Storage, looks at some of the most important physical aspects of database design, such as sizing and file placement.
Chapter 10, Safeguarding Your Data, covers the topics of backup, archiving, auditing, and security.
Part III: Designing for Specific Architectures
Chapter 11, Designing for Client/Server, applies Oracle7 design methods to the client/server model. We examine a variety of techniques for distributing the processing to optimize performance and usability.
Chapter 12, Designing for Distributed Databases, takes the data distribution model a step further and looks at the why and how of distributed databases. We examine the various options available with Oracle7 and consider which should be employed in various scenarios.
Chapter 13, Designing for Data Warehouses, looks at the steps involved in setting up and maintaining a data warehouse. We look at dimensional modeling and the various techniques involved in getting the data into the warehouse and extracting it out again.
Chapter 14, Designing for Parallel Processing, describes parallel processing concepts, looks at practical applications of Oracle's Parallel Query Option and Oracle Parallel Server, and discusses such technologies as disk striping and RAID.
Part IV: Designing the Code Modules
Chapter 15, Introduction to Code Design, describes the basic concepts of code module design.
Chapter 16, Determining Where to Locate the Processing, looks at an innovative way of partitioning the logic of an application.
Chapter 17, Metrics, Prototypes, and Specifications, describes the formal side of code design; in particular, this chapter focuses on how you can ensure that your modules meet the requirements.
Chapter 18, Locking, contains information that will help you minimize contention within your applications.
Chapter 19, Selecting the Toolset, compares the merits of the various categories of front-end products that can support an Oracle back-end.
Chapter 20, Designing Screens, Reports, Batch Programs, and More, deals with specific design issues for screens, reports, batch programs, error handling, navigation, and online help.
Part V: Appendixes
Appendix A, Off-the-Shelf Packages, compares the merits of buying a packaged solution with those of developing a complete application from scratch.
Appendix B, Tricks of the Trade, focuses on three specific design techniques. We suggest a method for avoiding the annoying "mutating table" problem in Oracle7 triggers; we look at the problems presented by the imminent change of century (the millennium problem); and we take a brief tour into the extensible world of SQL.
This book assumes prior knowledge of Oracle, although readers with experience using another relational database management system (RDBMS) who are making the transition to Oracle should find much of the book useful. It doesn't matter if your experience to date is mostly in the area of development or database administration. All we are really looking for is familiarity with the terms and concepts used in this book. We also presuppose a basic knowledge of a development project life cycle.
How will this book help you as a designer? We have attempted to provide you with a framework for your design and a checklist of common design issues. The book won't necessarily provide all the answers. We hope, though, that when you are performing a specific design task, you'll turn to this book. It will help you identify a number of questions which you otherwise might not have considered; we hope that it will also give you pointers that will help you resolve your design problem. We also hope that the book will provoke thought and discussion, will serve as a reference for important issues, and will help ensure the completeness of your design.
Which Oracle?
Most of what we say applies to all releases of Oracle7. In the few cases where this is not so, we indicate clearly which versions it does apply to. And what about Oracle8? At the time this book went into production, the product hadn't yet been announced. Naturally, we expect that Oracle8 will pose new problems as users start using new features. We also hope that Oracle8 will provide solutions to some of the shortcomings discussed in this book. In any case, we are confident that the great majority of our recommendations will continue to apply.
NOTE: What we refer to as simply Version 7.2 might well be called Oracle7 Release 7.2 in Oracle's own documentation. Throughout this book we refer to Oracle7 (and occasionally to Oracle6 and Oracle8), and we refer to the different releases of Oracle7 as versions since that is how most users refer to them. Oracle Corporation prefers the term releases.
| Preface | ||
| 1 | Introduction | 3 |
| 2 | Why Is Design So Important for Oracle? | 35 |
| 3 | Data Modeling | 60 |
| 4 | Deciding When to Denormalize | 101 |
| 5 | Choosing Datatypes and Nulls | 113 |
| 6 | Choosing Key and Indexes | 131 |
| 7 | Dealing with Temporal Data | 164 |
| 8 | Loading and Unloading Data | 191 |
| 9 | Deciding on Object Placement and Storage | 215 |
| 10 | Safeguarding Your Data | 225 |
| 11 | Designing for Client/Server | 267 |
| 12 | Designing for Distributed Databases | 299 |
| 13 | Designing for Data Warehouses | 335 |
| 14 | Designing for Parallel Processing | 369 |
| 15 | Introduction to Code Design | 393 |
| 16 | Determining Where to Locate the Processing | 410 |
| 17 | Metrics, Prototypes, and Specifications | 423 |
| 18 | Locking | 437 |
| 19 | Selecting the Toolset | 446 |
| 20 | Designing Screens, Reports, Batch Programs, and More | 453 |
| App. A | Off-the-Shelf Packages | 477 |
| App. B | Tricks of the Trade | 487 |
| Index | 513 |
Overview
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...