Databases Reference
In-Depth Information
In this recipe, instead, we are warned about over-indexing. In the first part of this recipe,
you must have seen that when there are indexes on a table, insert (updating and deleting)
operations on the table take long to execute.
When designing the database schema, it's important to balance the query speed obtained
using indexes and the bottleneck in DML operations caused by the same indexes.
We have seen regular B-tree indexes in our example, but the same behavior applies to other
types of indexes, especially when using bitmap indexes.
In the second part of the recipe there is a business rule that applies in our application.
We decided to implement it in a trigger, which calculates the value for the maximum credit
awarded to the customer based on his credit limit, age, and gender.
When we insert data in the table, the trigger fires and its body is executed, resulting in a
slightly slower execution. Triggers are useful in many situations, but from Oracle 11
g
we
have another tool to use, which can help us use fewer triggers to apply business rules when
designing our application.
This new feature is called virtual columns. It allows us to define one or more columns in a
table, whose value is the result of an expression. The actual value of the field is not stored
on-disk, but is calculated when requested.
Using virtual columns we obtained an important gain in insert performance. As seen earlier,
when we talked about using indexes—free lunch is over—there is a drawback when using
virtual columns. The counterpart to the performance gain introduced in insert and update
statement is a slightly slower query, when we ask for the virtual column value, which needs to
be calculated on the fly.
Result caching can be used also with tables
containing virtual columns.
For the same reason, it's mandatory to avoid the use of
SELECT
*
statements on tables that
contain virtual columns, to avoid the useless calculation of values not required.
As always, in performance tuning there isn't a silver bullet, but a complex mix of pros and cons
in every solution, which needs to be calibrated based on the application's requirements.
See also
F
The
Reducing
the
number
of
requests
to
the
database
using
materialized
views
recipe in
Chapter
2
,
Optimizing Application Design
F
The
Indexing
the
correct
way
,
Using
bitmap
indexes
, and
Migrating
to
index
organized
tables
recipes in
Chapter
3
,
Optimizing Storage Structures
