High-level line on Oracle
High water Mark's name is a high-level line, usually relative to a table, when a table has data inserted continuously, high water mark value is increasing, the select query that scans the whole table is to water mark as the end of , although there may be only one row of records in the table. It is the value that the space of the table has been expanded to.
The reason for HWM interest or due to the afternoon of that resize event, hehe
Create a test table
--Create table
CREATE TABLE TEST
(
A CHAR (1024)
);
SELECT * from dba_extents where segment_name= ' TEST '
See figure: The table is created in accordance with initialization parameters, the default is Initial extent 64k, contains 8 pieces of data.
Insert 1000 original record, 1000*1k approximately equal to 1M
Declare
--Local variables
I integer;
Begin
--Test statements here
For I in 1..1000
Loop
INSERT into TEST VALUES (I);
End Loop;
COMMIT;
End
Select SUM (BYTES) from user_extents where segment_name= ' TEST '
SELECT * from dba_extents where segment_name= ' TEST '
See figure after:
Perform a delete operation
Delete * from Test;
After the execution of the diagram as above, no changes have taken place
Then perform the truncate operation to restore the high level line of the table to the original initialization stage
TRUNCATE TABLE TEST;
See figure after:
Conclusion:
After the data has been deleted, the high-level line (High-water mark, HWM) has not been reset but the space is no longer used, again the query will still read the previous block to find out if there is any data available. The truncated table will reset the HWH, telling the space not to save the data.
Other extended issues:
In inserting the test data, if first inserts 100, 200, the database allocation interval is assigns in 64k, when inserts to 1000, the new allocation interval suddenly becomes 1M.
I have repeatedly looked at the storage definition of the table
Storage
(
Initial 64K
Minextents 1
Maxextents Unlimited
);
Just without the definition of next extent, and looking for a long 9i, 10g official document, found that the interpretation of next extent is still in the earlier version of the definition of how much distribution, baffled.
or Google found the introduction:
16 64K extent are assigned first,
0-15 extents each size is 64K total size 1M
16-79 extents each size is 1M total size 63M--The above two sizes equals 64M
80-199 extents each size is 8M total size 960M--The above three items size equals 1024M = 1G
200-.. Extents each size is 64M