alter table move相关知识研究

编程技术  /  houtizong 发布于 3年前   89

关于alter table move的一个小实验。
SQL> create table test
2 as
3 select * from dba_objects
4 where 1=0;

Table created.

SQL> select extents ,SEGMENT_NAME from dba_segments where
2 SEGMENT_NAME='TEST' and OWNER='SYS';

EXTENTS SEGMENT_NAME
---------- --------------------
1 TEST

SQL> insert into test
2 select * from dba_objects;

16603 rows created.

SQL> select extents ,SEGMENT_NAME from dba_segments where
2 SEGMENT_NAME='TEST' and OWNER='SYS';

EXTENTS SEGMENT_NAME
---------- --------------------
8 TEST

SQL> delete from test;

16603 rows deleted.

SQL> select extents ,SEGMENT_NAME from dba_segments where
2 SEGMENT_NAME='TEST' and OWNER='SYS';

EXTENTS SEGMENT_NAME
---------- --------------------
8 TEST

SQL> alter table test move;

Table altered.

SQL> select extents ,SEGMENT_NAME from dba_segments where
2 SEGMENT_NAME='TEST' and OWNER='SYS';

EXTENTS SEGMENT_NAME
---------- --------------------
1 TEST

SQL> insert into test
2 select * from dba_objects;

16603 rows created.

SQL> select bytes,extents,segment_name from dba_segments
2 where SEGMENT_NAME='TEST' and OWNER='SYS';

BYTES EXTENTS SEGMENT_NAME
---------- ---------- --------------------
2424832 8 TEST

SQL> set autotrace traceonly
SQL> select * from test;

16603 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1315 consistent gets
0 physical reads
0 redo size
1711517 bytes sent via SQL*Net to client
89944 bytes received via SQL*Net from client
1108 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16603 rows processed

SQL> delete from test;

16603 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'TEST'
2 1 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
118 recursive calls
18207 db block gets
263 consistent gets
0 physical reads
6745400 redo size
648 bytes sent via SQL*Net to client
445 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
16603 rows processed

SQL> select * from test;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
229 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> alter table test move;

Table altered.

SQL> select * from test;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
0 consistent gets
0 physical reads
0 redo size
784 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

结论：alter table ... move不但拉回了HWM，而且回收了extent

alter table move

在oracle9i中,delete很大的表的数据后,需要收缩表的空间,

可以使用alter table tabname move (tablespace tbs_name),

注意:这个时候一定需要rebuild index . 因为move后,数据的rowid变化了