Database Reference
In-Depth Information
I have a pretty simple mantra when it comes to developing database software, one that has been consistent for
many years:
•
You should do it in a single SQL statement if at all possible. And believe it or not, it is almost
always possible. This statement is even truer as time goes on. SQL is an extremely powerful
language.
•
If you can't do it in a single SQL Statement, do it in PL/SQL—as little PL/SQL as possible!
Follow the saying that goes “more code = more bugs, less code = less bugs.”
•
If you can't do it in PL/SQL, try a Java stored procedure. The times this is necessary are
extremely rare nowadays with Oracle9
i
and above. PL/SQL is an extremely competent, fully
featured 3GL.
•
If you can't do it in Java, do it in a C external procedure. This is most frequently the approach
when raw speed or using a third-party API written in C is needed.
•
If you can't do it in a C external routine, you might want to seriously think about why it is you
need to do it.
Throughout this topic, you will see the preceding philosophy implemented. We'll use PL/SQL—and object types
in PL/SQL—to do things that SQL itself can't do or can't do efficiently. PL/SQL has been around for a very long
time—over 26 years of tuning (as of 2014) has gone into it; in fact, way back in Oracle10
g,
the PL/SQL compiler itself
was rewritten to be an optimizing compiler for the first time. You'll find no other language so tightly coupled with SQL,
nor any as optimized to interact with SQL. Working with SQL in PL/SQL is a very natural thing—whereas in virtually
every other language from Visual Basic to Java, using SQL can feel cumbersome. It never quite feels “natural”—it's not
an extension of the language itself. When PL/SQL runs out of steam—which is exceedingly rare today with current
database releases—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. Often, 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 in many cases.
The Black Box Approach
I have an idea, borne out by first-hand personal experience (meaning I made the mistake myself ), as to why
database-backed software development efforts so frequently fail. Let me be clear that I'm including here those projects
that may not be documented as failures, but nevertheless take much longer to roll out and deploy than originally
planned because of the need to perform a major rewrite, re-architecture, or tuning effort. Personally, I call such
delayed projects failures: more often than not they could have been completed on schedule (or even faster).
The single most common reason for failure is a lack of practical knowledge of the database—a basic lack of
understanding of the fundamental tool that is being used. The black box approach involves a conscious decision to
protect the developers from the database. They are actually encouraged not to learn anything about it! In many cases,
they are prevented from exploiting it. The reasons for this approach appear to be FUD-related (Fear, Uncertainty,
and Doubt). Developers have heard that databases are “hard,” that SQL, transactions, and data integrity are “hard.”
The solution: don't make anyone do anything hard. They treat the database as a black box and have some software
tool generate all of the code. They try to insulate themselves with many layers of protection so that they don't have to
touch this “hard” database.
This is an approach to database development that I've never been able to understand, in part because, for me,
learning Java and C was a lot harder than learning the concepts behind the database. I'm now pretty good at Java
and C but it took a lot more hands-on experience for me to become competent using them than it did to become
competent using the database. With the database, you need to be aware of how it works but you don't have to
know everything inside and out. When programming in C or Java/J2EE, you do need to know everything inside and
out—and these are
huge
languages.
