I spend the bulk of my time working with Oracle database software
and, more to the point, with people who use this software. Over
the last twelve years, I've worked on many projects —
successful ones as well as failures, and if I were to encapsulate
my experiences into a few broad statements, they would be:
- An application built around the database — dependent on
the database — will succeed or fail based on how it uses the
- A development team needs at its heart a core of
'database savvy' coders who are responsible for
ensuring the database logic is sound and the system is tuned.
These may seem like surprisingly obvious statements, but in my
experience, I have found that too many people approach the
database as if it were a 'black box' — something
that they don't need to know about. Maybe they have a SQL
generator that will save them from the hardship of having to
learn SQL. Maybe they figure they will just use it like a flat
file and do 'keyed reads'. Whatever they figure, I can
tell you that thinking along these lines is most certainly
misguided; you simply cannot get away with not understanding the
database. This chapter will discuss why you need to know about
the database, specifically why you need to understand:
- The database architecture, how it works, and what it looks
- What concurrency controls are, and what they mean to you.
- How to tune your application from day one.
- How some things are implemented in the database, which is not
necessary the same as how you think they should be implemented.
- What features your database already provides for you and why
it is generally better to use a provided feature then to build
- Why you might want more than a cursory knowledge of SQL.
Now this may seem like a long list of things to learn before you
start, but consider this analogy for a second: if you were
developing a highly scalable, enterprise application on a brand
new operating system (OS), what would be the first thing you
would do? Hopefully, you answered, 'find out how this new OS
works, how things will run on it, and so on'. If you did not, you
Consider, for example, one of the early versions of Windows
(Windows 3.x, say). Now this, like UNIX, was a 'multi-tasking'
operating system. However, it certainly didn't multi-task like
UNIX did — it used a non-preemptive multi-tasking model
(meaning that if the running application didn't give up control,
nothing else could run — including the operating system).
In fact, compared to UNIX, Windows 3.x was not really a multi-
tasking OS at all. Developers had to understand exactly how the
Windows 'multi-tasking' feature was implemented in order to
develop effectively. If you sit down to develop an application
that will run natively on an OS, then understanding that OS is
What is true of applications running natively on operating
systems is true of applications that will run on a database:
understanding that database is crucial to your success. If you do
not understand what your particular database does or how it does
it, your application will fail. If you assume that because your
application ran fine on SQL Server, it will necessarily run fine
on Oracle then, again, your application is likely to fail.
Before we begin, I feel it is only fair that you understand my
approach to development. I tend to take a database-centric
approach to problems. If I can do it in the database, I will.
There are a couple of reasons for this — the first and
foremost being that I know that if I build functionality in the
database, I can deploy it anywhere. I am not aware of a server
operating system on which Oracle is not available — from
Windows to dozens of UNIX systems to the OS/390 mainframe, the
same exact Oracle software and options are available. I
frequently build and test solutions on my laptop, running
Oracle8i on Windows NT. I deploy them on a variety of UNIX
servers running the same database software. When I have to
implement a feature outside of the database, I find it extremely
hard to deploy that feature anywhere I want. One of the main
features that makes Java appealing to many people — the
fact that their programs are always compiled in the same virtual
environment, the Java Virtual Machine (JVM), and so are highly
portable — is the exact same feature that make the database
appealing to me. The database is my Virtual Machine. It is my
'virtual operating system'.
My approach is to do everything I can in the database. If my
requirements go beyond what the database environment can offer, I
do it in Java outside of the database. In this way, almost every
operating system intricacy will be hidden from me. I still have
to understand how my 'virtual machines' work (Oracle, and
occasionally a JVM) — you need to know the tools you are
using — but they, in turn, worry about how best to do
things on a given OS for me.
Thus, simply knowing the intricacies of this one 'virtual OS'
allows you to build applications that will perform and scale well
on many operating systems. I do not intend to imply that you can
be totally ignorant of your underlying OS — just that as a
software developer building database applications you can be
fairly well insulated from it, and you will not have to deal with
many of its nuances. Your DBA, responsible for running the Oracle
software, will be infinitely more in tune with the OS (if he or
she is not, please get a new DBA!). If you develop client-server
software and the bulk of your code is outside of the database and
outside of a VM (Java Virtual Machines perhaps being the most
popular VM), you will have to be concerned about your OS once
I have a pretty simple mantra when it comes to developing
- You should do it in a single SQL statement if at all
- If you cannot do it in a single SQL Statement, then do it in
- If you cannot do it in PL/SQL, try a Java Stored Procedure.
- If you cannot do it in Java, do it in a C external procedure.
- If you cannot do it in a C external routine, you might want
to seriously think about why it is you need to do it...
Throughout this book, you will see the above philosophy
implemented. We'll use PL/SQL and Object Types in PL/SQL to do
things that SQL itself cannot do. PL/SQL has been around for a
very long time, over thirteen years of tuning has gone into it,
and you will find no other language so tightly coupled with SQL,
nor any as optimized to interact with SQL. When PL/SQL runs out
of steam — for example, when we want to access the network,
send e-mails' and so on — we'll use Java. Occasionally,
we'll do something in C, but typically only when C is the only
choice, or when the raw speed offered by C is required. In many
cases today this last reason goes away with native compilation of
Java — the ability to convert your Java bytecode into
operating system specific object code on your platform. This lets
Java run just as fast as C.