High Water Mark (from huaihe0410]

What is waterline?(High Water Mark )?
----------------------------
All the oracle segments (segments here, for ease of understanding, we recommend that you use segment as a synonym for the table) have an upper limit to accommodate data within the segments, we call this upper limit "high water mark" or HWM. This HWM is a tag used to indicate how many unused data blocks have been allocated to this segment. HWM usually increases by five data blocks at a time. In principle, HWM only increases and does not shrink. Even if all the data in the table is deleted, HWM is the original value, this makes HWM very similar to the historical highest water level of a reservoir, which is the original meaning of HWM. Of course, it cannot be said that a reservoir has no water, but the historical highest water level of the reservoir is 0. However, if we use the truncate command on the table, the HWM of the table will be reset to 0.

HWM database operations have the following effects:
A) Full table scan usually reads all the database blocks in the table until the HWM mark, even if the table does not have any data.
B) even if there are idle database blocks below HWM AND THE append keyword is used for data insertion, the data blocks above HWM are used for data insertion, And the HWM will automatically increase.

How do I know the HWM of a table?
A) analyze the table first:
Analyze table <tablename> ESTIMATE/compute statistics;
B) SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = <tablename>;

The BLOCKS column indicates the number of database BLOCKS used in the table, that is, the waterline.
EMPTY_BLOCKS indicates the database block that is allocated to the table but above the waterline, that is, the data block that has never been used.

Let's take a BIG_EMP1 table with 28672 rows as an example:
1) SQL> SELECT segment_name, segment_type, blocks
FROM dba_segments
WHERE segment_name = 'Big _ emp1 ';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
---------------------------------------------------------------
BIG_EMP1 TABLE 1024 2
1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

3) SQL> SELECT table_name, num_rows, blocks, empty_blocks
FROM user_tables
WHERE table_name = 'Big _ emp1 ';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
BIG_EMP1 28672 700 323
1 row selected.

Note:
BLOCKS + EMPTY_BLOCKS (700 + 323 = 1023) has fewer database BLOCKS than DBA_SEGMENTS.BLOCKS because a database block is reserved as the segment header. DBA_SEGMENTS.BLOCKS indicates the number of all database blocks allocated to this table. USER_TABLES.BLOCKS indicates the number of used database blocks.

4) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) |
DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) "Used"
FROM big_emp1;
Used
----------
700
1 row selected.

5) SQL> DELETE from big_emp1;
28672 rows processed.

6) SQL> commit;
Statement processed.

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

8) SQL> SELECT table_name, num_rows, blocks, empty_blocks
FROM user_tables
WHERE table_name = 'Big _ emp1 ';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
BIG_EMP1 0 700 323
1 row selected.

9) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) |
DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) "Used"
FROM big_emp1;
Used
----------
0 -- the table name does not contain any database block to accommodate data, that is, the table does not have data.
1 row selected.

10) SQL> TRUNCATE TABLE big_emp1;
Statement processed.

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

12) SQL> SELECT table_name, num_rows, blocks, empty_blocks
2> FROM user_tables
3> WHERE table_name = 'Big _ emp1 ';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
BIG_EMP1 0 0 511
1 row selected.

13) SQL> SELECT segment_name, segment_type, blocks
FROM dba_segments
WHERE segment_name = 'Big _ emp1 ';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
---------------------------------------------------------------
BIG_EMP1 TABLE 512 1
1 row selected.

Note:
The TRUNCATE command recycles the free space generated by the delete command. Note that the space allocated to the table is reduced from the original 1024 blocks to 512 blocks.
To retain the free space generated by the delete command, you can use
Truncate table big_emp1 REUSE STORAGE
After using this command, the table will still be the first 1024 blocks.

**************************************** ********************

  HWM in the Oracle table segment

In the storage of Oracle data, you can think of the storage space as a reservoir, and the data as water in the reservoir. There is a line of water in the reservoir called a waterline. in Oracle, this line is called a High-warter mark (HWM ). When a database table is created, there is no data, so the watermark line is empty at this time, that is, HWM is the lowest value. When the data is inserted, the High-level line will rise, but here there is also a feature, that is, if you use the delete statement to delete the data, although the data is deleted, however, the high water level has not been lowered, but it is still as high as before you deleted the data. That is to say, this high water level line will only rise in daily addition, deletion operations, and will not fall.

The following describes the features of Select statements in Oracle. The Select statement scans the data in the table once, but how many data storage blocks are scanned does not mean that Oracle scans such a large data block as to how much data is in the database, instead, Oracle scans data blocks below the high waterline. Now imagine that if you have just created an empty table and performed a Select operation, the HWM of the high watermark line is at the lowest position, therefore, no data blocks need to be scanned, and the scanning time is very short. If at this time, you first Insert 10 million data records, and then use the delete statement to delete the 10 million data records. Because 10 million pieces of data are inserted, the high water level line is here 10 million pieces of data. Later, when the 10 million data records were deleted, because the delete statement does not affect the high waterline, the high waterline is still in the 10 million data records. At this time, the select statement is used again for scanning. Although there is no data in the table at this time, the scanning is based on the high water level, therefore, you need to scan the storage space of 10 million pieces of data once. That is to say, the time required for this scan is the same as that required for scanning 10 million pieces of data. So sometimes some people often say that my table does not have a few data records, but it is still so slow. In fact, the mystery is the high water level here.

Is there a way to reduce the high water level line? In fact, there is a simple method, that is, using the TRUNCATE statement to delete data. When the TRUNCATE statement is used to delete the data of a table, it is similar to re-establishing the table, not only deleting the data, but also clearing the HWM and restoring it to 0. Therefore, if you need to clear the table, you can use the TRUNCATE statement to delete the table when using the TRUNCATE statement to delete the data, especially the temporary storage table with a large amount of data.

In Manual Segment Space Management, there is only one HWM in the Segment, but in the Automatic Segment Space Management added in Oracle9iRelease1, there is a low HWM concept. Why is there another low HWM with HWM? This is caused by the feature of automatic segment space management. When data is inserted into a new data block, the data block is automatically formatted and waiting for data access. In automatic segment space management, after data is inserted into a new data block, the data block is not formatted. Instead, the block is formatted when the data block is accessed for the first time. So we need another waterline to mark the formatted block. This waterline is called low HWM. In general, low HWM must be lower than or equal to HWM.

**************************************** ******** Modify the high watermark line of an ORACLE table 

In ORACLE, deleting a table does not reduce the high watermark line of the table. The full table scan always reads allMark below high waterline. If the high watermark mark is not lowered after the delete operation is executed, the query statement performance will be poor. The following methods can reduce the mark of the high waterline.

1. Execute the table reconstruction command alter tableTable_nameMove;
(Online transfer tablespace alter table... Move tablespace...
Alter table... MOVE is followed by parameters,
If the parameter table is still in the original tablespace, remember to recreate the index after moving
If you want to add data to the table later, move is not necessary,
Only the space released is used for this table. Other tables or segments cannot use this space.
)

2. Execute alter tableTable_nameShrink space; note: this command is a new feature for Oracle 10 Gb. Before executing this command, you must allow rows to move the alter tableTable_nameEnable row movement;
3. copy the data to the temporary table t, drop the original table, and rename the temporary table t as the original table
4. emp/imp
5. alter table table_name deallocate unused
6. truncate as much as possible