Databases Reference
In-Depth Information
However, in data warehouse environments, the use of FTS operations is often preferable.
This is because when we have a larger database block, we can read many rows in a block
and even subsequent database blocks—in one operation—by setting the parameter
DB_FILE_MULTIBLOCK_READ_COUNT
(at the instance or session level). This parameter
controls the number of database blocks read in one I/O operation. Obviously, there is a
limit—imposed by the operating system—to the maximum size of bytes (or number of OS
blocks) that can be read with a single I/O operation. The use of this parameter influences
even the optimizer—if it's less expensive to read all the rows in a table than using an index,
the optimizer will use an FTS even if there are usable indexes in place.
There's more...
We need to understand how database rows are stored in database blocks, and some
storage parameters used when creating a table that affects this conduct.
The High-Water Mark
The High-Water Mark (HWM) is recorded in the segment header block, which indicates
the last used block in the segment.
You need to differentiate between unused blocks above and below the HWM. If we think of
the segments as a tank, we start filling the segments from the bottom up; if we draw a line
when the level increases, then that is the HWM, representing the highest level reached by
water. If we empty the tank a bit, the HWM will remain at the same level.
When applied to tables, there is unused space (empty blocks) above the HWM; this space
of the segment is never used.
In our example, in step 6 we have deleted many rows from the
MY_SALES_2
table, resulting
in a lot of unused space below the HWM. We have deleted the rows at many different points
in the blocks, so there are many unused rows in a block, but there aren't empty blocks at all.
We can inspect the situation with the following queries:
SELECT BLOCKS, EMPTY_BLOCKS
FROM DBA_TABLES
WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');
Please remember that a
DELETE
operation
won't reset the High-Water Mark, ever!
The HWM is very important in FTS operations—the database will read every single block in
the table segments, which are below the HWM, even if they are not used. So keep the HWM
as low as possible, to avoid the unnecessary scan of unused database blocks.
