Database Reference
In-Depth Information
Loss of information:
During the conversion of the original (correct) datatype to the
(wrong) database datatype, information gets lost. For example, let's imagine what happens
when the date and time of an event is stored with a
DATE
datatype instead of with a
TIMESTAMP WITH TIME ZONE
datatype. Fractional seconds and time zone information
get lost.
Things do not work as expected:
Operations and features for which the order of data is
important might result in unexpected results, because of the specific comparison semantics
associated to every datatype. Typical examples are issues related to range partitioned tables
and
ORDER BY
clauses.
Query optimizer anomalies:
The query optimizer might generate wrong estimates and,
consequently, might choose suboptimal execution plans because of wrong datatype
selection. This is not the fault of the query optimizer. The problem is that the query
optimizer cannot do its job because information is hidden from it.
In summary, there are plenty of good reasons for selecting datatypes correctly. Doing so will likely help you to
avoid many problems.
Not Using Bind Variables Correctly
From a performance point of view, bind variables introduce both an advantage and a disadvantage. The advantage of
bind variables is that they allow the sharing of cursors in the library cache, and in doing so they avoid hard parses and
the associated overhead. The disadvantage of using bind variables in
WHERE
clauses, and only in
WHERE
clauses, is that
crucial information is sometimes hidden from the query optimizer. For the query optimizer, to generate an optimal
execution plan for every SQL statement, having literals instead of bind variables is in fact much better. Chapter 2
discusses this topic in detail.
From a security point of view, bind variables prevent the risks associated with SQL injection. In fact, it is not
possible to change the syntax of a SQL statement by passing a value through a bind variable.
Not Using Advanced Database Features
Oracle Database is a high-end database engine that provides many advanced features that can drastically reduce
development costs, not to mention debugging and bug-fixing costs, while boosting performance. Leverage your
investment by taking advantage of those features as much as possible. Especially avoid rewriting already available
features (for example, do not create your own queuing system, because one is provided for you). That said, special
care should be taken the first time a specific feature is used, especially if that feature was introduced in the very same
database version you are running. You should not only carefully test such a feature to know whether it fulfills the
requirements, but also verify its stability.
The most common argument against advanced database features is that applications using them are closely
coupled to your current database brand and cannot be easily ported to another. This is true. However, most
companies will rarely change the database engine under a specific application anyway. Companies are more likely
to change the whole application before changing just the engine.
I recommend database-independent application design only when there are very good reasons for doing it.
And if for some reason doing database-independent design is necessary, go back and reread the discussion about
flexibility versus performance in the section “Implementing Generic Tables.” That discussion applies in this case
as well.
