Oracle Designers Handbook

Oracle Designers Handbook

by Dave Ensor, Ian Stevenson

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


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

Editorial Reviews

Covers database, code, and architecture design for the Oracle operating system. The text is arranged in four sections including an overview of Oracle and data modeling; aspects of database design including denormalization, data types, nulls, keys, indexes, temporal data, import/export, backup, recovery, security, archiving, and audit; examples of architecture design issues for client/server, distributed databases, data warehouses, and parallel processing; and aspects of code design including metrics and prototypes, locking, and the design of screens, reports, batch programs, online help text, and navigation. Annotation c. by Book News, Inc., Portland, Or.

Product Details

O'Reilly Media, Incorporated
Publication date:
Nutshell Handbooks Series
Product dimensions:
7.05(w) x 9.21(h) x 1.17(d)

Read an Excerpt

Why did we write this book? For some time, we've both felt strongly that people underrate the importance of Oracle design and that a book devoted exclusively to this topic was sorely needed. Despite our common conviction, we came to this point from somewhat different directions.

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:

  • On all of our projects, we've spent valuable time pursuing possibilities that were nonstarters-in other words, they were never going to work out, no matter what we tried. Had we known this, we could have eliminated them from the outset and saved some precious time.
  • As we grew in experience, we found that it was invaluable to have even rough notes from previous projects giving the potential advantages and disadvantages of possible design techniques. Even experienced designers sometimes overlook an option that might be used to solve a particular problem. So we concluded that having some type of reference, however nonabsolute, would be an invaluable tool in forcing us to think along different lines.
Essentially, this is our book in the sense that we-and people much like us-will be using it to do our daily work. We'd like you to share in the benefits we've derived from our previous, sometimes difficult, but always instructive experiences.

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.

Why Oracle?

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:

  • Functionality and adaptability
  • Throughput
  • Response time
  • Availability
  • Ease of use
  • Security

Structure of This Book

This book is separated into five parts, as described below. Although we'd like you to read every word, we know that some readers may not want to read the book from cover to cover; even those who read a large amount of the material may not do so in the order presented. To accommodate you, we've tried to make the chapters within the book fairly self-contained and to minimize the number of crossreferences. Experienced designers and those who have good experience of project life cycles may find that they can skim some of the earlier chapters (notably Chapters 1 and 2), which introduce these subjects. Those who have used the recent versions of Oracle7 might be able to skip Chapter 2. Many of the chapters that describe specific technologies may be relevant only if you are working on a design that uses the particular feature. Distributed database (Chapter 12) and data warehousing (Chapter 13) are examples of such specific areas.

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.

About Our Readers

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.

Meet 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 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

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

Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >