执行alter table move时，用户还能用表吗？

mikewujing

做了个简单的测试，发现alter table move真的会对index有影响，这个以前倒是没有注意

SQL> create table test (id int);

SQL> create index ind_test on test(id);

SQL> insert into test values (1);

SQL> select * from test where id = 1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2388774261

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_TEST |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> alter table test move;

SQL> select * from test where id = 1;


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

If you search the column status in the user_indexes, you will find that the status changed to UNUSABLE

select u_ind.index_name, u_ind.table_name, u_ind.status from user_indexes u_ind.


However after rebuild index online, you will find it is back online again

SQL> alter index IND_TEST rebuild online;

Index altered.

SQL> set autotrace traceonly exp
SQL> select * from test where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2388774261

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_TEST |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

rollingpig

1。 什么叫用？
select 在move过程中没问题。move后会由于没有可用index可能导致性能下降
dml 在move过程中会等待，而move后根据“skip unsable index”的参数会有两种情况。如果skip unsable index=true, 那么dml 可以进行，如果skip unsable index=false, dml会报错

2。 可行的，不太影响用户的做法就是在线重定义，也就是dbms_redefinition 包了。

rollingpig

对于 skip_unusable_indexes;
10g 是参数 skip_unusable_indexes
9i是用alter session 命令
alter session set skip_unusable_indexes=true;

lixunpeng

原帖由 macheraters 于 2009-11-26 10:26 发表
MOVE会导致索引实效，在线的，可以用10G的新特性，叫做SHRINK

不过感觉move可以把表在表空间的位置整体前移，而shrink只能把extent向段头靠拢，段头不能向前移动。所以move更有利于收缩数据文件。

[ 本帖最后由 lixunpeng 于 2009-11-28 12:52 编辑 ]

lixunpeng

原帖由 junsansi 于 2009-11-26 10:06 发表
可以考虑用在线重定义~~
除了finish操作时会对表加锁外，其它时段均不会影响表的正常操作~

在线重定义功能倒是挺强大，还可以用在move上。我原来以为只能重定义表呢。

lixunpeng

原帖由 rollingpig 于 2009-11-26 11:35 发表
1。 什么叫用？
select 在move过程中没问题。move后会由于没有可用index可能导致性能下降
dml 在move过程中会等待，而move后根据“skip unsable index”的参数会有两种情况。如果skip unsable index=true, 那么dml 可以进行，如果skip unsable index=false, dml会报错

2。 可行的，不太影响用户的做法就是在线重定义，也就是dbms_redefinition 包了。

深入！