Shulou(Shulou.com)06/01 Report--

When using delete table data, the space cannot be freed, and table contraction can be used to free the table space.

Note: when the delete table has a large amount of data, you should pay attention to undo. You can use: alter table emp nologging; to make it not produce logs.

I. the growth mode of the table

When the table is created, the intervals that make up the table are filled as records are inserted, and if automatic expansion is enabled, new intervals are assigned when the intervals are filled. Assume high water

The bit line moves from the leftmost end to the right end as the record increases, and when it reaches the end of the bottom interval, the new interval will be assigned.

Second, the principle of table contractibility

As the record increases, the high water mark continues to move to the right, and the deletion of the record does not cause the high water mark to move back (left).

Although the free space after deleting the record (to the left of the high water mark) can be used, its sparsity leads to free space.

Note: the time spent on a complete table scan will not be reduced by the reduction (deletion) of records.

Third, use alter table tbname shrink space to shrink the watch segment

1. Realization principle

Essentially construct a new table (internally represented by a series of DML operations, that is, inserting the copy into the new location and deleting the record of the original location)

Moving the record in the data block near the end (right end) to the free space at the beginning (left end) (DML operation) does not cause a DML trigger

When all possible movements are completed, the high water mark will move to the left (DDL operation)

Free space to the right of the new high watermark is released (DDL operation)

two。 Precondition for realization

Line record transfer (enable row movement) must be enabled

Only applicable to heap tables and located in automatic segment space management tablespaces (heap tables include: standard tables, partition tables, materialized view containers, materialized view log tables)

3. A table that cannot shrink.

Cluster table

Tables with LONG type columns

LOB section (although the table itself can be shrunk), note that future versions of 10gR2 support shrinking of LOB segment

A table with a materialized view based on submission (because triggers are disabled)

Tables with rowid materialized views (because rowid has changed)

IOT mapping table IOT overflow segment

Index function-based tables

Heap table with row record transfer not enabled

4. Advantages of segment contraction

Improve cache utilization and OLTP performance

Reduce disk I / O, improve access speed, and save disk space

Segment contraction is online, and indexes are maintained during segment contraction, requiring no additional disk space

5. Two options

Cascade: shrink the table and its indexes and move the high water mark to free up space

Compact: just shrink the tables and indexes, don't move the high water mark, don't free up space.

Alter table tbname shrink space is equivalent to the parameter cascade.

Hands-on exercises:

1. Delete the data in the table first

SQL > alter table FEE_COLLECT_TMP nologging;-sets the table not to generate undo

SQL > delete from FEE_COLLECT_TMP where CUTOFFDAY='20170501'

SQL > commit

2. Contraction table

SQL > alter table FEE_COLLECT_TMP enable row movement;-- > Open row movement

SQL > alter table FEE_COLLECT_TMP shrink space;-- > shrink successful

3. Grammar summary:

ALTER TABLE ENABLE ROW MOVEMENT-- > prerequisite ALTER TABLE SHRINK SPACE [| COMPACT | CASCADE] ALTER TABLE SHRINK SPACE COMPCAT -- > shrink tables and indexes, do not move high water mark, do not free space ALTER TABLE SHRINK SPACE;-- > shrink table, lower high water mark ALTER TABLE SHRINK SPACE CASCADE -- > shrink the table, lower the high water mark, and the related index also needs to shrink ALTER TABLE MODIFY LOB (lob_column) (SHRINK SPACE) -- > contraction of LOB segment ALTER INDEX IDXNAME SHRINK SPACE;-> contraction of index segment, same as table segment

#

1. Shrink regular tables and batch scripts

Select'alter table'| | table_name | | enable row movement;' | | chr (10) | | 'alter table' | | table_name | | 'shrink space;' | | chr (10) from user_tables

Select'alter index'| | index_name | | 'shrink space;' | | chr (10) from user_indexes

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.