ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于alter table move的一个小实验

关于alter table move的一个小实验

原创 Linux操作系统 作者:bartfj 时间:2008-12-18 22:50:57 0 删除 编辑

关于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 WNER='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 WNER='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 WNER='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 WNER='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 WNER='SYS';

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

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

16603 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=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 ptimizer=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 ptimizer=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 ptimizer=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变化了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7232789/viewspace-517383/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 《APUE》习题
请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    86
  • 访问量
    239047