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

This article will explain in detail what is the difference between alter table move and shrink space, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Move

Case study:

After a colleague deleted an extra 300w pieces of data from a key table, the program became unusually slow. The analysis shows that there should be too many table space fragments and the old index is too inefficient.

Execute the following two sentences:

Alter table ycsbt_qyygxx_jb move

Alter index R_SBXX_YCSBD_FK rebuild online

The effect is very obvious.

Deltete does not release tablespaces, but it can be reused, that is, inserts can fill the holes. Of course, in real applications, there is a situation that often deletes and rarely inserts, so there is the feasibility of releasing tablespaces to optimize the database. Truncate has the defect that it can not be conditional, so naturally I think of the method of removing tablespaces with alter table move. We should pay attention to three elements here.

1. Alter table move omits tablespace XXX, which means that users move to their default tablespace, so it is easy to understand if the current tablespace is at least twice the size of the table.

2. The index will fail in the process of alter table move, so re-indexing must be considered.

3. Locks will occur in the process of alter table move, so you should avoid operating during peak business periods.

The experimental instructions on the second and third points are as follows

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as ljb

-- first obtain the SID of the SESSION to facilitate experimental observation

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and sixty

SQL > create table ljb_test as select * from dba_objects

Table created

SQL > select count (*) from ljb_test

COUNT (*)

-

62659

SQL > create index idx_test on ljb_test (object_id)

Index created

-- query that the SESSION currently has no lock

SQL > select * from v$lock where sid=160

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

-

-- it's normal to check the index status!

SQL > select index_name,table_name,status from user_indexes where table_name='LJB_TEST'

INDEX_NAME TABLE_NAME STATUS

IDX_TEST LJB_TEST VALID

-- execute command alter table ljb_test move

-Enquiry:

Select * from v$lock where sid=160

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

2043451C 20434530 160 CF 0 04 0 0 0

1FA072BC 1FA073D8 160 TX 917534 592 60 1 0

204344C0 204344D4 160 HW 76 323783147 60 0 0

1F9C4224 1F9C423C 160 TM 84825 0 60 0 0

204342F4 20434308 160 TT 76 16 4 0 0 0

1F9C377C 1F9C37C4 160 TS 76 323783147 60 0 0

However, since the alter table move command is not finished, the index is still valid!

SQL > select index_name,table_name,status from user_indexes where table_name='LJB_TEST'

INDEX_NAME TABLE_NAME STATUS

IDX_TEST LJB_TEST VALID

After the alter table ljb_test move; command is finished, check and find that the lock is gone.

SQL > select * from v$lock where sid=160

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

-

-- but the index failed!

SQL > select index_name,table_name,status from user_indexes where table_name='LJB_TEST'

INDEX_NAME TABLE_NAME STATUS

IDX_TEST LJB_TEST UNUSABLE

Summary: this experiment shows that: in addition to knowing that the alter table move command can free space (of course, the most fundamental function of this statement is to move tables to different table spaces, here is just a feature that it can free up space), but also understand that this action will lock the table until the end of the command, and will lead to index failure, which is a dangerous command, it is recommended that you should not operate in the peak period of business.

II. Shrink

It is well known that alter table move or shrink space can shrink segments to eliminate partial row migration, eliminate space debris, and make data more compact, but move is still different from shrink space.

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

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

There are two prerequisites for using Shrink:

Row movement must be enabled for tables

The segment space management (segment space management) of the table space where the table segment is located must be auto

The syntax for shrinking on a line segment is as follows:

Alter table/index/materialized view object_name shrink space [cascade] [compact]

Cascade: means to compress all dependent objects, such as compressed table statements plus cascade, and all indexes on the table will be compressed

Compact: the compression process is divided into two stages: the statement of the first stage is compact, and the segment space is compressed. In this process, you need to add a RX lock on the table, that is, only 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. The second phase statement without compact adjusts the high water level and frees up the reclaimed space. 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. For large tables, the compact option is recommended.

Maybe it's hard to understand, just look at the test.

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

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

Table created.

SQL > analyze table test compute statistics

Table analyzed.

SQL > col SEGMENT_NAME for A10

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS INIT

--

TEST 10 1280 10

SQL > col TABLE_NAME for A10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 0 1280

The TEST table initially allocates 10m of space, and you can see that there are 10 EXTENTS,1280 BLOCKS. The USER_TABLES view shows that there are 0 BLOCKS,1280 free BLOCKS in use, that is, the BLOCK in this 10m space has not been "formatted" by ORACLE.

SQL > begin

For i in 1..100000 loop

Insert into test values (I)

End loop

End

/

PL/SQL procedure successfully completed.

SQL > analyze table test compute statistics

Table analyzed.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS

TEST 10 1280

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 186 1094

-after inserting 10W pieces of data, the allocated space remains the same because the 10 EXTENTS have not been used up. It shows that 186 BLOCKS are used and 1094 BLOCKS are idle. At this time, the 186BLOCKS is the high water mark.

SQL > delete from test where rownum analyze table test compute statistics

Table analyzed.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS

TEST 10 1280

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 186 1094

SQL > select count (distinct dbms_rowid.rowid_block_number (rowid)) used_blocks from test

USED_BLOCKS

-

seventy-seven

As you can see here, after deleting half of the data, it still shows that 186BLOCKS are used, and the high water level remains the same. But only 77 BLOCK are actually used by the query. So the DELETE operation will not change the HWM.

SQL > alter table test move

Table altered.

SQL > analyze table test compute statistics

Table analyzed.

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 81 1199

After MOVE, the HWM is reduced and the free block is also up.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS

TEST 10 1280

-- but the space allocated has not changed, still 1280 BLOCKS. Let's look at it in SHRINK SPACE's way.

SQL > alter table test enable row movement

Table altered.

SQL > alter table test shrink space

Table altered.

SQL > analyze table test compute statistics

Table analyzed.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS

TEST 1 88

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 81 7

-- the allocated space has been reduced to a minimum, 1 EXTENTS, 88 BLOCKS

So MOVE is not really compressed space, it only compresses the space below HWM and removes debris. We generally do not specify the initial parameter when we build the table (the default is 8 BLOCK), so we do not feel this difference. And SHRINK SPACE really achieved the compression of the segment, including the initial allocation is also pressed, so it is a blow and above HWM operation.

As for which method is needed, it depends on your demand, and you need to analyze the growth of the table. If you will reach the previous HWM height in the future, it is obvious that MOVE is more appropriate, because SHRINK SPACE also needs to re-apply for the space that will be released before, which undoubtedly increases the operation.

Note:

1. However, you can also use MOVE to compress and allocate space, as long as you specify the STORAGE parameter.

SQL > drop table test

Table dropped.

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

Table created.

SQL > analyze table test compute statistics

Table analyzed.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS INIT

--

TEST 10 1280 10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 0 1280

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

Table altered.

SQL > analyze table test compute statistics

Table analyzed.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NA EXTENTS BLOCKS INIT

--

TEST 16 128 1

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 0 128

two。 When using move, the ROWID of some records will be changed, so the index will become invalid after MOVE, and REBUILD is required. At the same time, move tables require twice the tablespace, while shrink does not.

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

4. Indexes can also be compressed. Specify Shrink space cascade to compress the index when compressing the table, or alter index xxx shrink space to compress the index.

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

Limitations of 6.shrink

About what the difference between alter table move and shrink space is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.