Database Reference
In-Depth Information
High-water Mark of Newly Created Table
High-water Mark After Inserting 10,000 Rows
High-water Mark After Deleting 5,000 Rows
Figure 10-1.
Depiction of an HWM
Figure
10-1
shows that the HWM starts at the first block of a newly created table. As data is placed into the table
over time and more blocks get used, the HWM rises. If we delete some (or even
all
) of the rows in the table, we might
have many blocks that no longer contain data, but they are still
under
the HWM, and they will remain under the HWM
until the object is rebuilt, truncated, or shrunk (shrinking of a segment is a feature introduced in Oracle 10
g
that is
supported only if the segment is in an ASSM tablespace).
The HWM is relevant since Oracle will scan all blocks under the HWM, even when they contain
no
data, during a
full scan. This will impact the performance of a full scan—especially if most of the blocks under the HWM are empty.
To see this, just create a table with 1,000,000 rows (or create any table with a large number of rows), and then execute a
SELECT COUNT(*)
from this table. Now,
DELETE
every row in the table and you will find that the
SELECT COUNT(*)
takes
just as long to count
0
rows as it did to count 1,000,000 (or longer, depending on if you need to clean out the block,
refer to the “Block Cleanout” section of Chapter 9). This is because Oracle is busy reading all of the blocks below the
HWM to see if they contain data. You should compare this to what happens if you used
TRUNCATE
on the table instead
of deleting each individual row.
TRUNCATE
will reset the HWM of a table back to zero and will truncate the associated
indexes on the table as well. If you plan on deleting every row in a table,
TRUNCATE
—if it can be used—would be the
method of choice for this reason.
■
Keep in mind that a
TRUNCATE
statement cannot be rolled back, nor will any triggers fire (if they exist) on the
table. therefore, before truncating, ensure you permanently want to remove the data since it can't be undone.
Caution
