Databases Reference
In-Depth Information
The only ways to reset the HWM are:
F
TRUNCATE
: Truncating the table will reset the HWM, but all the data in the table will
be lost. There is an option to de-allocate the space—
REUSE
STORAGE
—this is useful
when we want to unload all the data and reload it keeping the same segments,
resulting in a faster loading process.
F
ALTER
TABLE
MOVE
: The table can be moved, but all indexes will be marked
unusable and must be rebuilt.
F
EXPORT
+
DROP
+
IMPORT
: You can export the table data, drop the table, and
import the data back in the database.
Please note that the
ALTER
TABLE
…
DEALLOCATE
UNUSED
command de-allocates database
blocks above the High-Water Mark.
PctFree, PctUsed, and FREELISTs
When we create a table,
PCTFREE
and
PCTUSED
are two parameters of the
STORAGE
clause— they are useful in specifying the behavior of the database blocks in the segment.
PCTFREE
defines the minimum percentage of a database block to be reserved for future
updates on the rows in the data block. If we define a
PCTFREE
of 10, then the database
will fill the data block with new rows until the data block is filled up to 90 percent of its size;
then the data block is removed from the
FREELIST
, so it's not used for new inserts.
PCTUSED
sets the minimum percentage of a database block above which a database block
can return on the
FREELIST
. In the previous example, after we have reached the
PCTFREE
,
the database block is removed from the
FREELIST
. We have set a
PCTUSED
of 45 on table
creation, so when we delete rows from the table and when the database block reaches a
percentage of usage space less than 45 percent, the database block is put in the
FREELIST
.
Now the subsequent inserts will put rows in the data block, until the free space reaches the
PCTFREE
, and so on.
Having a table with a small
PCTFREE
parameter will lead to blocks with more data in it, so
FTS operations will benefit from this situation, accessing the same number of rows reading
fewer database blocks. Please note that setting small values to the
PCTFREE
parameter
could lead to
ROW
MIGRATION
problems during subsequent updates to the rows.
PCTUSED
has no meaning, when using Automatic Segment Space Management (ASSM).
See also
F
Recipes in
Chapter 3
,
Optimizing Storage Structures
about indexing to avoid
Full Table Scans
F
In
Chapter 8
,
Other Optimizations
the
Loading data with SQL Loader and Data Pump
recipe
explains the EXPORT/IMPORT process and data loading in more depth
F
For row chaining refer to the
Avoiding row migration
recipe in
Chapter 3
