Detailed description of Oracle high level line

1. What is a 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.
 

 
Ii. operations on the HWM database have the following impacts:
 
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.
 

3. 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>;
 
Note:
 
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
 
----------------------------------------
 
BIG_EMP1 TABLE 1024
 
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 one database block less than DBA_SEGMENTS.BLOCKS because one 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.

1 2 3 Next Page