Databases Reference
In-Depth Information
In this chapter, we will look at some structures available in Oracle databases that store
data and improve the access time—that is, the time needed to retrieve data—when the
data is queried.
The first recipes relate to tables—the most used storage structure—with useful tips to avoid
bottlenecks related to data stored in tables.
We will then move on to indexes (and inspect different index flavors). At the end of the chapter,
we will finally see recipes related to index organized tables and partitioning options. Choosing
the right structure to store data can drastically improve the performance of applications; using
the right index can speed up our queries, as can introducing drawbacks in insert, update, and
delete operations. As always, the performance tuning process aims to balance different needs
to obtain the maximum average speed in the different operations involving the database.
Avoiding row chaining
We encounter row chaining when the size of the row data is larger than the size of the database
block used to store it. In this situation, the row is split across more than one database block, so,
to read it we need to access more than one database block, resulting in greater I/O.
Getting ready
Before we can start, we have to alter an initialization parameter of the test database
(assuming the default block size is 8KB in the test database):
ALTER SYSTEM SET db_16k_cache_size = 16m scope=both;
We need to set this parameter to allocate a memory buffer dedicated to storing database
blocks of a different size; in this recipe, we will create a tablespace using a 16KB block size,
so we need the corresponding buffer allocated to use it.
How to do it...
In this recipe, we will examine how to detect row chaining issues, and how to avoid chaining in
our tables. Follow these steps:
1.
Connect to the HR schema:
CONNECT hr@TESTDB/hr
2.
Create the table
BIG_ROWS
:
CREATE TABLE HR.BIG_ROWS (
id number NOT NULL,
field1 char(2000) DEFAULT 'A' NOT NULL,
field2 char(2000) DEFAULT 'B' NOT NULL,
