Oracle 大规模 delete，update 操作 注意事项

一.说明

如果对大表进行大规模的delete和update，那么可以注意一下如下说明：

（1）查看执行计划，如果说删除的记录很多，走索引的成本会比全表扫描更大，因为更新数据时还需要做一些约束校验和创建index entry。而且对于多CPU情况，全表扫描还可以使用并行的特性。

Oracle Parallel Execution(并行执行)

http://blog.csdn.net/xujinyang/article/details/6832630

（2）如果表上有索引，B-Tree索引可以unusable索引，函数索引则disable索引，等操作结束之后在rebuild索引。

（3）如果是大规模的delete，那么可能还需要注意一下高水位的问题，在允许的情况下，可以用alter table move来降低高水位，同时注意rebuild索引。

Oracle高水位(HWM: High Water Mark)说明

http://blog.csdn.net/xujinyang/article/details/6830245

如果是OLTP的生产环境，对于禁用索引和高水位处理的操作要慎重。

二.相关测试

--查看表中记录数

SYS@dave2(db2)> select count(*) from dave;

COUNT(*)

----------

3080115

--查看索引信息

SYS@dave2(db2)>select index_name from dba_indexes where table_name='DAVE';

INDEX_NAME

------------------------------

IDX _PRCODE

IDX _STATE

IDX _INSERT

SYS_C005469

--创建一个备份表，下次使用

SYS@dave2(db2)> create table dave1 as select /*+parallel(t,3)*/ * from dave t;

Table created.

--查看执行计划

SYS@dave2(db2)> explain plan fordelete from dave where time_insert<to_date('2011-5-1','yyyy-mm-dd');

SYS@dave2(db2)> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2615685836

---------------------------------------------------------------------------

| id| operation| name | rows| bytes | cost (%cpu)| time|

---------------------------------------------------------------------------

|0 | delete statement||1369k|26m|7916(3)| 00:01:35 |

|1 |delete| dave |||||

|*2 |table access full| dave |1369k|26m|7916(3)| 00:01:35 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

---------------------------------------------------

2 - filter("time_insert"<to_date('2011-05-01 00:00:00', 'yyyy-mm-ddhh24:mi:ss'))

15 rows selected.

--查看走执行计划的大规模update操作

SYS@dave2(db2)>explain plan forupdate dave d set getcard_code=10 where state=2;

Explained.

SYS@dave2(db2)>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3706120077

-------------------------------------------------------------------------------

| id| operation| name| rows| bytes | cost (%cpu)| time|

-------------------------------------------------------------------------------

|0 | update statement|| 96254 |469k|2533(1)| 00:00:31 |

|1 |update| dave|||||

|*2 |index range scan| idx_state | 96254 |469k|194(2)| 00:00:03 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

---------------------------------------------------

2 - access("STATE"=2)

14 rows selected.

--禁用索引

SYS@dave2(db2)>alter index idx_state unusable;

Index altered.

SYS@dave2(db2)>select status from dba_indexes where index_name='IDX_STATE';

STATUS

--------

UNUSABLE

--如果是对进行delete操作，那么相关的索引要全部禁用才起作用。

--更新数据

SYS@dave2(db2)> update dave d setstate=10 where state=2;

101837 rows updated.

SYS@dave2(db2)> commit;

Commit complete.

--rebuild索引

SYS@dave2(db2)> alter index idx_state rebuild;

Index altered.

Oracle alter index rebuild说明

http://blog.csdn.net/xujinyang/article/details/6823011

--查看高水位

SYS@dave2(db2)> select num_rows,blocks,empty_blocks from dba_tables where table_name='DAVE';

NUM_ROWSBLOCKS EMPTY_BLOCKS

---------- ---------- ------------

3080115353500

num_rows的值只有做过统计信息收集之后才有，如果该值与count(*)差距太大，就说明需要重新进行收集了。

empty_blocks参数只有使用analyze收集统计信息才有，使用dbms_stats收集不到这个参数的信息。如果empty_blocks较多，就说明有高水位了。

SYS@dave2(db2)>analyze table dave compute statistics;

Table analyzed.

SYS@dave2(db2)>select num_rows,blocks,empty_blocks from dba_tables where table_name='DAVE';

NUM_ROWSBLOCKSEMPTY_BLOCKS

---------- ---------- ------------

3080115353504585

有关统计信息更多内容参考：

Oracle Statistic统计信息小结

http://blog.csdn.net/xujinyang/article/details/6881672

--处理高水位

SYS@dave2(db2)> alter table dave move;

Table altered.

--move会使所有索引失效，需要重新rebuild

SYS@dave2(db2)> select index_name,status from dba_indexes where table_name='DAVE';

INDEX_NAMESTATUS

------------------------------ --------

IDX_PRCODEUNUSABLE

IDX_STATEUNUSABLE

IDX_INSERTUNUSABLE

SYS_C005469UNUSABLE

--rebuild索引

SYS@dave2(db2)> alter index IDX_PRCODE rebuild;

Index altered.

SYS@dave2(db2)> alter index IDX_STATE rebuild;

Index altered.

SYS@dave2(db2)> alter index idx_insert rebuild;

Index altered.

SYS@dave2(db2)> alter index SYS_C005469 rebuild;

Index altered.

SYS@dave2(db2)>select index_name,status from dba_indexes where table_name='DAVE';

INDEX_NAMESTATUS

------------------------------ --------

IDX_PRCODEVALID

IDX_STATEVALID

IDX_INSERTVALID

SYS_C005469VALID

--使用analyze分析表

SYS@dave2(db2)>analyze table dave compute statistics;

Table analyzed.

SYS@dave2(db2)> select num_rows,blocks,empty_blocks from dba_tables where table_name='DAVE';

NUM_ROWSBLOCKS EMPTY_BLOCKS

---------- ---------- ------------

3080115354874448

因为我这里没有进行大量的delete操作，效果很有限。

-------------------------------------------------------------------------------------------------------