11. Tidy up by dropping the tablespace:
drop tablespace small including contents and datafiles;
Segment Reorganization
During typical database operation, rows will be inserted, updated, and deleted. This
will affect the table segments that store the rows themselves, and the index segments
associated with the tables. Chapter 5 included a discussion of extent management (how
space is allocated to segments within a tablespace) and segment space management
(how rows are allocated to blocks within a segment). You are strongly advised to use
locally managed tablespaces with automatic segment space management: these options
are enabled by default in the current release of the database and rely on bitmaps to track
extent allocation and block usage.
The bitmapped managed techniques are very efficient for allocating space as rows
are inserted and segments grow, but UPDATE and DELETE statements can still result
in problems that may make it necessary to reorganize the segments. Updates can
cause row migration, and deletions can result in wasted space.
Row Chaining and Migration
A chained row is a row that is stored in more than one block. This will occur when the
P AR T III
row is bigger than the block. If the block size is 4KB and the row is 5KB, there is no
choice; the row will be stored in two blocks. At system design time, this should have
been considered: the rows are too large for the blocks. This is sometimes a systems
analysis problem, caused by incorrect normalization resulting in an unnecessary
number of columns in the table, but it is often a design time problem. Perhaps the
table uses columns of type CHAR or LONG rather than VARCHAR2 or BLOB, or
perhaps the table could have been sliced vertically, into two or more tables. Whatever
the cause, row chaining is not the DBA’s problem. Row migration is a different matter.
Most of the Oracle primitive data types have variable length, and therefore most
tables have rows of variable length. As rows are updated, their length may change.
This means that the rows will get bigger.
The default settings for a table segment reserve 10 percent of each block as space
for rows to expand. If rows are only ever inserted or deleted, then this 10 percent is in
fact wasted space. But if rows are subjected to updates that make them bigger, then it
may not be sufficient. If the 10 percent free space has already been used by previous
updates and an update is made that will increase the size of another row, the entire
row must be relocated to another block, where there is room for the new version of
the row. This is a row migration. Clearly, this will impact on the performance of the
UPDATE statement: it becomes in effect a DELETE and an INSERT. A worse problem
arises with subsequent access to the row, because the index entries are not adjusted
when a row is migrated: the index keys always point to the row’s original location.
When a row is migrated, a stub (think of it as a forwarding address) is left behind,
which directs the session to the current location of the row. The result is that it takes
two table block reads to get the row, not one. Worse still, if a row has been migrated
once and is updated again, it may be migrated again. And again.
EXAM TIP Row migration is caused by UPDATE statements. INSERT and
DELETE can never cause row migration.
Row migration should be detected and fixed by the DBA. The ideal situation is to
prevent it in the first place, by adjusting the table settings to reserve an appropriate
amount of space for rows to expand. For example, if you know that on average the
rows of a certain table will double in size during its lifetime, the percentage of space
reserved should not be 10 percent but 50 percent. This can be set at table creation
time, or later:
alter table <table_name> pctfree 50;
Many times the information needed to set the PCTFREE correctly is not available, so
all you can do is correct the problem later. The simplest method is to move the table:
alter table <table_name> move [ <tablespace_name> ] ;
The underlying implementation of the MOVE command is a CREATE TABLE . . .
AS SELECT * FROM . . . command followed by some data dictionary magic to rename
the newly created table to the name of the original table, while maintaining the
connections to dependent objects. The row migration problem is fixed, because the
moved table will have all the rows freshly inserted. An INSERT can never cause row
migration. The problem is that all dependent indexes will be broken, because the
rows are in a new table, with new rowids; the indexes will have rowids that are now
invalid. The indexes will have to be reorganized next.
EXAM TIP Reorganizing a table with a MOVE will render all associated
indexes unusable.
TIP While a table move is in progress, the table can be queried but will be
locked against DML. You cannot move a table if there is an uncommitted
transaction against it.
A row migration problem can be detected with the ANALYZE command. This will
pass through the table counting the rows that are chained or migrated. To see the result,
query the CHAIN_CNT column in DBA_TABLES. To determine whether the rows are
chained or migrated, look at the AVG_ROW_LEN column: if this is less than the block
size, then the rows will be migrated rows; if it is greater, they will be chained rows.
EXAM TIP For most purposes, tables should be analyzed with procedures
in the DBMS_STATS package. An exception is counting chained and migrated
rows: these are detected only with the ANALYZE command.
