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

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

1. 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 recycle space, while alter table TABLE_NAME shrink space; defragmates and reclaims space. Alter table TABLE_NAME shrink space cascade; defragmentation reclaims space and collates (such as indexes) alter table pt_table modify PARTITION P1 shrink space cascade; partitioned tables together with cascading objects of the table

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).

II. Move

1. The functions of move table:

①: move one table from the current tablespace to another tablespace:

②: to change the storage parameters of table's existing block, such as alter table t move storage (initial 30k next 50k)

The ③: move operation can also be used to solve the problem of row migration in table.

2. Some precautions when using move:

①: index on table requires rebuild:

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

②: lock on table when move

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

③: 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.

3. 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

Fourth, the actual combat experiment:

Experimental environment: Oracle11.2.0.4

[oracle@dbs] $sqlplus'/ as sysdba'SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 10 14:44:59 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL >

1. Create two test tables: test_1 and test_2

SQL > create table test_1 (name varchar2 (10)) storage (initial 500m next 1m); Table created.SQL > create table test_2 (name varchar2 (10)) storage (initial 500m next 1m); SQL > create index idx_test1 on test_1 (name); Index created.SQL > create index idx_test2 on test_2 (name); Index created.

2. Insert data and collect statistical information:

SQL > insert into test_1 values ('zhang'); SQL > insert into test_1 values (' zhang'); SQL > insert into test_2 values ('zhang'); SQL > insert into test_2 values (' zhang'); SQL > exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_1',cascade= > TRUE); PL/SQL procedure successfully completed.SQL > exec dbms_stats.gather_table_stats (ownname = > ADMIN',tabname = > 'TEST_2',cascade= > TRUE); PL/SQL procedure successfully completed.SQL >

3. View the blocks information of the two tables:

SQL > select B.SEGMENT_NAME, B.blocksMagi B.Backtim * 8096 / 1024 / 1024, A.BLOCKSMagazine A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_1 64512 498.09375 222 1.71405029 0TEST_2 64512 498.09375 222 1.71405029 0SQL > select TABLE_NAME BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST_1','TEST_2') TABLE_NAME BLOCKS EMPTY_BLOCKS-- TEST_1 22220TEST_2 2220SQL > select owner,segment_name Sum (bytes) / 1024 MB from dba_segments where tablespace_name='TEST' and segment_type like'% TAB%' group by owner,segment_name order by MB desc OWNER SEGMENT_NAME MB -ADMIN TEST_2 504ADMIN TEST_1 504SQL > select index_name Table_name,status from user_indexes where table_name in ('TEST_1','TEST_2') -Index status is normal INDEX_NAME TABLE_NAME STATUS-- IDX_TEST2 TEST_2 VALIDIDX_TEST1 TEST_1 VALIDSQL >

As you can see from the above, since we pre-allocated 500m to the two tables, they now have a total of 64512 blocks, a total of 500m, while actually occupying only 222m

4. Delete the data of the two tables, collect statistics, and then view the blocks information of the two tables:

SQL > delete from test_1 where rownum delete from test_2 where rownum exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_1',cascade= > TRUE); PL/SQL procedure successfully completed.SQL > exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_2',cascade= > TRUE); PL/SQL procedure successfully completed.SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in ('TEST_1','TEST_2') TABLE_NAME BLOCKS EMPTY_BLOCKS-- TEST_1 22220TEST_2 2220SQL > select B.SEGMENT_NAME B.blocksAND A.TABLE_NAME * 8096 / 1024 / 1024, A.Block WHERE TABLE_NAME in * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 2 blocksB WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_1 64512 498.09375 222 1.71405029 0TEST_2 64512 498.09375 222 1.71405029 0SQL > SQL > select index_name Table_name,status from user_indexes where table_name in ('TEST_1','TEST_2') -Index status is normal INDEX_NAME TABLE_NAME STATUS-- IDX_TEST2 TEST_2 VALIDIDX_TEST1 TEST_1 VALID

As can be seen from the above, although the data of the table has been deleted, the space has not been released, including above and below the high water level line. (the space above the high water mark is the pre-allocated space minus the actual occupied space

The space below the high water mark is the space actually occupied by the data-- because the delete will not be space-free, that is, the high water level will always exist unless the newly inserted data overwrites it)

5. Move the test_1 table:

SQL > alter table test_1 move;Table altered.SQL > exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_1',cascade= > TRUE) PL/SQL procedure successfully completed.SQL > select B.SEGMENT_NAME, B.blocksMagi B.Backtim * 8096 / 1024 / 1024, A.BLOCKSMagazine A. blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 2 USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_2 64512 498.09375 222 1.71405029 0TEST_1 64384 497.105469 35 .270233154 0SQL > select index_name Table_name,status from user_indexes where table_name in ('TEST_1','TEST_2') INDEX_NAME TABLE_NAME STATUS-- IDX_TEST2 TEST_2 VALIDIDX_TEST1 TEST_1 UNUSABLE

As can be seen from the above, after move the table, the actual space occupied by the table has been released, but the pre-allocated space has not changed, which means that the move operation will release space below the high water level, but will not release the space above the high water level; at the same time, the index of the test_1 table has become invalid!

6. Shrink space the test_2 table:

SQL > alter table test_2 enable row movement;Table altered.SQL > alter table test_2 shrink space;Table altered.SQL > exec dbms_stats.gather_table_stats (ownname = > 'ADMIN',tabname = >' TEST_2',cascade= > TRUE) PL/SQL procedure successfully completed.SQL > select B.SEGMENT_NAME, B.blocksMagi B.Backtim * 8096 / 1024 / 1024, A.BLOCKSMagazine A. blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, 2 USER_SEGMENTS B WHERE TABLE_NAME in ('TEST_1','TEST_2') AND A.TABLE_NAME = B.SEGMENT_NAME SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS -TEST_2 40. 308837891 1. 007720947 0TEST_1 64384 497.105469 35 .270233154 0SQL > SQL > select index_name Table_name,status from user_indexes where table_name='TEST_2' INDEX_NAME TABLE_NAME STATUS-- IDX_TEST2 TEST_2 VALIDSQL >

-as can be seen from the above, all the pre-allocated space has been released, indicating that shrink space will also move the high water level, but it will also release the applied space, which can be operated above and below the high water level (below and above HWM), and the index will not be invalidated.

Note:

①: when using move, the ROWID of some records will be changed, so the index will become invalid after MOVE, and REBUILD is required.

②: when using shrink space, the index is automatically maintained. 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.

③: indexes can also be compressed. When compressing a table, specify Shrink space cascade to compress the index at the same time, or alter index xxx shrink space to compress the index.

④: shrink space needs to be managed automatically in the tablespace, so tables on the system tablespace cannot be shrink space.

-add that move can also really compress and allocate space by specifying the STORAGE parameter. :

SQL > alter table test_1 move storage (initial 1m)

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.