First, Oracle high-water level detailed

First, what is the waterline (high water Mark)?

All Oracle segments (segments, where, for ease of understanding, suggest segment as a synonym for a table) have a limit on the amount of data that can be accommodated in a paragraph, we call this cap "high water mark" or HWM. This HWM is a token used to indicate how many unused chunks of data have been allocated to this segment. HWM usually increase the amplitude of a 5 data block, in principle, HWM will only increase, will not shrink, even if the data in the table is all deleted, HWM or the original value, because of this feature, so that HWM is like a reservoir of the history of the highest water level, which is the original meaning of HWM, of course, can not say a reservoir without water It says the reservoir has a history of a maximum water level of 0. But if we use the TRUNCATE command on the table, the table's HWM will be reset to 0.

Second, the operation of the HWM database has the following effects:

A) A full table scan is usually read out until all of the HWM tags belong to the table database block, even if there is no data in the table.

b) Even if there is an idle database block below HWM, typing uses the APPEND keyword when inserting data, the HWM increases automatically when the data blocks above HWM are inserted.

Third, how to know the HWM of a table?

A) The table is analyzed first:

ANALYZE TABLE <tablename> Estimate/compute STATISTICS;

b) SELECT blocks, empty_blocks, num_rows

From User_tables

WHERE table_name = <tablename>;

Description

The BLOCKS column represents the number of database blocks that have been used in the table, that is, the waterline.

Empty_blocks represents a database block that is assigned to the table, but above the waterline, that is, a block of data that has never been used.

Let's take a 28672-line BIG_EMP1 table 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.

Attention:

BLOCKS + empty_blocks (700+323=1023) than dba_segments. Blocks less than 1 database blocks, because a database block is reserved for use as the segment header. Dba_segments. BLOCKS represents the number of all database blocks allocated to this table. User_tables. Blocks indicates the number of database blocks that have been used.

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----This table name does not have any database blocks that contain data, i.e. no data in the table

1 row selected.

sql> TRUNCATE TABLE big_emp1;

Statement processed.

One) sql> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

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.

Sql> SELECT Segment_name,segment_type,blocks

From Dba_segments

WHERE segment_name= ' BIG_EMP1 ';

Segment_name Segment_type BLOCKS

------------  ------------- ------

BIG_EMP1 TABLE 512

1 row selected.

Attention:

The truncate command reclaims the free space generated by the Delete command, noting that the space allocated by the table is reduced from the original 1024 blocks to 512 blocks.

To preserve the free space generated by the Delete command, you can use the Truncate TABLE BIG_EMP1 reuse STORAGE.

With this command, the table will also be the original 1024 blocks.

Iv. high watermark HWM in Oracle table segments

In the storage of Oracle data, storage space can be imagined as a reservoir, the data is imagined as water in the reservoir. The position of the water in the reservoir is a line called the watermark, in Oracle, this line is called the High watermark (High-warter mark, HWM). When the database table was just established, because there is no data, so this time the watermark is empty, that is, HWM is the lowest value. When the data is inserted, the high watermark will go up, but there is also a feature here, that is, if you delete the data using a DELETE statement, the data is deleted, but the high watermark is not lowered, or you just deleted the data before the high water level. In other words, this high watermark will only rise in daily additions and deletions and will not fall.

Let's talk about the features of the SELECT statement in Oracle. The SELECT statement scans the data in the table one at a time, but how much data storage blocks are scanned, this does not mean how much data is in the database, and Oracle scans such large chunks of data, but Oracle scans the blocks below the high watermark. Now imagine that if you had just created an empty table and you did a select operation, because the high watermark HWM in the lowest 0 position, no data block would have to be scanned and the scan time would be very short. And if you first insert 10 million data at this time, then delete the 10 million data with the DELETE statement. Since 10 million data is inserted, the high watermark at this time is 10 million data here. When the 10 million data is deleted, the high watermark is still 10 million data, because the DELETE statement does not affect the high watermark line. This time again with the SELECT statement to scan, although this time there is no data in the table, but because the scan is based on the high watermark, so you need to 10 million of data storage space to be scanned once, that is, the time required for this scan and scan 10 million data will take as much time. So sometimes people always say, how my table is not a few data, but still so slow, this time in fact, the mystery is the high water mark here.

There is no way to let the high water mark down, in fact, there is a relatively simple way, that is, using the TRUNCATE statement to delete data. When using the TRUNCATE statement to delete data from a table, it is similar to re-establishing the table, not only deleting the data, but also restoring the HWM to 0. So if you need to empty the table, use the TRUNCATE statement to delete the table when it is possible to use the TRUNCATE statement to delete the data, especially the temporary storage table that has a potentially large amount of data.

In manual segment space management (Manual Segment spaces Management), there is only one HWM in the segment, but the automatic segment space management that is added in Oracle9irelease1 (Automatic Segment Space Management), there is a concept of a low HWM. Why is there a HWM and a low HWM, this is due to the characteristics of automatic section space management. In the method section space management, when the data is inserted, if it is inserted into the new data block, the data block will be automatically formatted to wait for data access. In automatic section space management, when data is inserted into a new block, the data block is not formatted, but the block is formatted the first time it is accessed by the data block. So we need a watermark to indicate which blocks have been formatted. This watermark is called low HWM. Generally speaking, low HWM must be below or equal to HWM.

V. Fix high watermark for Oracle table

In Oracle, performing a delete operation on a table does not degrade the table's high watermark. The full table scan will always read all blocks below the high watermark mark in one segment (extent). If you do not lower the high watermark mark after you perform a delete operation, the performance of the query statement will be poor. The following methods can reduce the high watermark markings.

1. Execute table REBUILD command ALTER TABLE table_name move;

(Online transfer table space ALTER TABLE ...) MOVE tablespace ... ALTER TABLE ... The move does not follow the parameters, not with the parameter table or in the original table space, move after remember to rebuild the index. If you want to continue to add data to this table later, there is no need to move, just freed up the space, only this table, other tables or segment cannot use the space)

2. Execute ALTER TABLE table_name shrink space; Note that this command is new for Oracle 10g and must be allowed to move the ALTER TABLE table_name enable row movement before executing the instruction;

3. Copy the data to be persisted to the temporary table t,drop the original table, and then rename the temporary table T as the original table

4.emp/imp

5.alter Table table_name Deallocate unused

6. Try to truncate it.

Ext.: http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html

First, Oracle high-water level detailed