About Oracle's high-level line ____oracle

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.