Databases Reference
In-Depth Information
In the latter version we have only used one statement to achieve the same results. Besides
the code length, the important thing here is that we thought in terms of set-operations,
rather than in an iterative way. Relational databases perform better when we use this type of
operation. We will see how much and why in
Chapter 4
,
Optimizing SQL Code
and
Chapter 6
,
Optimizing PL/SQL Code
, in the
Introducing arrays and bulk operations
and
Array processing
and bulk-collect
recipes, respectively.
Inadequate relational design
A big issue could be the relational design of the database. Here we are not discussing
academic ways to design a database system, because in the real-world sometimes a
relational design could be less-than-perfect in terms of normalization, for example,
to provide better performance in the way the data is used.
When we speak about bad relational design, we mean problems like over-normalization,
which often leads to an overabundance of table joins to obtain the desired results.
Often, over-normalization is a problem which arises when we try to map an object-oriented
model to a relational database: a good volume and operations analysis could help in
designing the logical model of the database. For example, introducing a redundant column
to a table can lead to better performance because the redundant data, otherwise, have to
be calculated by scanning (in most cases) a big table.
Another big issue in relational design is related to the use of incorrect indexes on a table.
Based on the data selection approach an application is going to take, correct indexes should
be set on the table, and this is one of the design considerations while creating a relational
database model.
Improper use of storage structures
The Oracle database logical structure is determined by the tablespace(s) and by the
schema objects. Wrong choices about these structures often lead to bad performance.
While designing an Oracle database, we have a rich set of schema objects, and we have
to answer questions like "Which is better, a bitmap index or a reverse key index?", looking
at both the application and data.
In the latest releases of Oracle database, many operations to alter storage structures can
be performed with the database online, with minimal performance decay, and without
service shortage.
We will examine in depth the problems we have just been presented with in later chapters,
namely, session management and relational design in
Chapter 2
, cursor management in
Chapter 4
, and storage structures in
Chapter 3
.
OK, let's begin!
