Shulou(Shulou.com)05/31 Report--

What is the difference between alter table move and alter table shrink space? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

What move and shrink have in common

1. Contraction section

2. Eliminate partial row migration

3. Elimination of space debris

4. Make the data more compact

Shrink

Syntax:

Alter table TABLE_NAME shrink space [compact | cascate]

Two phases of segment shrink execution:

1. Data reorganization (compact):

Through a series of insert and delete operations, the data is arranged in front of the segment as far as possible. In the process, you need to put a RX lock on the table, that is, only on the rows that need to be moved.

Because it involves the change of rowid, it needs enable row movement. At the same time, disable should be based on rowid trigger. This process has little impact on the business.

2. HWM adjustment: the second stage is to adjust the HWM location and release free data blocks.

This process requires an X lock on the table, which blocks all DML statements on the table. It may have a greater impact on systems with particularly busy business.

Note: the shrink space statement is executed in both phases.

Shrink space compact only executes the first phase.

If the system business is busy

You can first execute shrink space compact to reorganize data, and then execute shrink space to reduce HWM to release free data blocks when the business is not busy.

Give an example

Alter table TABLE_NAME shrink space compact; only defragments and does not reclaim space.

Alter table TABLE_NAME shrink space; defragmates and reclaims space.

Alter table TABLE_NAME shrink space cascade; defragmentation reclaims space and collates (an index, for example) along with cascading objects of the table.

-Partition table

Alter table ticket modify PARTITION P28071 shrink space cascade

Advantages of shrink

1. Can be executed online

two。 You can use the parameter cascade while shrinking the index on the table

3. The execution will not cause the index to fail.

4. You can avoid taking up a lot of table space during alter table move execution (if the table size is 10G, then alter table move needs about 10G of space to execute).

Conditions for use of shrink:

Use steps

1. Alter table t1 enable ROW MOVEMENT

2. Shrink operation

3. Alter table t1 disable ROW MOVEMENT

Shrink usage restrictions:

Shrink operation needs to satisfy that tablespaces are managed locally and segmented automatically (10g, 11g default is like this)

Shrink cannot be used in the following situations:

IOT index organization table

The base table of materialized views created with rowid

Table with functional index

Large objects of type SECUREFILE

Compression table

Move

Problems solved by move

1. Move a table from the current tablespace to another tablespace:

Alter table t move tablespace tablespace_name

Alter table TABLE_NAME move;-- move within the original tablespace.

2. Change the storage parameters of the existing block in table, such as:

Alter table t move storage (initial 30k next 50k)

3. In addition, move operation can also be used to solve the problem of row migration in table.

Some considerations for using move:

1. Rebuild is required for index on table:

As we discussed earlier, the rowid of the data has changed after the move operation. We know that index uses rowid to fetch data rows, so index on table must be rebuild.

Alter index index_name rebuild online

2. Lock on table during move

When we move table, we can find that exclusive lock is added to table by querying the v$locked_objects view.

3. About the use of space in move:

When we use alter table move to reduce the HWM of table, it is important to note that the current tablespace needs to have twice as much free space as table.

The differences between move and hrink are:

1. After move, the position of the table in the tablespace will certainly change, and it may move forward or backward. Generally speaking, if there is enough space in the tablespace in front of the table to accommodate the table, move forward, otherwise move backward.

2. After hrink, the position of the table in the tablespace will not change, that is, the position of the segment header of the table will not change.

3. Move will move the high water level, but will not release the application space. It is an operation below the high water level (below HWM).

4. Shrink space will also move the high water level, but it will also free up the space for application, which can be done both above and below the high water level (below and above HWM).

5. When using move, the ROWID of some records will be changed, so the index will become invalid after MOVE, and REBUILD is needed.

6. When using shrink space, the index will be maintained automatically. If you do compression when the business is busy

You can shrink space compact first to compress the data without moving the HWM, and then shrink space to move the HWM when you are not busy.

7. Shrink can compress the index separately, and alter index xxx shrink space can compress the index. In addition, specifying Shrink space cascade when compressing the table will compress the index at the same time

test

SQL >

SQL > drop table test purge

SQL > drop table test2 purge

SQL >

SQL > create table test (id number) storage (initial 10m next 1m) tablespace users

SQL > create table test2 (id number) storage (initial 10m next 1m) tablespace users

SQL >

SQL > insert into test values (1)

SQL > insert into test2 values (1)

SQL >

SQL > analyze table test compute statistics

SQL > analyze table test2 compute statistics

SQL >

SQL > col SEGMENT_NAME for A10

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2')

SEGMENT_NA EXTENTS BLOCKS INIT

TEST2 3 1280 10

TEST 3 1280 10

-- two tables, the number of partitions and blocks of the original request

SQL > col TABLE_NAME for A10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2')

TABLE_NAME BLOCKS EMPTY_BLOCKS

-

TEST 46 1234

TEST2 46 1234

-- two tables, 46 blocks actually used and 1234 blocks free.

SQL >

SQL > begin

2 for i in 1..100000 loop

3 insert into test values (I)

4 insert into test2 values (I)

5 end loop

6 end

7 /

SQL >

SQL > analyze table test compute statistics

SQL > analyze table test2 compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2')

SEGMENT_NA EXTENTS BLOCKS INIT

TEST2 3 1280 10

TEST 3 1280 10

After inserting a large amount of data, the original number of applied partitions and blocks of the two tables remain unchanged.

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2')

TABLE_NAME BLOCKS EMPTY_BLOCKS

-

TEST 174 1106

TEST2 174 1106

After inserting a large amount of data, the number of blocks actually used by the two tables changed, using 174blocks and idle 1106 blocks. 174 is the high water mark.

SQL >

SQL >

SQL > delete from test where rownum delete from test2 where rownum

SQL > analyze table test compute statistics

SQL > analyze table test2 compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2')

SEGMENT_NA EXTENTS BLOCKS INIT

TEST2 3 1280 10

TEST 3 1280 10

-- after deleting a large amount of data, the original number of applied partitions and blocks of the two tables remain unchanged

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2')

TABLE_NAME BLOCKS EMPTY_BLOCKS

-

TEST 174 1106

TEST2 174 1106

After deleting a large amount of data, there is no change in the number of blocks actually used by the two tables. That is, delete will not release space.

SQL >

SQL >

SQL > alter table test move

SQL >

SQL > analyze table test compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2')

SEGMENT_NA EXTENTS BLOCKS INIT

TEST2 3 1280 10

TEST 3 1280 10

-- do the move operation on the test table, the original application partition and the number of blocks remain unchanged.

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2')

TABLE_NAME BLOCKS EMPTY_BLOCKS

-

TEST 95 1185

TEST2 174 1106

-- do move operation on test table, and the number of blocks actually used changes.

Move will move the high water level, but will not release the application space, is below the high water level (below HWM) operation.

SQL >

SQL >

SQL > alter table test2 enable row movement

SQL > alter table test2 shrink space

SQL > analyze table test2 compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in ('TEST','TEST2')

SEGMENT_NA EXTENTS BLOCKS INIT

TEST2 1 104 10

TEST 3 1280 10

-- changes have taken place in test2 table, shrink operation, original application partition and number of data blocks

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST','TEST2')

TABLE_NAME BLOCKS EMPTY_BLOCKS

-

TEST 95 1185

TEST2 79 25

-- shrink operation on test2 table. The actual number of blocks used has changed.

Shrink space will also move the high water level, but it will also free up the space for application, which can be done both above and below the high water level (below and above HWM).

After reading the above, have you mastered the difference between alter table move and alter table shrink space? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.